SQL Server : Reporting Services - Report Access and Delivery

10/14/2010 9:54:00 AM

Several methods are available for delivering reports to users on demand or by subscription. Most popular is the Report Manager interface described earlier. You can embed reports in SharePoint Portal using the included Web parts. Direct browser access is handled by URL parameters. There is also a ReportViewer control that programmers can embed in ASP.NET or Windows Forms applications.

Delivery on Demand

Reports are available on demand through Report Manager and direct URL browser Web connections.

Report Manager

The basic navigation concepts for Report Manager were described earlier. Users navigate the folder namespace hierarchy or use the search prompt to find the desired report. Folder and report items are presented as single-click hyperlinks. Clicking a report runs the report with a control toolbar at the top of the page. The toolbar has buttons for paging, zooming, search, export, refresh, and printing, as shown in Figure 1.

Figure 1. ReportViewer in Report Manager

The search text box accepts a string. Entering the string enables the Find and Next links, which help you search larger reports for a particular phrase.

The Print button loads a print preview ActiveX control that downloads from the report server. You can set device properties, adjust margins, and select page ranges.

If the report requires parameters, a text box or drop-down list is included on the toolbar for each parameter. Fill in the details or select from the list. Click the View Report button to render the report and see the Report Viewer toolbar. Some parameters are dependent on each other, as described earlier. In this case, selecting from one list fills the next.

The Select A Format drop-down list works in conjunction with the Export link to render reports in a variety of presentation formats.

SharePoint Web Parts

Two SharePoint Web parts are included that enable folder navigation and report viewing. These work with SharePoint Services or Portal Server. The Web parts must be installed on the SharePoint server using the Stsadm.exe utility. Report Manager must be installed and operational because the Report Viewer Web part makes URL calls directly to this site. If SharePoint and Reporting Services live on the same machine, the Reporting Services virtual directories must be excluded from SharePoint Services.

You add the Web parts to a SharePoint page. You can use Report Viewer by itself or in tandem with Report Explorer. In standalone mode, the report server address and report path are entered in the Web part’s Properties dialog box. You cannot enter required parameters. The report runs automatically when the page is displayed and prompts you for the parameters. If you want a page with the parameters preset, use a PageViewer Web part and the URL access approach described next.

The Report Explorer Web part can display the contents of a folder on the report server. You can select a report and have it linked to a Report Viewer Web part for display. Report Explorer also has links for subscription management.

Browser URL Access

Instead of navigating Report Manager, it might be preferable to display a report directly in a browser window. This technique allows integration with existing Web sites and forms applications using a browser control. You can customize the report viewing and navigation experience by including parameters in the URL phrase. You can also pass report and query parameters in this fashion. The URL phrase is often used in a Web page hyperlink that opens in a new target window or iframe.

Two sets of parameters are used to control navigation and viewing options. The viewer commands are prefixed with rc: and include Toolbar, Parameters, DocMap, Zoom, Find, Paging, and Stylesheets. The report server commands are prefixed with rs: and include Format, Language, and Snapshot. Samples of the URL syntax are shown in the URL Access programming sample.

ReportViewer Control

The ReportViewer control is part of the .NET Framework 2.0. It works in Windows Forms, as shown in Figure 2, and Web pages, as shown in Figure 3.

Figure 2. ReportViewer control in a Windows Forms application

Figure 3. ReportViewer control in an ASP.NET Web page

You can configure ReportViewer to render a report locally or from a report server. Both use the same basic rendering technology. The local client-based rendering relies on an RDLC file included in the project. The report server method requires a report server URL and a report path. Properties are set to configure toolbar, navigation, and output options.


You can automate report delivery through subscription services. You can link or attach a report to an e-mail message or save it to a file share. You create subscriptions on the Subscription tab of a report; they are summarized in the My Subscriptions link at the top of each Report Manager page. Note that all subscriptions require cached credentials in the report data source.

Once this requirement is met, the New Subscription button on the Subscription tab opens a page that prompts for the delivery method. Report Server File Share is one option. If e-mail settings are configured for the report server, the second option is Report Server E-Mail. Custom delivery extensions are described at the end of this chapter. They appear as additional delivery methods.

