Chapter 4. Report Management

When Reporting Services is running in SharePoint Integrated mode, reports are stored in a SharePoint document library. Reports are essentially documents, and the document management functions provided by SharePoint are automatically available (for example, properties, versioning, check in, and check out). In addition, a number of report management functions are applicable only to reports that are also integrated into SharePoint. Most of the report management functions are available via a drop-down context menu (referred to as the document context menu in the rest of this chapter) accessible by clicking the glyph next to each report in the SharePoint library, as shown in Figure 4.1.

Figure 4.1. Document context menu.

This chapter covers deploying reports to a SharePoint library and the following report management functions:

• Built-in SharePoint functions: Functions available in all SharePoint document libraries.

• Shared schedules: Schedules that can be reused for multiple report management functions, like expiring a cached report or generating a report snapshot.

• Processing options: Specify whether a report is run with live data, cached data, or rendered from a report snapshot.

• Data sources: Define the source of data for a report.

• Shared datasets: Define a query or specify a stored procedure that is executed to retrieve the report data from a given data source. The shared dataset can be used in multiple reports.

• Parameters: Specify default value and prompt for parameters defined in a report.

• Subscriptions: Generate a report periodically based on a schedule and output format.

Deploying Reports

Reports can be deployed to a SharePoint document library by any of the following actions:

• Upload to the document library

• Deploy from SQL Server Business Intelligence Development Studio (BIDS)

• Save from Report Builder

BIDS is included with SQL Server; it is a subset of Visual Studio (the developer tool for building applications), and it provides project types to develop reports, SQL Server Integration Services packages, and SQL Server Analysis Services cubes. Report Builder is a user-friendly tool for developing reports and is available for download as part of the SQL Server 2008 R2 Feature Pack.

Upload Report to Document Library

Follow these steps to upload a report to a SharePoint document library:

1. Open your browser and navigate to the document library
2. Click the Documents tab, and click the Upload Document icon on the ribbon bar or the Add Document hyperlink beneath the list of reports, as shown in Figure 4.2.

   Figure 4.2. Upload a document.

   

3. After you click Upload Document or Add Document, the Upload Document dialog is displayed, as shown in Figure 4.3.

   Figure 4.3. Specify the report to upload.

   

   ---

   Note

   The document library used in Figure 4.3 has Versioning enabled, which retains a version of the document for each change that is made. If your document library does not have Versioning enabled, you will not see the Version Comments text box; you will see an Overwrite existing files check box and a link to upload multiple files.

   ---

4. Enter the full path of the report to be uploaded or navigate to the report by clicking the Browse button. If the report already exists in the SharePoint library, click the Add as a New Version to Existing Files check box to retain the version history of the report; leave the check box unchecked to overwrite the report.

Deploy Report from BIDS

Follow these steps to deploy a report from a BIDS report project:

1. To launch BIDS, click the Start menu, navigate to the SQL Server program folder, and then select SQL Server Business Intelligence Development Studio, as shown in Figure 4.4.

   Figure 4.4. Launch BIDS.

   

2. Navigate to the project that contains the reports to be deployed.
3. Right-click the project in the Solution Explorer to display the project Properties dialog, as shown in Figure 4.5. Fill in the properties with the appropriate values for your environment.

   Figure 4.5. Report project Properties dialog.

   

Several important properties are directly related to deploying reports and their dependent artifacts to a SharePoint document library:

• TargetServerURL: The URL of the SharePoint site. This should be the root site in the site collection (for example, http://SharePoint2010).

• TargetDataSourceFolder: The URL of a SharePoint library where shared data sources will be stored.

• TargetDatasetFolder: The URL of a SharePoint library where shared datasets will be stored. Shared datasets are a new feature in SQL Server 2008 R2 that allow a query to be defined once and then reused in multiple reports.

• TargetReportPartFolder: The URL of a SharePoint library where report parts will be stored. Report parts are a new feature in SQL Server 2008 R2 allowing portions of a report to be published and then reused in other reports (for example, a chart, table, or tablix).

• TargetReportFolder: The URL of the SharePoint document library where reports will be stored.

Note that except for the TargetServerURL property, each property detailed here requires the full URL of the SharePoint library. If the SharePoint library does not already exist, it is created automatically when reports are deployed. After setting the properties, as shown earlier, deploy an individual item in the project by right-clicking it in the Solution Explorer and selecting Deploy from the context menu. To deploy everything in the project (that is, reports, shared data sources, shared datasets, report parts, and so on), right-click the project in the Solution Explorer and then select Deploy from the context menu.

Save from Report Builder

Follow these steps to deploy a report by saving it to a document library from Report Builder:

1. To launch Report Builder, click the Start menu and select Microsoft SQL Server 2008 R2 Report Builder, as shown in Figure 4.6.

   Figure 4.6. Launch Report Builder.

   

2. To set the deployment options in Report Builder, click the icon in the upper-left corner of the window to display the drop-down menu, as shown in Figure 4.7.

   Figure 4.7. Report Builder settings.

   

3. Click the Options button to display the Report Builder Options dialog, as shown in Figure 4.8.

   Figure 4.8. Report Builder options.

   

4. Fill in the settings as appropriate for your environment.

There are two important settings for deploying reports in the dialog shown in Figure 4.8:

• Use This Report Server or SharePoint Site by Default: Fill in the SharePoint site where reports will be published. This must be the root site in a SharePoint site collection.

• Publish Report Parts to This Folder by Default: Fill in the SharePoint library where report parts will be deployed. The URL for the report parts library is the SharePoint site plus the report parts folder.

Built-In SharePoint Functions

SharePoint provides a number of built-in functions to manage documents; a report deployed to a SharePoint library is essentially a document. SharePoint provides properties for a document. For a report, the properties can be viewed or edited by selecting View Properties or Edit Properties from the document context menu. An example of the report Properties dialog is shown in Figure 4.9.

Figure 4.9. Report Properties dialog.

The report Properties dialog also provides access to a number of other built-in SharePoint document management functions, such as the following:

• Edit Item: Provides a dialog to edit the report properties.

• Delete Item: Removes the report.

• Version History: Shows the change history for the report (if document versioning is enabled for the document library).

• Manage Permissions: Specifies the access levels of individuals and groups for a report.

• Alert Me: Enables an email or text message when a report is modified.

• Check Out: Locks the report while changes are being made. Another user will be blocked from checking out the report until it is checked in or the checkout is cancelled.

• Manage Copies: Creates linked reports.

With the exception of Manage Copies, each of the report management functions available from the View Report Properties dialog is built in to SharePoint and is available in any document library.

In addition to the report management functions available from the document context menu, SharePoint provides customizable library settings. To navigate to the library settings, click the Library tab in a SharePoint library, and then click Library Settings, as shown in Figure 4.10.

Figure 4.10. Library settings.

The Library Settings page will display, as shown in Figure 4.11. The General Settings and Permissions and Management settings are available in any SharePoint library. There is nothing special for a library that contains reports, so there is no discussion of these items here other than to say that you can use them to customize various aspects of a SharePoint library, and this is relevant to a library that contains reports.

Figure 4.11. Document Library Settings page.

For more information about customizing the content types for a SharePoint library that contains reports, see Chapter 3, “Reporting Services Setup and Installation.”

Shared Schedules

A number of report management functions can be scheduled, such as generating report snapshots and setting a time for a cached report to expire. When configuring these items, you can specify a custom schedule or a shared schedule. A shared schedule can be created once and then used for more than one report management function. Follow these steps to create a shared schedule:

1. Make sure that the SQL Server Agent service is running. You can start the service from the SQL Server Configuration Manager, which is available from the SQL Server 2008 R2 program group in the start menu.
2. Click Site Actions (available on any SharePoint page) to display the drop-down menu, as shown in Figure 4.12.

   Figure 4.12. Site Actions menu.

   

3. Click Site Settings to display the Site Settings page, as shown in Figure 4.13.

   Figure 4.13. Site Settings page.

   

4. Click Manage Shared Schedules under the Reporting Services category to display the Manage Shared Schedules page, as shown in Figure 4.14.

   Figure 4.14. Manage Shared Schedules page.

   

5. Click Add Schedule and fill in the values, as shown in Figure 4.15.

   Figure 4.15. Add Shared Schedule page.

   

The preceding example is a shared schedule used for any number of report management functions where things need to be available first thing Monday morning. This could be used to schedule the generation of report snapshots, expire cached reports, and so on. The schedule is now displayed, as shown in Figure 4.16.

Figure 4.16. Shared schedules.

By default the schedule status is set to Ready. You can disable the schedule by clicking the check box and clicking Pause Selected Schedules. You can enable the schedule by clicking the check box and clicking Run Selected Schedules.

Processing Options

Processing options provide settings for data refresh options, processing timeout, and report snapshots. The Processing Options page for a particular report is available from the document context menu and is shown in Figure 4.17.

Figure 4.17. Report processing options.

You have the following three data refresh options for specifying when data shown on a report is retrieved:

• Use Live Data: Retrieves data from the data source every time the report is executed. This is the default and ensures that the report always uses current data.

• Use Cached Data: Retrieves data from a cache on the report server instead of querying the underlying data source each time the report is executed. When the cache expires, the data is retrieved from the data source and then cached for future use.

• Use Snapshot Data: A copy of the report is stored on the report server. This report copy is rendered when the report is executed. When the snapshot expires, the report is run with live data, and a new snapshot is created.

When you select the data refresh option Use Cached Data, the Cache Options are displayed below the data refresh options as shown in Figure 4.18.

Figure 4.18. Cache options.

The cache options enable you to specify when the cached copy of a report expires. The choices are a number of minutes, a shared schedule, or a custom schedule.

When you select the data refresh option Use Snapshot Data, the Data Snapshot Options are displayed below the data refresh options as shown in Figure 4.19.

Figure 4.19. Snapshot options.

A snapshot is a copy of a report generated at a point in time. It is especially useful to archive a report at the end of a reporting period, such as a week, month, quarter, or year. Data snapshot options enable scheduling of data processing based on a shared schedule or a custom schedule, and an option to create or update the snapshot after completing the page.

The processing timeout option provides for limiting the amount of processing time it takes to run the report. If the allotted time is exceeded, processing is aborted, and the report is not generated. The setting on this page could override the server default, but choosing the server default is probably best in most instances.

The history snapshot options determine whether to allow a snapshot to be created manually, to store all snapshots in report history, and schedule snapshot generation. Allowing manual snapshot generation provides users with the flexibility to archive a copy of a report at any time. To generate a snapshot manually, select View Report History from the document context menu, and then click New Snapshot, as shown in Figure 4.20.

Figure 4.20. Report history.

The initial snapshot shown here was created after completing the processing options page for the report and checking the option Create or update the snapshot when this page is saved.

Data Sources

A data source defines information needed to be able to retrieve data for a report. A report may use one or more data sources. A dataset defines the query or stored procedure to be executed to retrieve the necessary data; a dataset is associated with a particular data source. Select Manage Data Sources from the document context menu for a particular report to edit the data source information. The Manage Data Sources page is displayed showing the data sources used by the report, as shown in Figure 4.21.

Figure 4.21. Data sources.

Click the data source name to display the data source properties page, as shown in Figure 4.22.

Figure 4.22. Data source properties.

A shared data source is one that can be reused in multiple reports. A custom data source is embedded in a single report. As a general rule, using shared data sources is a best practice, allowing the data source details to be defined once (and changed if necessary) and then used in multiple reports. The Data Source link points to the actual data source definition.

A data source can be created or edited from a SharePoint document library. Follow these steps to create a data source:

1. Click the Documents tab in a SharePoint library.
2. Click New Document, and then select Report Data Source, as shown in Figure 4.23.

   Figure 4.23. Create a data source.

   

Note that for the report options to be shown in the drop-down menu in Figure 4.23, the appropriate content types need to be added to the document library. See the “Add Content Types to a Document Library” section in Chapter 3 for details.

After you click Report Data Source on the New Document menu, the Data Source Definition dialog displays. Fill in the details, as shown in Figure 4.24.

Figure 4.24. Data source definition.

Note that Enable This Data Source must be checked before it can be used by a report.

An existing data source can be changed by clicking the glyph next to the data source in the document library and then selecting Edit Data Source Definition from the context menu, as shown in Figure 4.25.

Figure 4.25. Edit data source definition.

Shared Datasets

A dataset is a query definition (or the name of a stored procedure) that retrieves data to be rendered on a report. A shared dataset is defined once and can then be used in multiple reports. For instance, a dataset used to populate the list of states is a good candidate for a shared dataset because the same parameter drop-down might be used in many different reports. To view or manage the shared datasets used by a report, follow these steps:

1. Click the glyph next to the report and select Manage Shared Datasets from the document context menu, as shown in Figure 4.26.

   Figure 4.26. Manage shared datasets.

   

2. The dataset information page is displayed, as shown in Figure 4.27. Click the dataset name to view or edit it.

   Figure 4.27. Shared datasets.

   

3. The dataset information is displayed, as shown in Figure 4.28.

   Figure 4.28. Shared dataset properties.

   

The dataset link is the only property available, and it is the file that contains the dataset definition.

To edit a shared dataset, follow these steps:

1. Navigate to the document library where the dataset is stored.
2. Click the glyph next to the dataset and then select Edit in Report Builder, as shown in Figure 4.29.

   Figure 4.29. Edit a shared dataset.

   

3. Report Builder opens and allows changes to the dataset, as shown in Figure 4.30.

   Figure 4.30. Edit a shared dataset in Report Builder.

   

Report Builder allows editing the data set query by using the familiar query designer or by entering the query as text. The default for editing the dataset query is based on how the query was created. Figure 4.30 has the Edit as Text button selected and the dataset query is displayed for manual editing because this is how the dataset query was saved. Edit the query as necessary and then save it by clicking the disk icon on the top of the Report Builder window.

The other options available for fine-tuning the shared dataset are as follows:

• Click Select to view or edit the data source properties.

• Click Set Options to view or edit the fields, options (collation, case sensitivity, and so on), filters, and parameters.

• Click Import to navigate to a .SQL file or report and copy an existing query.

• Click the exclamation point (next to the Import button) to execute the query.

• Select Text or Stored Procedure from the Command Type drop-down and edit the query as appropriate.

Parameters

Reports can include parameters that can be set at runtime to filter the data rendered in the report. Parameters can be defined only in the report itself. After a report is deployed, some changes can be made to the existing parameters. To edit or view report parameters, follow these steps:

1. Navigate to a report in a document library.
2. Click the glyph next to the report, and select Manage Parameters, as shown in Figure 4.31.

   Figure 4.31. Manage parameters.

   

3. The Manage Report Parameters page is displayed, as shown in Figure 4.32.

   Figure 4.32. Report parameters.

   

4. To edit a parameter, click the parameter name to display its properties, as shown in Figure 4.33.

   Figure 4.33. Report parameter properties.

   

You can use the parameter properties to set a default value for the parameter and set its display status. If a parameter would always have the same value, specify a default value and change the Display option to Hidden or Internal so that users are not prompted for it.

Subscriptions

A subscription provides the capability to execute a report based on an event or a schedule, select the output format for the report, and select the report delivery mode (for example, email, store in a Windows file share, or store in a SharePoint document library).

Follow these steps to create a subscription for a particular report:

1. Select Manage Subscriptions from the document context menu, as shown in Figure 4.34.

   Figure 4.34. Manage subscriptions.

   

2. The Add Subscriptions page is displayed, as shown in Figure 4.35.

   Figure 4.35. Add subscriptions.

   

3. Click Add Subscription and then fill in the Subscription properties page, as shown in Figure 4.36.

   Figure 4.36. Subscription example.

   

This example executes the report whenever a snapshot is generated and stores the report as a PDF in a SharePoint document library.

Another option is the data-driven subscription, which enables you to specify a query to retrieve the values for the various subscription settings. Each subscription option can be specified by retrieving the value from the query, selecting from a drop-down list, or entering a static value.

Follow these steps to create a data-driven subscription:

1. Click Add Data-Driven Subscription on the Manage Subscription page and fill in the Create a Data-Driven Subscription page, as shown in Figure 4.37.

   Figure 4.37. Create a data-driven subscription.

   

2. Click the Validate button to check that the query executes successfully.
3. After completing this page, click Next to proceed to the Parameters page where you can enter any parameter values required for running the report. The Parameters page is always displayed even if the report does not require any parameters.
4. Enter the parameter values (if any) and click Next to proceed to the Delivery Options page and fill in the values as appropriate for your subscription.

The Delivery Options page has more settings than can be easily shown in a single screen shot. For many of the settings, the choice is to either specify a static value or get the value from a column in the query (defined on the previous page). Figure 4.38 has the Delivery Type and To: settings.

Figure 4.38. Delivery Type and To: options.

The following delivery types are available:

• Windows File Share: Saves the report as a file in the folder you specify

• E-Mail: Sends the report as an e-mail attachment

• SharePoint Document Library: Saves the report as a document in a SharePoint library that you specify

• Null Delivery provider: Runs the report but does not save it anywhere; use this option to execute a report and update the cache

Note that the To: setting gets its value from the EmailAddress column in the query.

Figure 4.39 shows the settings for whether to include the report as an attachment to the email and the render format for the report.

Figure 4.39. Include Report and Render Format options.

Figure 4.40 shows the settings for the email subject.

Figure 4.40. Email subject options.

Figure 4.41 shows the setting for the delivery event, which is the trigger for executing the data-driven subscription. The delivery event can be when a snapshot is created or on a schedule. Daily Refresh is a shared schedule that executes at 5 a.m. on the days noted; it is used to execute report subscriptions that are to be available when people arrive each day.

Figure 4.41. Delivery event options.

After you complete the data-driven subscription setup, the manage subscriptions page is displayed as shown in Figure 4.42.

Figure 4.42. Manage subscriptions.

Summary

A number of report management functions are integrated into SharePoint when Reporting Services is configured in SharePoint Integrated mode. In addition, the built-in document management functions provided by SharePoint are available (for example, versioning, check in, and check out). This simplifies report management and provides an intuitive user interface that business users can easily master.

Best Practices

The following are best practices from this chapter:

• Use shared data sources to store this information in one place rather than embed it in each report. If the data source information changes, the data source can be updated rather than each individual report.

• Use shared schedules so that a single schedule can be defined and used to trigger multiple report management functions. If the schedule needs to change, only the shared schedule needs to be updated, not the individual report management values.

• Use shared datasets to define reusable queries and then reference them in multiple reports.

• Take advantage of subscriptions to schedule report generation during off hours to reduce the load on servers and to provide information to users automatically.