Chapter 5. Using the Report Viewer Web Parts

Web parts are one of the most useful features of SharePoint. They encapsulate functionality that a user can add to a page and customize by setting various properties. There are many examples of useful web parts, such as Lists and Libraries, Business Data, and so on. From a business intelligence (BI) standpoint, there are web parts for rendering a Reporting Services report on a page, which is the focus of this chapter.

There are different Reporting Services web parts depending on how Reporting Services is configured. The SQL Server Reporting Services Report Viewer web part is used to render reports when Reporting Services is running in SharePoint Integrated mode. For Reporting Services running in native mode, there are two web parts: Report Explorer displays the list of reports in a particular folder in the Report Manager, and Report Viewer renders a report. Report Viewer can also render the selected report in the Report Explorer web part.

Configuring the SharePoint Integrated Mode Web Part

When Reporting Services is configured to run in SharePoint Integrated mode, the SQL Server Reporting Services Report Viewer web part is available to render a report in a web part page. The SQL Server Reporting Services Report Viewer web part is included with the Reporting Services Add-In for SharePoint installation. This is currently the only way to install the web part. The installation and configuration of the Reporting Services Add-In for SharePoint is covered in Chapter 3, “Reporting Services Setup and Configuration.”

Follow these steps to add the Report Viewer web part to a page and configure it to render a report:

1. Create a new page (Click Site Actions and select New Page from the drop-down menu) or navigate to an existing page.
2. Click the Insert tab under Editing Tools, and then click Web Part on the toolbar ribbon, as shown in Figure 5.1.

   Figure 5.1. Adding a web part.

   

3. Select SQL Server Reporting from the Categories list, and then SQL Server Reporting Services Report Viewer from the Web Parts list, as shown in Figure 5.2.

   Figure 5.2. Select Report Viewer web part.

   

4. Click the Add button to display the web part on the page, as shown in Figure 5.3. All web parts have a drop-down menu that is used to modify the web part properties. The menu is accessible by clicking the glyph that appears when the mouse hovers over the title bar of the web part. The Report Viewer web part also has a Click Here to Open the Tool Pane hyperlink that you can use to configure the web part.

   Figure 5.3. Report Viewer web part added to page.

   

5. Click the Click Here to Open the Tool Pane hyperlink or hover the mouse over the web part title bar, click the glyph, and select Edit Web Part, as shown in Figure 5.4. The report to be displayed must be specified in the web part properties.

   Figure 5.4. Web part menu.

   

6. The web part properties form is now displayed, as shown in Figure 5.5. The Report property is used to specify the report to be rendered by the web part. Click the button next to the Report text box to display the Select an Item dialog, as shown in Figure 5.6.

   Figure 5.5. Web part properties.

   

   Figure 5.6. Select report.

   

7. Navigate to the folder that contains the report to be rendered and select it.

The report to be rendered must be in the form /folder/reportname.rdl (for example, /Standard Reports/ChannelSalesByYear.rdl). The Report Server URL is specified in the configuration of the Reporting Services add-in for SharePoint Integrated mode (covered in Chapter 3), so it is not required here. The page being edited belongs to a particular SharePoint site, so an absolute URL is not required either; just the folder name (that contains the report) relative to the site root and the actual report name are required.

At this point, the minimum configuration of the Report Viewer web part is complete. The report selected displays sales data by channel and year. The year is a parameter. The web part displays a form to allow the user to enter values for any parameters defined in the report. Figure 5.7 shows the rendered report.

Figure 5.7. Channel Sales by Year report rendered in Report Viewer web part.

SharePoint Integrated Mode Web Part Properties

All web parts have many properties that can be set to configure various aspects of their functionality. The Report Viewer web part properties are shown in Figure 5.8. The Report property identifies the report to be rendered. The Hyperlink Target determines how to render any linked content in the report. The valid values are _blank, which loads the content in a new browser window; _self, which opens the content in the current frame; and _top, which uses the current window.

Figure 5.8. Report properties.

The View group has several check boxes that toggle the visibility of items on the toolbar, as shown in Figure 5.9.

Figure 5.9. View properties.

Several of the toolbar visibility items are included in the Actions drop-down menu, as shown in Figure 5.10. Moving from the Actions menu to the right is the refresh command, page navigation commands (first, previous, go to page number, next, and last), go back to parent report, search text box, find next command, zoom, and ATOM Feed. The ATOM Feed button is new with SQL Server Reporting Services 2008 R2, providing the capability to extract the data in the report as an ATOM data feed. One use of the ATOM data feed is to import the data into a PowerPivot worksheet in Excel 2010.

Figure 5.10. Report Viewer toolbar.

The Parameters group provides for loading the parameters defined in the report. Click the Load Parameters button, as shown in Figure 5.11. Loading the parameters is required to connect a filter web part to the Report Viewer web part, as discussed in the next section.

Figure 5.11. Parameters properties.

The parameters defined in the report are added to the form, as shown in Figure 5.12, providing a choice between using the defaults as defined in the report or specifying a value.

Figure 5.12. Parameters loaded.

The remaining web part properties are common to all web parts, so they are not discussed here.

SharePoint Integrated Mode Web Part Connections

Web parts support connections where the value of one web part (for example, a filter web part) can be assigned to a parameter of another web part. In the case of the Report Viewer web part, if there are parameters defined in the report, the parameter values can be specified in a filter web part and passed to the report instead of having the Report Viewer web part render a parameter entry form.

A number of filter web parts are included with SharePoint 2010 Server, as shown in Figure 5.13.

Figure 5.13. Filter web parts.

The following is a list of the filter web parts and a brief description:

• Choice Filter is a drop-down list of available selections; the list is entered in the web part properties form.

• Current User Filter provides properties of the current user.

• Date Filter provides for the entry of a date; a calendar icon is available to pop up a calendar.

• Filter Actions adds a button that is to be clicked when all filter web part values have been entered; this allows for transferring the values of all filter web parts at one time.

• Page Field Filter provides information about the current page.

• Query String (URL) Filter provides access to items in the query string.

• SharePoint List Filter populates a list of values from the contents of a SharePoint list.

• SQL Server Analysis Services (SSAS) Filter populates a list of values from an SSAS cube.

• Text Filter allows entry of a text value.

Figure 5.14 shows a web part page that has a Text Filter and a Filter Actions web part and the Report Viewer web part. Note the warning message underneath the Text Filter stating that it is not connected. The message goes away after the web part gets connected to the Report Viewer web part.

Figure 5.14. Web part page with filter web parts.

Follow these steps to connect the Text Filter web part to the Report Viewer web part:

1. Edit the properties for the Report Viewer web part and expand the Parameters group, as shown in Figure 5.15.

   Figure 5.15. Report parameters.

   

2. Click the Load Parameters button to retrieve the parameters defined in the report. The parameters defined in the report are displayed in the web part properties, as shown in Figure 5.16.

   Figure 5.16. Report parameters loaded.

   

3. Click OK on the web part properties form to save these changes.
4. Move the mouse over the title bar of the Text Filter, click the glyph, select Connections from the drop-down menu, Send Filter Values To, and Report Viewer – ChannelSalesByYear, as shown in Figure 5.17.

   Figure 5.17. Connecting the filter web part to the Report Viewer web part.

   

5. The Configure Connection dialog is displayed, as shown in Figure 5.18. This connects the filter web part to a parameter defined in the report. Select the report parameter from the drop-down and click Finish.

   Figure 5.18. Configure connection.

   

If the preceding dialog does not display, check for a prompt in the browser that is asking whether to allow pop-ups. To display the dialog, enable pop-ups (at least temporarily).

The web part connections are now complete, and the page is ready to accept parameters and render the report, as shown in Figure 5.19. Note that the Text Filter no longer shows the warning message that the filter is not connected, as shown earlier in Figure 5.14.

Figure 5.19. Web part page with connections.

Enter the year parameter value for the report in the Text Filter web part and click the Apply Filters button to render the report, as shown in Figure 5.20.

Figure 5.20. Report rendered with web part connection.

Configuring the Native Mode Web Parts

Reporting Services includes SharePoint web parts that work with a Report Server running in native mode. There are two web parts:

• Report Explorer displays the list of reports in a folder (similar to what you see in the Report Manager, which is the web application used to work with Reporting Services running in native mode).

• Report Viewer renders a report. It can either render a report selected in the Report Explorer web part or simply render the report specified in its web part properties.

The native mode web parts are not installed automatically; there is a manual step required to install them. The default location of the web parts varies based on the version of SQL Server. (For example, on a 64-bit install of SQL Server 2008 R2, the web parts are located in the file rswebparts.cab in the folder C:Program Files (x86)Microsoft SQL Server100ToolsReporting ServicesSharePoint.) To install the web parts, open the SharePoint 2010 Management Shell (in the Microsoft SharePoint 2010 Products program group) and run the following command (verify the location of the rswebparts.cab file):

Install-SPWebPartPack "C:Program Files (x86)Microsoft SQL
Server100ToolsReporting ServicesSharePoint swebparts.cab" –GlobalInstall

The -GlobalInstall parameter puts the dynamic link library (DLL) in the Global Assembly Cache (GAC); this is required to connect the web parts to the filter web parts, which provide for entering and setting parameter values required by a report.

Follow these steps to add the native mode web parts to a page (the steps for setting up the web parts for native mode are practically the same as the steps covered earlier for the SharePoint Integrated mode web part):

1. Create a new page or navigate to an existing page (refer to details in the preceding section).
2. Click the Insert tab under Editing Tools and then click Web Part on the toolbar ribbon.
3. Select Miscellaneous from the Categories list, and the two available web parts are visible in the Web Parts list, as shown in Figure 5.21.

   Figure 5.21. Selecting web parts for native mode.

   

4. Add the Report Explorer web part to the page.
5. Add the Report Viewer web part to the page.
6. The page displays the added web parts as shown in Figure 5.22.

   Figure 5.22. Native mode web parts added to page.

   

7. Move the mouse into the Report Explorer web part title area and then click the glyph to display the context menu; click Edit Web Part.
8. Fill in the Configuration properties, as shown in Figure 5.23.

   Figure 5.23. Configuring the Report Explorer web part.

   

9. Click OK when done.

The Report Explorer web part display is refreshed and now shows the list of reports in the specified Report Manager folder, as shown in Figure 5.24.

Figure 5.24. Report Explorer web part.

To configure the Report Viewer follow these steps:

1. Move the mouse into the web part title area.
2. Click the glyph to display the context menu.
3. Click Connections, Get Report From, then Report Explorer, as shown in Figure 5.25.

   Figure 5.25. Connecting Report Viewer to Report Explorer.

   

   The Connections option in the context menu in Figure 5.25 is obscured; the Report Explorer menu option overlays it. The Report Viewer web part now renders the report that is selected in the Report Explorer web part.

4. Click the ChannelSalesByYear report in the Report Explorer web part to render the report, as shown in Figure 5.26.

   Figure 5.26. Channel Sales by Year Native report rendered in web part.

   

The Report Viewer web part can also be configured to render a specific report. Fill in the Report Manager URL (for example, http://server/reports) and report path, as shown in Figure 5.27. Note that the buttons adjacent to the text boxes pop-up a window that accepts only text; they do not provide any help in navigating to the Report Manager URL or the report.

Figure 5.27. Specifying a report in the Report Viewer web part.

Summary

The Report Viewer web parts provide the capability for rendering Reporting Services reports on a SharePoint page. There are separate web parts for reports deployed to a Report Server running in SharePoint Integrated mode versus reports deployed to a Report Server running in native mode. The SharePoint Integrated mode web part is installed with the Reporting Services add-in for SharePoint installation; the native mode web parts are packaged with SQL Server and can be installed with the Install-SPWebPartPack PowerShell command. The web parts contain a number of properties that can be specified to fine-tune how a report is rendered.

Best Practices

The following are best practices from this chapter:

• If possible, run Reporting Services in SharePoint Integrated mode; the Report Viewer web part included with the Reporting Services add-in for SharePoint is much newer than the native mode web parts, which have remained unchanged for quite some time.

• There are many properties available for customization in the report web parts. Take advantage of this flexibility.

• SharePoint 2010 Server includes a nice collection of filter web parts out of the box. Use the filter web parts to accept report parameters and connect them to the SharePoint Integrated mode Report Viewer web part instead of relying on the parameter form that the Report Viewer web part renders by default.