Subscriptions must have a schedule. You can manually create a schedule for the report or use a shared schedule maintained by an administrator in the Site Settings area. A schedule can specify running once or repeating hourly, daily, weekly, or monthly. Select the desired days and time. Start and end dates limit the lifetime.

Subscriptions are dependent on the SQL Reporting Services Windows service and the SQL Server Agent service. Both must be running to successfully complete your subscription.


The e-mail delivery method creates a single e-mail message with a report link or an attached file. Links are more efficient for internal reporting and usually connect the user directly to Report Manager. Attachments are appropriate for external mailings, typically in PDF or XLS format. You can specify the To, Cc, Bcc, Subject, Priority, and Body (comment). You are prompted for any required parameters at the bottom of the form. Formatting support is limited. Highly stylized messages require a custom delivery extension. The same is true for multiple file attachments per message.

Shared Folder

The folder delivery method creates a single file for each report. You enter the path and select a report format. Credentials are required for access to the file share. Options for overwrite and file versioning are available.

Data-Driven Subscriptions

Data-driven subscriptions can generate e-mails or shared folder files. The required settings are provided by a database query. You can use a subscription-specific or shared data source to host a query that returns one row for each report that you want to generate. The columns include names such as To, Cc, Bcc, Subject, filename, path, render_format, and values for required report parameters. You map these directly to the required settings.

This method provides a lot of flexibility for customized report distribution. You can create a sales report for hundreds or thousands of employees or customers that is then tailored to specific regions or product areas. This is also a good way to maintain archived reports to satisfy retention policies.

Presentation Formats

Report output can be produced in a variety of Web-, page-, and desktop-compatible formats. You cannot edit these files using the Reporting Services tools, but third-party products are available to fit this need. Each format has specific options and properties that configure the rendering extension. The same extensions are used on the report server, the Report Designer environment, and the ReportViewer control.


HTML is the default rendering extension. It produces UTF-8 encoded pages or fragments. Standard HTML 4.0 is produced by Report Manager, SharePoint Web parts, and direct URL access. HTML 3.2 is possible only through URL addressing with the rs:Format=HTML3.2 option. MHTML is also available through URL addressing and the Export option in Report Manager. This format, referred to as Web Archive, combines the graphics and report data into a single file.

All report data regions are rendered as tables. Charts become images. Style properties turn into CSS style tags. Report-level items such as headers and footers become HTML <DIV> tags.


The Excel rendering extension creates BIFF files that are compatible with Excel 97 and later. Each page in the report generates a worksheet in the Excel file, as shown in Figure 4. The data is stored in tabular format with nesting. Some colors are not supported, so substitutions are made. Limitations exist for worksheet, row, cell, and column sizes, so very large reports can be problematic. Charts turn into pictures and are not editable Excel chart objects. Document maps are rendered as the first worksheet with links to other data sheets.

Figure 4. Report output in Excel format


The PDF rendering extension produces PDF 1.3–compatible output, as shown in Figure 5. These files are suitable for viewers such as Adobe Acrobat. Bookmarks are supported and turn into PDF bookmarks. Avoid drillthrough links—use hyperlinks instead. Make sure that all fonts necessary to view the report are installed on the report server as well as on the target clients.

Figure 5. Report output in PDF format


The Image rendering extension creates a bitmap or metafile. The default format is TIFF, which stores multiple pages in a single file. Other available formats include valid GDI+ variants: GIF, JPEG, PNG, BMP, and EMF. Size, resolution, and image format are configurable. Items are rendered in the order that they appear in the report definition. You can control this by setting the zindex property of select objects.


The Comma-Separated Value (CSV) rendering extension produces plain-text files with no character formatting or graphics. This is useful for importing into other applications, such as spreadsheets and databases. The first row contains field names by default. All data regions are output with a column for each data element. This extension ignores such items as headers, footers, images, and ActiveX controls. Field, record, qualifier, and header settings are configurable.


