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 Nov 30, 2007

Snapshot Reports II: SQL Server Management Studio Perspective - Page 2

By William Pearson

Settings to Configure Snapshot Caching from SQL Server Management Studio

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 SQL Server Management Studio. 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

In Part I, we noted 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. In this article, we will examine the use of SQL Server Management Studio to achieve similar ends. Among many other management capabilities, SQL Server Management Studio affords us a straightforward means of flexibly performing administration of reports and related Reporting Services resources.

Let’s take a look at how we can work with Snapshot caching settings for a report within the SQL Server Management Studio. To do this, we will take the following steps:

1.  Start Microsoft SQL Server Management Studio.

2.  Select Reporting Services in the Server type selector of the Connect to Server dialog that appears, if necessary.

3.  Type / select the server name (server name / instance, if appropriate) in the Server name selector.

4.  Supply authentication information, as required in your own environment.

The Connect to Server dialog appears similar to that depicted in Illustration 3.

Illustration 3: Connecting to Reporting Services ...

5.  Click the Connect button to connect with the specified server.

The Microsoft SQL Server Management Studio opens. The Reporting Server to which we have connected appears atop the Object Explorer pane.

6.  In the Object Explorer pane, expand the Home folder (click the “+” sign to its immediate left), appearing underneath the server within which we are working.

7.  Navigate to, and expand, the subfolder containing a sample report with which to complete the steps of this section, as appropriate (my sample reports have been deployed within the AdventureWorks Sample Reports folder, and appear, with this step, as shown in Illustration 4.

Illustration 4: The Sample Reports Appear

8.  Right-click the Sales Reason Comparisons report (or another report of your choice).

9.  Select Properties from the context menu that appears next, as depicted in Illustration 5.

Illustration 5: Accessing Report Properties from the Object Explorer ...

The Report Properties dialog for the selected report opens.

10.  Click Execution in the Select a page pane on the left side of the dialog, 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.

The Execution properties page opens in the area to the right of the Select a page pane. Here 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.

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

12.  Select (by placing a checkmark in the checkbox to its left, if necessary) Create a snapshot on the following schedule.

13.  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 Create Custom Schedule page, where we can establish a schedule for Snapshot report generation.

14.  Within the Range of Recurrence section in the upper half of the page, to the right of the Begin running this schedule on, select January 1, 2008 (or another, more relevant, date for the time at which you are performing these procedures) using the calendar selector, as depicted in Illustration 7.

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

15.  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.

16.  Select Month as the Type, within the Recurrence Pattern section just beneath the Recurrence Range section.

17.  Within the scheduling details settings in the boxed section that next appears below, modify the Start time atop the section to read “12:00:00 AM.”

18.  Ensure that all months have been selected via the associated checkboxes.

19.  Select the radio button labeled On calendar day(s), at the bottom of the boxed section, and supply a “1” in the input box to the right of the label.

Our Create Custom Schedule page settings, which establish a schedule for Snapshot generation in the earliest minutes of the first day 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, as we found in our examination of performing parallel settings via Report Manager (in Part I), initiate Snapshot execution from either the individual report level (the foregoing procedure) or from a shared schedule.

20.  Click OK (at bottom left) to save settings and to return to the Execution page within the Report Properties dialog.

21.  Select the radio button labeled Create a snapshot of the report when this page is saved, if you wish to create a Snapshot report immediately.

Our settings on the Execution page, among which is included a summary of our newly established schedule for Snapshot generation, appears as depicted in Illustration 9.

Illustration 9: Execution Page Settings with Schedule Summary

NOTE: For details surrounding the Report Execution Timeout Defaults settings in the bottom section of the Execution page, see Report Execution Caching Parts I and II.

22.  Click OK to apply our settings (creating a Snapshot in the process, if we have directed the Report Server to do so using the Create a snapshot of the report when this page is saved setting we examined above), and to dismiss the Properties dialog for the report we have chosen.

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