Microsoft SQL Server 2008 Reporting Services: Deploying Reports to a Server

Configuring Report Execution Properties

Report execution transforms a report stored in the ReportServer database into a rendered report that the user views. As part of the execution process, the queries defined in the report’s datasets execute and return data to the report server, which then uses the report definition file to determine how to construct the report with the retrieved data. At this point, the report is in an intermediate format that is sent to the applicable rendering extension to produce the final rendered report.

Report performance is determined largely by how much data is retrieved by the report and how much processing is required to produce the rendered report. If the users don’t need the most current data in the database each time the report executes, you should consider any execution option that generates the report’s intermediate format in advance of the user viewing the report to achieve optimal performance. The following sections in this chapter explain the relationship between each execution option and generation of the intermediate format.

Executing a Report on Demand

When a user clicks the link for a report, the report executes on demand, returning the most current data to the report server which then produces an intermediate format. The intermediate format is stored in the ReportServerTempDB database in a session cache to speed up subsequent requests by the same user for the same report during the current session. For example, after the user views the report online, the user might export the report to an Excel format. By having the intermediate format in session cache, the online version and the Excel version of the report match. Furthermore, the Excel version renders very quickly from the intermediate format of the report because the report server does not need to wait for the query to execute again.

In this procedure, you configure the execution properties of the Employee Sales Moving Average report to execute on demand with a 60-second timeout.

Configure report execution on demand with a 60-second timeout

1. In Internet Explorer, open the execution properties page of the Employee Sales Moving Average Report.

   • Native mode: In Report Manager, click the Home link at the top of the window, click the For Review link, click the Employee Sales Moving Average link, click the Properties tab, and then click the Execution link to view the execution properties, as shown here.

     

   • Integrated mode: In the navigation bar, click Reports, click the For Review link, point to Employee Sales Moving Average, click the Edit button appearing to the right of the report title, and select Manage Data Sources. Click the AdventureWorksDW2008 link, and then click the ellipsis button to the right of the Data Source Link box. In the Select An Item dialog box, click Up twice, click the Data Connections link, select AdventureWorksDW2008, click OK twice, and click Close. Now the link to the data source, which was broken when you moved the report to the For Review folder, is fixed, and you can proceed to set the execution properties. Point to Employee Sales Moving Average, click Edit, and then select Manage Processing Options.

   The two execution properties for a report are the type of execution and the report execution timeout, as shown in the following table.

   For This Property Type	On a Native-Mode Report Server, the Default Property Is	On an Integrated-Mode Report Server, the Default Property Is
   Execution type	Do Not Cache Temporary Copies Of The Report	Use Live Data
   Execution timeout	Use The System Default Setting	Use Site Default Setting

   You learn how to configure the System/Site default setting in Chapter 12, “Performing Administrative Tasks,” if you want to use a value other than the 1,800-second default for all reports. Alternatively, you can override this value, as shown in the next step.

2. Change the report execution timeout value to 60.

   • Native mode: In the Report Execution Timeout section, select Limit Report Execution To The Following Number Of Seconds, and then, in the text box to the right, type 60, and click Apply.

   • Integrated mode: In the Processing Time-out section, select Limit Report Processing (In Seconds), and then, in the text box to the right, type 60, and click OK.

3. View the report to confirm execution on demand.

   • Native mode: Click the View tab and note the date-time stamp below the chart.

   • Integrated mode: Click the Employee Sales Moving Average link and note the date-time stamp below the chart.

4. Above the report title, click the For Review link, click the Employee Sales Moving Average link, and then note the new date-time stamp below the chart.

Caching Reports

Caching a report is helpful when you want to strike a balance between having current data in the report and having faster access to the online report. The first time a user clicks the link for a report configured to cache, the report execution process is identical to the on-demand process. However, the intermediate format is flagged as a cached instance and stored in ReportServerTempDB until the time specified by the cache settings expires. Meanwhile, if any user requests the report during the time that it still resides in cache, then the report server retrieves the intermediate format and renders the report so long as the user requests the same combination of parameter values. This process is much faster than retrieving the data, producing the intermediate format, and then rendering the report.

If a user requests a different set of parameter values for a cached report, then the report processor treats the request as a new report executing on demand, but flags it as a second cached instance. If users are constantly looking at different parameter combinations and not reusing parameter values, then using cached reports is not helpful.

In this procedure, you configure the execution properties of the Employee Sales Moving Average report to cache the report and expire the cached instance after 60 minutes.

Configure caching with expiration after 60 minutes

1. In Internet Explorer, open the execution properties page of the Employee Sales Moving Average report.

   • Native mode: Open the report, if necessary, click the Properties tab, and then click the Execution link to view the execution properties.

   • Integrated mode: If the report is open, click the For Review link. Otherwise, type http://<servername>/sites/ssrs/ReportsLibrary/For Review (replacing <servername> with the name of your server), point to Employee Sales Moving Average, click the Edit button (located to the right of the report title), and then select Manage Processing Options.

2. Set the execution type to caching with an expiration after 60 minutes.

   • Native mode: Select Cache A Temporary Copy Of The Report, and then, in the Expire Copy Of The Report After A Number Of Minutes text box, type 60. Click Apply.

   • Integrated mode: Select Use Cached Data, and then, in the Elapsed Time In Minutes text box, type 60, as shown here, and click OK.

     

3. View the report to confirm execution.

   • Native mode: Click the View tab and note the date-time stamp below the chart.

   • Integrated mode: Click the Employee Sales Moving Average link.

4. Above the report title, click the For Review link, click the Employee Sales Moving Average Report link, and then note the date-time stamp below the chart has not changed to reflect the current time.

5. In the Employee drop-down list, select Campbell, David, click View Report or Apply (as applicable to your report server), and then note the new date-time stamp.

6. In the Employee drop-down list, select Pak, Jae, click View Report or Apply as applicable, and then note the date-time stamp for the first cached instance of the report.

Creating Report Snapshots

When users don’t need up-to-the minute data from the data source, and also need fast report performance, you can use report snapshots. A report snapshot executes the query and produces the intermediate format in advance of the user’s request to view the report. You can configure a report to generate a snapshot on demand, or you can set up a recurring schedule to replace a snapshot periodically with a more current version. The intermediate format of the report has no expiration time like a cached instance, and is stored in the ReportServer database as part of permanent storage. If you have limited disk space, you should monitor the growth in your ReportServer database, as described in Chapter 12.

If you decide to create a report snapshot on a recurring schedule, you can choose between a report-specific schedule and a shared schedule. The benefit of using a shared schedule is the ability to apply the schedule to several different activities, such as creating snapshot, expiring caches, or delivering subscriptions.

In this procedure, you configure the Sales and Average Sales report to execute as a report snapshot on a weekly schedule.

Configure a report as a scheduled snapshot

1. Click Start, select All Programs, Microsoft SQL Server 2008, Configuration Tools, and click SQL Server Configuration Manager. Then select SQL Server Services in the tree view on the left, right-click SQL Server Agent in the detail view on the right, and, if the state of the SQL Server Agent is stopped, select Start. Close SQL Server Configuration Manager.

   Important

   

   The SQL Server Agent must be running to create and execute schedules. If you later stop or disable the SQL Server Agent, any schedules that were missed do not execute.

2. Switch to Internet Explorer, and open the execution properties page of the Sales and Average Sales report.

   • Native mode: In Report Manager, click the Home link at the top of the window, click the For Review link, click the Sales And Average Sales link, click the Properties tab, and then click the Execution link to view the execution properties.

   • Integrated mode: If the report is open, click the For Review link. Otherwise, type http://<servername>/sites/ssrs/ReportsLibrary/For Review (replacing <servername> with the name of your server), point to Employee Sales Moving Average, click the Edit button (located to the right of the report title), and then select Manage Processing Options.

3. Set the execution type to a report execution snapshot using a report-specific schedule.

   • Native mode: Select Render This Report From A Report Execution Snapshot, select the Use The Following Schedule To Create Report Execution Snapshots check box, and then click Configure.

   NOTE

   

   If a shared schedule exists, you can select the Shared Schedule option, and select the shared schedule in the corresponding drop-down list. You can configure server-wide schedules by clicking the Site Settings link at the top of the window, clicking the Schedules link, and then clicking New Schedule. You then specify the schedule details on the Scheduling page, which looks very similar to the page that you configure in the next step.

   • Integrated mode: Select Use Snapshot Data, select the Schedule Data Processing check box, and then click Configure.

   NOTE

   

   If a shared schedule exists, you can select the On A Shared Schedule option, and select the shared schedule in the corresponding drop-down list. You can configure server-wide schedules by clicking the Site Actions button at the top of the window. Click Site Settings, click Modify All Site Settings, click Manage Shared Schedules in the Reporting Services section, and click Add Schedule. You then specify the schedule details on the Scheduling page, which looks very similar to the page that you configure in the next step.

4. Configure a weekly schedule to create the snapshot each Monday at 6:00 AM, ending in two weeks.

   • Native mode: Select Week, and then, in the first Start Time text box, replace the current value with 06. Select the Stop This Schedule On check box, click the Calendar icon, and select a date two weeks into the future, as shown here. Click OK.

   

   • Integrated mode: Select Week, and then, in the Start Time box, replace the current value with 6:00. Select Stop Running This Schedule On, click the Calendar icon, select a date two weeks into the future, and click OK.

   NOTE

   

   In this procedure, a specific end date for the schedule is specified to limit the number of times the report executes on a schedule. In a production system, you are more likely to omit specifying an end date for the schedule to keep creating the report snapshot on an ongoing basis.

5. Apply the new execution properties.

   • Native mode: On the execution properties page, shown here, click Apply.

     

   • Integrated mode: On the Manage Processing Options: Employee Sales Moving Average page, click OK.

   Notice that the Create A Report Snapshot When You Click The Apply Button On This Page check box is selected by default on this page on a native-mode report server. On an integrated-mode server, the equivalent option is the Create Or Update The Snapshot When This Page Is Saved check box. You can create a snapshot on demand without waiting for the scheduled snapshot by opening this page, selecting this check box, and saving the page by clicking Apply on a native-mode report server or by clicking OK on an integrated-mode report server.

6. View the report snapshot label in the For Review folder.

   • Native mode: Above the execution properties, click the For Review link to view the snapshot label, shown here.

     

   • Integrated mode: After you click OK in the previous step, the contents of the For Review folder displays. However, there is no indicator that the report is now a snapshot report. The Report Modified date-time stamp displays the execution time of the snapshot after you refresh the browser.

Each time you click the report link to view the report snapshot, the execution date-time reflects the execution time of the snapshot. Note that the user cannot change any report parameter that passes a value to a query parameter. The report parameter is visible in the toolbar, but it is disabled.

When a new snapshot is created according to the defined schedule, the former snapshot is removed. Only the most current snapshot is available for viewing unless you configure report history, which you learn how to do in the next procedure.

Saving Report Snapshots in Report History

By default, a new report snapshot replaces the previous report snapshot. You can choose to accumulate report snapshots in report history to preserve a record of the report contents at specific points in time. To add a report snapshot to report history, you can click a button to add it manually, or you can add to report history each time a new report snapshot is created according to the defined schedule.

Because snapshots can require a lot of storage space, you might want to impose a serverwide limit to the number of snapshots that can accumulate for a report, or you can set a limit specific to each report.

In this procedure, you configure report history and manually add a report snapshot to report history.

Configure report history

1. Open the History page of the Sales and Average Sales report.

   • Native mode: In Report Manager, click the Sales And Average Sales link, click the Properties tab, and then click the History link to view the history properties, as shown here.

   

   • Integrated mode: In the For Review folder of the Reports Library, point to the Sales And Average Sales report, click the Edit button (which appears to the right of the report title), and select Manage Data Sources. Click the AdventureWorksDW2008 link, and then click the ellipsis button to the right of the Data Source Link box. In the Select An Item dialog box, click Up twice, click the Data Connections link, select AdventureWorksDW2008, click OK twice, and click Close. Now, to view the history properties, select the Sales And Average Sales report, click Edit, and then click Manage Processing Options.

   Select the Allow Report History To Be Created Manually check box to enable the user to create a report history snapshot on demand. The report history snapshot is different from the snapshot you created in the previous procedure because it is accessible only on the History tab of the report on a native-mode report server, and on the View History page on an integrated-mode server, where you can accumulate multiple snapshots in report history. The New Snapshot button appears on the History tab when this option is enabled.

   As an alternative to creating report history manually, you can select the Use The Following Schedule To Add Snapshots To Report History check box on a native-mode report server. On an integrated-mode report server, select the Create Report History Snapshots On A Schedule check box.

2. Configure the option to store each report execution snapshot in report history.

   • Native mode: Select the Store All Report Execution Snapshots In History check box, and click Apply.

   • Integrated mode: Select the Store All Report Data Snapshots In Report History check box, and click OK.

   NOTE

   

   Only report execution snapshots created after you enabled this option are stored in report history.

   Because report history consumes storage space in the ReportServer database, you should carefully manage the number of snapshots that accumulate in report history. You learn more about how to change the server-wide defaults in Chapter 12. The options for applying a snapshot limit are described in the following table.

   To Set This Type of Snapshot Limit	On a Native-Mode Report Server, Use This Option	On an Integrated-Mode Report Server, Use This Option
   Default	Default Setting	Use Site Default Setting
   specific number	Limit The Copies Of Report History	Limit Number Of Snapshots To
   No limit	Keep An Unlimited Number Of Snapshots In Report History	Do Not Limit the Number Of Snapshots

3. Create a report snapshot manually.

   • Native mode: Click the History tab, and then click the New Snapshot button. The report snapshot appears in a list on the History tab.

   • Integrated mode: Select the Sales And Average Sales report, click Edit, click View Report History, and click New Snapshot.

   The list of report snapshots in history includes the date and time the report snapshot was created and the total size of the report. You can delete a report by selecting its check box, and clicking the Delete button on the toolbar.