The XML rendering extension creates XML documents that are specific to the source report. Layout and images are ignored. XML is useful in application integration scenarios. The top-level element in XML is Report. Each data region creates an element. The elements and attributes are generated in the order that they appear in the report definition, as shown in Figure 6. Data types are notated in the included schema. You can set element names by using the DataElementName property of individual objects in Report Designer or by using raw RDL.

Figure 6. Report output in XML format when viewed in Internet Explorer

The rendered XML can be transformed using an XSLT document that you specify in URL addressing. This is a creative and efficient way to produce custom output formats. The result can be a more complex XML document, XHTML, or plain text. Note that each report creates unique XML output, so the XSLT transforms are specific to that report.

Programming: Rendering

The code samples for this chapter include several examples that demonstrate report output. One uses ASP.NET Web pages to host links and iframes for URL access. Another incorporates the ReportViewer control in both ASP.NET pages and Windows Forms. The third is a useful utility that combines Web service functionality with rendering to create a report file generation factory.

Parameterized URL Access

CS Web Pages, includes a URLAccess.ASPX page that demonstrates URL access, as shown in Figure 7.

Figure 7. URL access inside a Web Page using an IFrame tag

In design view, you see a hyperlink (<A> tag) that opens a new page with the Tabular report. The URL phrase is shown here:


In this example, localhost is the server name and reportserver indicates the Web service virtual directory. The path and report are the first URL parameters. The rs:Command parameter instructs report server to render the report. Several rs: and rc: parameter options were described earlier.

The Chart Report button on the Web page has an onclick event handler that sets the src property of an iframe. This causes the chart sample to appear on demand.

ReportViewer Examples

The ReportViewer control is part of the .NET Framework 2.0. You can use it in ASP.NET pages as well as Windows Forms, as shown earlier in Figures 2 and 3. The ASP.NET project called CS Web Pages has a page called ReportViewer.aspx that embeds this control. The properties of the control have a ProcessingMode that defaults to Local. Choose Remote, and use the Smart Tag control to set the Report Server URL and Report Path. The Toolbar category in the Properties window has options that control the toolbar display. No code is needed for the report to display at runtime.

The project CS Forms has a form called Simple.cs. This form embeds a ReportViewer control with the Dock property set to fill. You use the Smart Tag control or Properties windows to adjust the settings. Notice that the report properties are identical in the Web and form versions. Adding this control to the form automatically generates the necessary lines of code:

private void Simple_Load(object sender, EventArgs e)

Another sample form, called Service.cs, uses the report server Web service to enumerate folder and report names in a list box. Selecting a list item triggers the SelectedIndexChanged event, which sets the ReportViewer ReportPath property and calls RefreshReport, as shown in the following code block:

namespace CS_Forms
public partial class Service : Form
ReportingService2005 rptSrvr = new ReportingService2005();
CatalogItem[] catItem;

public Service()

private void Service_Load(object sender, EventArgs e)
rptSrvr.Credentials = System.Net.CredentialCache.DefaultCredentials;
rptSrvr.Url = "http://localhost/reportserver/reportservice2005.asmx";

catItem = rptSrvr.ListChildren("/Chap21/reports", true);

foreach (CatalogItem item in catItem)

private void ListBox1_SelectedIndexChanged(object sender, EventArgs e)
ReportViewer1.ServerReport.ReportPath = catItem[ListBox1.SelectedIndex].Path;


Rendering Factory

The report server Web service can render reports in several output formats. A CS Forms project called Render.cs combines the folder navigation methods with a Treeview control to display the folder hierarchy. Select a report from the /Chap21/Reports folder. The option button in the middle of the form determines the output format. Enter a file name, and use FolderBrowserDialog to select a target file folder. The Generate button renders the report using the Web service and redirects the byte stream to the target file. This utility can quickly generate sample output for testing purposes. The code can easily be edited to automate other report generation tasks.

Top 10
SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
The latest Audi TT : New angles for TT
Era of million-dollar luxury cars
Game Review : Hearthstone - Blackrock Mountain
Game Review : Battlefield Hardline
Google Chromecast
Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
Michael Kors Designs Stylish Tech Products for Women
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
Popular Tags
Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone