Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Oct 29, 2007

Snapshot Reports I: Report Manager Perspective - Page 2

By William Pearson

Settings to Configure Snapshot Caching from Report Manager

As we have learned, the primary objective of Snapshot caching is to support the maintenance of snapshots (or “histories”) of selected reports over time for comparison or other purposes. “Fringe benefits” include enhanced general reporting performance, especially for more complex or larger reports, because the Snapshot reports are generated from cached intermediate files in the Report Server database.

In a manner similar to that of setting other caching options in Reporting Services, we make the required settings within the Report execution properties for the respective report. In the subsections that follow, we will examine these settings for a sample report from the perspective of the Report Manager. We will do so, as we have in other articles of this subseries, using a report from among the samples that are available to anyone installing Reporting Services 2005.

If you have not installed the samples, you’ll need to install them to use the sample report with which we perform the steps below. If you prefer to use a local report, the steps will be approximately the same for accessing the property settings for your report of choice.

Configure Snapshot Caching

Most of us are aware that, among the graphical user, command-line and programmatic interfaces that Reporting Services makes available to us for performing management activities, Report Manager is perhaps the most user-friendly. This web-based management application interfaces with the Report Server via the Web Service API, and is easily accessed using a web browser. Report Manager has many uses, and affords us a straightforward means of flexibly managing access to all reports and related resources.

Let’s take a look at how we can make Snapshot caching settings for a report within the Report Manager. To do this, we will take the following steps:

1.  Open an instance of the web browser on the PC.

2.  Point the browser to the following URL:


NOTE: Replace <Server_Name> in the above with the name of the server hosting Report Manager in the local environment. Also, replace the default Reports directory with the appropriate virtual directory established in the local environment, if yours is different.

Report Manager’s home page next appears, somewhat similar to that depicted in Illustration 3.

Illustration 3: Accessing the Report Manager from the Web Browser

3.  Access the sample reports (mine are within the AdventureWorks Sample Reports directory shown in Illustration 3 above), by navigating into their containing folder(s) from the home page.

4.  Click-select the Sales Reason Comparisons report from among the sample reports listed, as shown in Illustration 4.

Illustration 4: Opening the Sample Sales Reason Comparisons Report ...

The report briefly executes, and then opens.

5.  Click the Properties tab atop the report, as depicted in Illustration 5.

Illustration 5: Accessing Report Properties ...

6.  Click the Execution tab that appears on the left panel of the page, as shown in Illustration 6.

Illustration 6: Accessing the Execution Settings for the Report ...

Note: Credentials must be stored, and default parameters must be in place, for any report for which we wish to configure Snapshot caching.

We arrive at the Execution properties page, where we can set execution properties for the currently selected report only. These options determine when report processing occurs, as we have seen in other articles of this series. Among other activities, we can make settings to govern a report run during off-peak hours, and the like, for flexible resource scheduling within our own environments.

7.  On the Execution page, click-select the radio button to the immediate left of Render this report from a report execution snapshot.

8.  Select Use the following schedule to create execution snapshots.

9.  Leaving the radio button to the immediate left of Report-specific schedule selected, click the Configure button to its immediate right.

We arrive at the schedule details page, where we can establish a schedule for Snapshot report generation.

10.  Within the Schedule Details section in the upper half of the page, select the radio button labeled Month on the left side of the section.

11.  Within the Monthly Schedule box in the Schedule Details section, ensure that all months have been selected via the associated checkboxes.

12.  Select the radio button labeled On calendar day(s) within the same Monthly Schedule box, supplying a “1” in the input box to the right of the label. (Clear any residual settings in the On day of week checkboxes just above.)

13.  Modify the Start time at the bottom of the box to read “12:00 AM.”

14.  In the Start and end dates section in the bottom portion of the page, click the calendar icon to the right of the input box labeled Begin running this schedule on.

15.  Select November 1 (or other, more relevant date for the time you are performing these procedures) using the calendar selector, as depicted in Illustration 7.

Illustration 7: Selecting a Date to Begin Snapshot Execution ...

16.  In like manner, select a date exactly a year away in the input box labeled Stop this schedule on, ensuring that the selection is enabled via a checkmark in the checkbox to the left of the label.

Our settings, which establish a schedule for Snapshot generation at midnight on the first of every month, to continue through one annual cycle, appear as shown in Illustration 8.

Illustration 8: Schedule Details, with Our Input

It is useful to consider that we can, of course, initiate Snapshot execution from either the individual report level (the foregoing procedure) or from a shared schedule.

17.  Click OK (at bottom left) to save settings and to return to the Execution page.

18.  Select the radio button labeled Create a report snapshot when you click the Apply button on this page, if you wish to create a Snapshot report immediately.

Our settings on the Execution page, together with a summary of our newly established schedule for Snapshot generation, appear as depicted in Illustration 9.

Illustration 9: Execution Page Settings with Schedule Summary

19.  Click Apply.

Unless we dictate differently, a single Snapshot will be maintained with the ReportServerDB database. By default, whenever a new Snapshot report is created, it will be swapped with the single Snapshot already in place. To maintain historical Snapshots within the database, we need to make the appropriate settings on the History tab, as we shall see in the next section.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM