MicrosoftВ® SQL Server(TM) 2005 Reporting Services Step by Step (Step by Step (Microsoft))

The report history feature of the Report Manager enables you to keep copies of a report’s past execution. This lets you save the state of your data without having to save copies of the data itself. You can keep documentation of inventory levels, production schedules, or financial records. You can look back in time, using the report history to do trend analysis or to verify past information.

Enabling Report History

To demonstrate the report history feature of Reporting Services, we need a report whose results change often. It just so happens we have such a report in our Chapter08 solution. The TransportMonitor report provides different values every time the report is run. We can move that report to the Report Server, and then enable the report history.

1. Open the Report Manager and navigate to the Chapter 08 folder.

2. Use the Upload File button to upload the TransportMonitor report from the Chapter 08 solution.

3. Select Show Details, if it is available in the report viewer toolbar. If it is unavailable, you are already in Show Details mode.

4. Click the icon in the Edit column for the TransportMonitor report. The Properties page appears.

5. Click Parameters on the left side of the page. The Parameters page appears.

6. Click the Has Default check box and type 1304 for Default Value.

7. Click Apply.

8. Click Data Sources on the left side of the page. The Data Sources page appears.

9. Click Browse. The Data Source page appears.

10. Use the tree view to find the 2006 Conference folder in the tree structure. The 2006 Conference folder is inside the Galactic Delivery Services folder.

11. Select the Galactic shared data source and click OK.

12. Click Apply.

13. Click History on the left side of the page. The History Properties page appears, as shown in Figure 11–7.

    Figure 11–7: The History Properties page

14. Make sure the Allow History to Be Created Manually check box is checked. If it is not, check it and click Apply.

15. Click the View tab at the top of the page. Remember, this report has autorefresh set. After a few seconds, the report refreshes and new data is displayed.

Manually Creating a Report History Snapshot

One way to create a report history is to do so manually. We can give this a try in the following example:

1. Select the History tab. (This is the History tab along the top, not the History link on the left side of the page.) The Create/View History page appears.

2. Click the New Snapshot button in the report viewer toolbar. An entry for a report history snapshot appears.

3. Click the New Snapshot button two more times to create two more report history snapshots, as shown in Figure 11–8.

   Figure 11–8: The Create/View History page

4. Click the link in the When Run column to the first report history snapshot you created. This report should appear in a new browser window.

5. Open the other two report history snapshots and compare all three.

As with the cached report instances, the report history snapshots store the intermediate format of the report. Because of this, you can export this report to any of the rendering formats.

1. Select one of your browser windows containing a report history snapshot.

2. Export the snapshot to the Acrobat (PDF) file format and open it in Adobe Acrobat Reader.

3. Close Acrobat Reader and the browser windows containing your report history snapshots.

Report History Snapshots and Report Parameters

To make our TransportMonitor report work with report history snapshots, we had to provide a default value for the transport number parameter. These parameters cannot be changed when each snapshot is created. (They can be changed, however, if the report is run normally through the Report Manager.)

Essentially, we are saving report history snapshots for only one transport. To save report history snapshots for other transports, we need to create linked reports with parameters defaulted to the other transport numbers.

1. Select the Properties tab.

2. Click General on the left side of the page.

3. Click Create Linked Report.

4. Type Transport 1305 Monitor for Name and The Transport Monitor Report for Transport 1305 for Description.

5. Click OK. The linked report executes.

6. Select the Properties tab.

7. Click Parameters on the left side of the page.

8. Change Default Value to 1305.

9. Click Apply.

10. Click the History tab.

11. Click New Snapshot.

12. Click the entry for the new snapshot to view it. You can see this is a snapshot for transport number 1305.

13. Close the browser window containing your report history snapshot.

We can create as many linked reports as we need to collect report history snapshots for the different possible parameter values. Remember, linked reports all point back to a single report definition. If the TransportMonitor report is ever updated, it only needs to be deployed in one location, and all the linked reports will have the updated report definition.

Additional Methods for Creating Report History Snapshots

You can create report history snapshots in two other ways, in addition to the manual method just described. You can instruct the Report Server to create a report history snapshot each time it creates an execution snapshot. With this setting turned on, any time the Report Server creates an execution snapshot—either manually or on a scheduled basis—a copy of that execution snapshot is saved as a report history snapshot.

You can also set up a schedule to create your report history snapshots. Let’s give that a try:

1. Click the Chapter 08 link at the top of the page.

2. Click the icon in the Edit column for the TransportMonitor report. (The original report, not the linked copy.) The Properties page appears.

3. Click History on the left side of the page.

4. Check the Use the Following Schedule to Add Snapshots to Report History check box.

5. Report-Specific Schedule is selected by default. Click Configure next to Report-Specific Schedule. The Schedule page appears.

6. Select Hour.

7. Change the Hourly Schedule to run every 0 hours 1 minutes. Set Start Time to five minutes from now.

8. Today’s date should be selected for Begin Running This Schedule On.

9. Check the Stop This Schedule On check box and set it to tomorrow’s date.

10. Click OK to return to the History Properties page. Note the description of the schedule you just created under Report-Specific Schedule.

11. Click Apply to save your changes to the history snapshot settings.

12. Select the History tab.

As each minute passes beyond the time you chose for the schedule to start, a new report history snapshot is created. You need to refresh your browser to see the new history snapshots in the list.

Report History Snapshots and Security

Not all users can change report history snapshot properties. To change the report history snapshot properties for a report, you must have rights to the Manage Report History task. Of the four predefined security roles, the Content Manager and My Reports roles have rights to this task.

Managing Report History Snapshots

You will not usually have a report that requires a new report history snapshot every minute of the day, as we set up in our example. Even so, report history snapshots can start to pile up if you let them. Making business decisions about the number of history snapshots to save for each report is important. Even more important, then, is to implement those business decisions and manage the number of history snapshots being saved on the Report Server.

Setting Limits on the Number of Report History Snapshots

Reporting Services provides a way to limit the number of history snapshots saved for any given report. Let’s take a look and put a limit on our TransportMonitor report snapshots at the same time.

1. Select the Properties tab.

2. In the Select the Number of Snapshots to Keep section of the page, select the Limit the Copies of Report History option.

3. Set the limit to 5.

4. Click Apply to save your changes to the history snapshot settings.

5. Click OK in response to the warning dialog box.

6. Select the History tab.

If you waited long enough to accumulate more than five report history snapshots, you see the list was reduced to the five most recent history snapshots. The older history snapshots were automatically deleted. As each new history snapshot is created, the oldest history snapshot is deleted, so the total always remains at five. Again, remember, you need to refresh your browser to see these changes as each minute passes.

We chose to set a limit on the number of history snapshots saved for this report. In addition to this option, you have two others to choose from (see Figure 11–7). You can keep an unlimited number of history snapshots, or you can use the default setting for history snapshot retention. You see how to change this default setting in the section “Configuration Options and Default Values.”

Manually Deleting Report History Snapshots

In addition to using the history snapshot limit on the History Properties page, you can also manually delete unwanted history snapshots.

1. Refresh your browser.

   Caution

   If you reached the limit of five history snapshots, the Report Server is automatically deleting old history snapshots as new ones are created. If your Create/View History page is not current, you could try to delete a history snapshot that has already been removed by the Report Server. This results in an error.

2. Check the check box in the Delete column for three of the snapshot history entries.

3. Click Delete in the History tab toolbar.

4. Click OK to confirm the deletion.

The Report Server again accumulates history snapshots for this report until it has reached our five snapshot limit. At that point, it again deletes the oldest history snapshot as each new one is created.

Disabling Report History Snapshot Creation

We can now disable the creation of report history snapshots for this report, so we are not wasting valuable execution cycles.

1. Select the Properties tab.

2. Uncheck the Use the Following Schedule to Add Snapshots to Report History check box.

3. Click Apply.

4. Select the History tab.

New history snapshots are no longer created for this report on a scheduled basis. Note, however, that the existing history snapshots were not deleted. These history snapshots are still available for viewing, even though the schedule that created them was disabled.

Updating Report Definitions and Report History Snapshots

One of the best features of report history snapshots is this: they are not lost if the definition of the underlying report is changed. Let’s see this in action.

1. Start Visual Studio or the Business Intelligence Development Studio and open the Chapter 08 solution.

2. Open the TransportMonitor report layout.

3. Select the Data tab. Select the TransportMonitor dataset.

4. Click the ellipsis button (…) to view the Dataset dialog box.

5. Select the Filters tab.

6. Select =Fields!Item.Value from the Expression column drop-down list. Select != from the Operator column drop-down list. Enter Thruster in the Value column. This filter removes the thruster data from the report.

7. Click OK to exit the Dataset dialog box.

8. Select the Preview tab. Check 1304 in the Transports drop-down list and click View Report. Note the Thruster graph is missing.

9. Click Save All in the toolbar.

10. Right-click the TransportMonitor report in the Solution Explorer and select Deploy from the Context menu.

11. After the deployment has succeeded, close Visual Studio or the Business Intelligence Development Studio.

12. Return to the Report Manager in your browser.

13. Select the View tab for the TransportMonitor report. Note, the report now includes our change, eliminating the thruster data from the report.

14. Select the History tab. We still have some report history snapshots based on the old report definition.

15. Click New Snapshot to manually create a report history snapshot based on the new report definition. Our five history-snapshot limit is still in effect, so one of the old history snapshots may have to be deleted to make room for the new one.

16. Click the most recent history snapshot to view it. It does not contain thruster data because it is based on the new report definition.

17. Close this browser window.

18. Click the oldest history snapshot to view it. It does contain thruster data because it is based on the old report definition.

19. Close this browser window.

Just like the cached report instance, the report history snapshot contains both the report definition and the dataset. Therefore, it is unaffected by subsequent changes to the report definition.