Setting Report History and Snapshots


Most of the reports, including many of the samples, don't really tax modern hardware. However, in some cases, there might be a report in production that could be hundreds of pages, or could tax the database server with expensive queries. In these cases, it would be helpful to be able to cache reports , so that the report-rendering engine and the database server don't waste resources.

SSRS attains the preceding goals by retaining something called report history. The history is a collection of previously run reports called snapshots. A snapshot is a copy of the report at a point in time. If the report layout or data changes, the snapshot remains the same and does not reflect the update.

Report Server Settings to Affect Report History

There are two levels of settings when setting report history. The first level is Report Server-wide, and is accessible through the Site Settings link or by clicking on the server itself in Object Explorer through SQL Server Management Studio. The other settings are at the report level, and can override the sitewide settings.

The only global parameter for the Report Server as a whole is how many snapshots to keep in history. By default, the sitewide settings are set to keep an unlimited number of snapshots in history; however, most reports are set to run on the fly, and not set to render from a snapshot. If an administrator decided to change this value, the old snapshots would be deleted as the maximum number of snapshots is reached.

The rest of the settings for snapshots are set at the report level, and can be set either through the Report Server or through SQL Server Management Studio. To access the setting through the Report Manager, click the report, select the Properties tab, and select History. From SQL Server Management Studio, right-click on the report and select Properties and then select History.

The first option is Allow Report History to Be Created Manually.

This allows the New Snapshot button to appear on the History page in Report Manager. Using this button, users can then select to create a snapshot themselves .

The second option is Store All Report Execution Snapshots in History. This stores a copy of each snapshot in the report history. Users can then look back over time and see how the report has changed.

Another option allows users/administrators to generate report snapshots on a custom or shared schedule. The snapshots generated over time will help to form the history.

The last option defines how many snapshots are to be kept in history. This allows us to use the Report Server's default setting or override it by allowing a limited number or infinite number of copies.

From a security perspective, users must have the Manage Report History task inside their roles to generate snapshots. The end users must also have the View Reports role to view the report history. Report snapshots are not meant for, and not recommended to be used on, a report that contains secure or confidential data. If a report uses a data source that prompts the user for a password, or one that requires integrated security, the snapshot cannot be created.

Creating Snapshots

Snapshots contain the following data:

  • A copy of the resultset(s) brought back by the data sources of the report.

  • The report definition at the time when the snapshot was created. Keep in mind that this will not take into account any recent changes.

  • Parameter values that were used while processing the reports and/or query.

  • Any embedded resources for a report. If the report relies on an outside resource, that resource is not saved on the RS database.

To add a snapshot using Report Manager, follow these steps:

1.
Open the report for which you want to create a snapshot, and click the History tab.

2.
Click the New Snapshot button, as shown in Figure 17.17.

Figure 17.17. Creating a snapshot.

3.
A new snapshot should appear, with the date it was created and the size .

To create a snapshot using SQL Server Management Studio, complete the following steps:

1.
Using Object Explorer, navigate to the History folder under the report for which you want to create a snapshot.

2.
Right-click the History folder, and select New Snapshot. See Figure 17.18.

Figure 17.18. Creating a snapshot through SQL Server Management Studio.

3.
Right-click on the History folder again and select Refresh to make sure the snapshot appears.

Deleting Snapshots

After snapshots have been created, they can only be deletednot modified. There are two ways to delete a snapshot.

The first way involves using either SQL Server Management Studio or Report Manager to delete individual snapshots in the report history. From Report Manager, navigate to the report and click the History tab. Then select the check boxes next to the individual snapshots and click the Delete button. From SQL Server Management Studio, use Object Explorer to navigate to the History folder under the report, right-click it, and select History.

Right-clicking on the History folder gives the user an option to Delete All Snapshots. Otherwise, right-click on an individual snapshot and select Delete.

The second way to delete snapshots is to simply lower the number of snapshots the Report Server should keep. This forces the older snapshots to be deleted as needed.



Microsoft SQL Server 2005 Reporting Services
Microsoft SQL Server 2005 Reporting Services
ISBN: 0672327996
EAN: 2147483647
Year: 2004
Pages: 254

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net