Monday, September 27, 2010

Working with Report Snapshots in SQL Server Reporting Services (SSRS)

Problem


We have many SQL Server Reporting Services (SSRS) reports that use our OLTP systems as data sources. We are always adding new reports and the number of users running these reports is also increasing rapidly. We would like to look at leveraging report snapshots as a way to reduce some of the load on our database servers. Can you provide us with an example of how to do this?

Solution

A report snapshot provides the ability to run a report at a scheduled time or on demand and save the report in the ReportServer database. When a user requests the report, they automatically get the snapshot version stored in the ReportServer database rather than executing the SQL queries to retrieve the data from the underlying data source. You can schedule report snapshots to be run during off-peak hours; this can reduce the load on your database servers. You can also create snapshots manually and select which snapshot version of a report that you would like to run. You can save as many snapshots as you like or you can have Reporting Services limit the number of snapshots that are saved. Besides reducing the load on your database servers, you can use report snapshots to archive reports at a particular point in time for later viewing; e.g. month-end reports, year-end reports, etc. Depending on how many report snapshots you keep, you could use a significant amount of space in the ReportServer database. Read more..

How to: Create and Manage Subscriptions

You can create subscriptions for reports that you access from a SharePoint Web application that is integrated with a report server that runs in SharePoint integrated mode.


To create a subscription, the report must use stored credentials and you must have permission to view the report and create alerts. When you create a subscription, there are three ways to specify its delivery:

You can create a subscription that delivers a document based on the original report to a library within the same SharePoint site as the original report. You cannot deliver the document to a library on another server or another site within the same site collection. To deliver the document, you must have Add Items permission on the library to which the report is delivered.

You can deliver a document based on the original report to a shared folder on the file system. You must select an existing folder that is accessible over a network connection.

If the report server is configured to use the Report Server E-mail delivery extension, you can create a subscription that sends a report or an exported report file (saved in an output format) to your in-box. To receive just the notification without the report or report URL, clear the Include a link to this report and the Show report inside message checkboxes.

When you create a subscription, you can select a file format that creates the report as a certain document type. Not every report works well in every format. Before you select a format in a subscription, open the report and export it to different formats to verify that it appears as expected.


Important:

A subscription that delivers a report to a library or to a shared folder creates a new, static file that is based on the original report, but it is not a true report definition that runs in the Report Viewer Web Part. If the original report has interactive features (such as drillthrough links) or dynamic content, those features will not be available in the static file that is delivered to the target location. If you select a "Web Page" you can preserve some interactivity, but because the document is not an .rdl file that runs in the Report Viewer, clicking through a report creates new pages in the browser session that you must scroll through to return to the site.

You cannot rename the file name extension of an exported report to .rdl and have it run in the Report Viewer Web Part. If you want to create a subscription that provides an actual report, use the Report Server E-mail delivery extension and set options to include a link to the report.

Version settings on the library that contains the delivered document determine whether a new version of the document is created with each delivery. By default, version settings are enabled for each library. Unless you specifically choose No versioning, a new major version of the document will be created upon delivery. Only major versions of the document are created; minor versions are never created as a result of subscription delivery, even if you select a versioning option that allows minor versions. If you limit the number of major versions that are retained, older deliveries will be replaced by newer ones when the maximum limit is reached.

Output formats that you select for a subscription are based on rendering extensions that are installed on the report server. You can only select output formats that are supported by the rendering extensions on the report server. For descriptions of the output formats, see Exporting Reports.

To deliver a report to a SharePoint library

Point to the report in a library.

Click the down arrow next to the report, and select Manage Subscriptions.

Click Add Subscription.

In Delivery type, select SharePoint Document Library.

In Document Library, select a library within the same site.

In File Options, specify the file name and title for the document that will be created by the subscription.

In Output Format, select the application format.

Web archive (MHTML) is the default because it produces a self-contained HTML file, but it will not preserve interactive report features that might be in the original report.

In Overwrite Options, specify an option that determines whether subsequent deliveries overwrite a file. If you want to preserve previous deliveries, you can select Create a file with a unique name. A number will be appended to new files to create a unique file name.

In Delivery Event, specify a schedule or event that causes the subscription to run. You can create a custom schedule, select a shared schedule if one is available, or run the subscription whenever the data is refreshed for a report that runs with snapshot data. For more information about schedules and data processing, see How to: Set Processing Options (Reporting Services in SharePoint Integrated Mode) and How to: Schedule Report and Subscription Processing (Reporting Services in SharePoint Integrated Mode).

In Parameters, if you are creating a subscription to a parameterized report, specify the values that you want to use with the report when the subscription is processed. For more information about parameters, see How to: Set Parameters on a Published Report (Reporting Services in SharePoint Integrated Mode).

To create a subscription for shared folder delivery

Point to the report in a library.

Click the down arrow next to the report, and select Manage Subscriptions.

Click Add Subscription.

In Delivery type, select Windows File Share.

In File Name, enter the name of the file that will be created in the shared folder.

In Path, enter a folder path in Uniform Naming Convention (UNC) format that includes the computer's network name. Do not include trailing backslashes in the folder path. An example path might be \\ComputerName01\Public\MyReports, where Public and MyReports are shared folders.

In Render Format, select the application format for the report.

In Write Mode, choose between None, Autoincrement, or Overwrite. These options determine whether subsequent deliveries overwrite a file. If you want to preserve previous deliveries, you can choose Autoincrement. A number will be appended to new files to create a unique file name. If you choose None, no delivery will occur if a file of the same name already exists in the target location.

In File Extension, choose True to add a file name extension that corresponds to the application file format, or False to create the file without an extension.

In User Name and Password, enter credentials that have write permissions on the shared folder.

In Delivery Event, specify a schedule or event that causes the subscription to run. You can create a custom schedule, select a shared schedule if one is available, or run the subscription whenever the data is refreshed for a report that runs with snapshot data. For more information about schedules and data processing, see How to: Set Processing Options (Reporting Services in SharePoint Integrated Mode) and How to: Schedule Report and Subscription Processing (Reporting Services in SharePoint Integrated Mode).

In Parameters, if you are creating a subscription to a parameterized report, specify the values that you want to use with the report when the subscription is processed. For more information about parameters, see How to: Set Parameters on a Published Report (Reporting Services in SharePoint Integrated Mode).

To create a subscription for report server e-mail delivery

Point to the report in a library.

Click the down arrow next to the report, and select Manage Subscriptions.

Click Add Subscription.

In Delivery type, select E-mail.

In Delivery options, specify an e-mail address to send the report to.

Optionally, you can modify the Subject line. The Subject line uses built-in parameters that capture the report name and time when it was processed. These are the only built-in parameters that can be used. The parameters are placeholders that customize the text that appears in the Subject line, but you can replace it with static text.

Choose Include a link to this report if you want to embed a report URL in the body of the message.

In Report Contents, specify whether you want to embed the actual report in the body of the message.

The rendering format and browser determine whether the report is embedded or attached. If your browser supports HTML 4.0 and MHTML, and you select the Web archive rendering format, the report is embedded as part of the message. All other rendering formats (CSV, PDF, and so on) deliver reports as attachments. Reporting Services does not check the size of the attachment or message before sending the report. If the attachment or message exceeds the maximum limit allowed by your mail server, the report will not be delivered. Choose one of the other delivery options (such as URL or notification) for large reports.

In Delivery Event, specify a schedule or event that causes the subscription to run. You can create a custom schedule, select a shared schedule if one is available, or run the subscription whenever the data is refreshed for a report that runs with snapshot data. For more information about schedules and data processing, see How to: Set Processing Options (Reporting Services in SharePoint Integrated Mode) and How to: Schedule Report and Subscription Processing (Reporting Services in SharePoint Integrated Mode).

In Parameters, if you are creating a subscription to a parameterized report, specify the values that you want to use with the report when the subscription is processed. For more information about parameters, see How to: Set Parameters on a Published Report (Reporting Services in SharePoint Integrated Mode).

To view or modify a subscription

Point to the report.

Click the down arrow, and then click Manage Subscriptions.

Each subscription is identified by the type of delivery. Click the subscription type to view and change the existing properties.

To delete a subscription

Point to the report.

Click the down arrow, and then click Manage Subscriptions.

Click the checkbox next to the subscription, and click Delete.

Thursday, September 9, 2010

Creating a Drill Down Report with SQL Server 2008

In SSRS we can generate drill down reports in very easy way. To create Drill down reports, please follow the following steps.
Step1: Create New Report Project with one Shared DataSource with data query for generating report.
Step1: Create report by taking Table type report as shown bellow
Step3: Now Add Group for product by right clicking on Product Column as shown bellow
Select Add Parent group

Selet Product from Group By DropDown and check Add Group Header, then click OK

The report table looks like this
Step 4: From Row groups > select Product group details (group Details and not group header) > go to Group Properties > select Visibility tab > select Hide > click on Display can be toggled by this group item and select the name of the group then click on OK
Then the  preview the report
Step 5:We can also change the drill down and make it reverse by setting ‘InitialToggleState’ to True and by selecting the group properties (header), visibility tab and click Show