SQL Server Reporting Services is a server-based reporting system with an application layer responsible for the authentication, processing, rendering, and delivery of reports to users. Depending on the installation mode, the application layer might also be responsible for the security of reporting-related objects stored on the server such as reports, shared data sources, shared datasets, and shared report parts.
In addition to the items described below, which are fairly standard across most enterprise-level standard reporting systems, Reporting Services also includes a number of other features that provide substantial gains in performance and user experience. These features include items such as subscriptions, caching, and snapshots, as well as a new feature known as data driven alerts.
In the rest of this section, we will explore the primary server-level features and functionality offered by the SQL Server Reporting Services.
While planning a SQL Server Reporting Services installation, you will need to choose between the Native and SharePoint Integrated modes. The report authoring and consumption experience is nearly identical between the two modes—and so the decision typically comes down to whether or not you already have an existing SharePoint farm or are planning to deploy one.
- Native: When this mode selected, a standalone web application is installed and provides a user interface to manage the configuration of the report server, folder and report settings, and security access controls. Users will typically access reports directly through the standalone report server.
- SharePoint Integrated: When this mode selected, the entire configuration, security access controls, and report rendering is managed through SharePoint. Users will typically access reports through SharePoint sites and document libraries.
From a feature perspective, there are only a few differences between Native and SharePoint Integrated mode. For example, Data Alerts are only available in SharePoint Integrated mode, while My Reports and Linked Reports are only available in Native mode. Each of these features will be discussed in more detail later in the chapter.
Both modes provide mechanisms for scaling out to accommodate a growing user load. See the following TechNet articles for details http://technet.microsoft.com/en-us/library/hh479774.aspx and http://technet.microsoft.com/en-us/library/ms159114.aspx.
When it comes to standard reporting in general, the ability to schedule reports has become a ubiquitous feature in nearly all enterprise-level solutions; SQL Server Reporting Services is no exception. In the context of Reporting Services, a schedule is simply a request to automatically run a report at some point in the future. While a schedule is most commonly configured to execute on a regular, recurring basis, it can also be configured to execute a report just once and never again.
Reports can be associated with multiple schedules. For example, a report might be configured to run every weekday at 8 am (schedule 1) as well as every Sunday night at 10 pm (schedule 2).
Scheduling is available in both SharePoint Integrated and Native modes and comes in two forms: custom and shared. A custom schedule is a schedule created for a specific report and cannot be used by other reports on the report server. A shared schedule is a standalone, server-level object defined by a power user or administrator and can be used by multiple reports on the report server.
Note
One major requirement associated with scheduling is that the report must be configured to use either stored credentials or no credentials. Windows integrated security is not an option since the schedule is not associated with a user account, which can cause problems when trying to deal with dynamic data security.
The following screenshot shows the interface for creating a schedule on a report server installed in Native mode. The interface is similar in SharePoint Integrated mode:
There are three main uses for schedules in SSRS, which we will explore in the rest of this section:
One important distinction between these three items is that the first one is a business process enhancement, while the second and third items are performance enhancement techniques.
A subscription defines the execution and delivery of a report on a schedule. For example, a user might define a subscription to have a daily report executed each morning and delivered to their e-mail inbox so that they can review the information when they get into the office. This capability saves the business a lot of time because users are not required to manually navigate to the report server and run the reports every time they need to review the information to see how some aspect of the business is doing.
The following screenshot shows an example of a subscription to a daily sales report that runs every morning at 6 am and creates a PDF copy of the report on a network share; if the report contained parameters, values would need to be supplied at the time the subscription was created:
Report delivery options
There are four main report delivery options, and these define how the server should handle the results after executing the report.
- Email: Upon executing this option, the results are e-mailed to the recipient(s) in the form of a link to the report on the server or a physical file for offline viewing. A single subscription can be configured to e-mail multiple recipients.
- Network File Share: Upon executing this option, the report will be saved to the specified network file share for viewing in one of the available offline file formats.
- SharePoint Document: Upon executing this option, the report will be added to the specified SharePoint document library in one of the available offline file formats. This option is only available for report server installed in the SharePoint Integrated mode.
- Null Delivery Provider: This option is only used to preload the cache, which we'll cover later on in this chapter.
The following screenshot shows a list of the offline file formats available with Reporting Services 2012. For more information about these file formats, please see the following TechNet article at http://technet.microsoft.com/en-us/library/ms154606.aspx.
Subscriptions can be standard or data-driven. For standard subscriptions, all of the input parameters for the report (for example, Sales Territory and Fiscal Period) and the subscription (for example, recipient e-mail addresses and delivery file format) must be known and supplied at the time the subscription is created. With data-driven subscriptions, the values for parameters and delivery information are queried from a relational source when the schedule executes. This makes data-driven subscriptions ideally suited for delivering popular reports to a large list of recipients in a variety of formats with different runtime parameter values—especially if the list of recipients changes frequently.
The purpose of a snapshot is to reduce the load on the source system(s) and to reduce the time a report consumer waits for a long-running report to execute on demand. The basic idea is that a report is executed at some point in time, prior to when it is actually needed by the business. A copy of the execution results is stored as a snapshot on the report server. Then, at some point in the future, when users execute the same report, the request is satisfied from the existing snapshot instead of having to reprocess the entire report, which would involve sending another query to the source system(s) and processing the results again.
There is a tradeoff, however, for this performance gain. The data in a report generated from a snapshot will be stale; if the data in the source system(s) has changed since the snapshot was taken, the reports executed by the users that were satisfied by the snapshot will not contain the most recent changes. This is typically not a problem for summary reports or historical reports generated from a data warehouse. However, it could definitely be a problem if the purpose of the report is to provide up-to-the-minute information on a production line.
Enabling snapshots is done on a report-by-report basis. The first step is to change the Processing Options screen from the default setting of Always run this report with the most recent data to Render this report from a report snapshot. The next step is to actually create a snapshot, which can be done manually or on a schedule, though typically they are created and updated on a recurring schedule. The following screenshot is what the Processing Options screen should look like once both of these steps have been completed:
Report processing options
Before a snapshot can be created for a parameterized report, all parameters must be assigned default values. As long as users run the report using the default parameters, the request will be satisfied from the snapshot. However, if the users change the report parameters from the default values, utilization of the snapshot depends on how the parameter is used in the report. If the parameter is used in the dataset query to filter the results retrieved from the source system, the report will not be generated from the snapshot. However, if the parameter is used outside of the query to filter the dataset after all data has been retrieved from the source system, then the report will be generated from the snapshot. This makes sense because the snapshot is essentially storing the query results to avoid having to go back to the source system for different data, which is where the performance gains come from. Bottom line: as long as the query results don't change as a result of the parameter values, the snapshot will be utilized.
The following screenshot shows a simple table to help understand when a snapshot will be used:
Snapshot usage table
A good use case for snapshots is period-end reports. These types of reports often deal with large amounts of data and can take a considerable amount of time to run. They are also characterized by their large audience of report consumers. Instead of having each user run this report on demand, a snapshot can be scheduled to run after hours as soon as the period ends. Then, when the users come in the next day, all subsequent requests can be satisfied immediately from the snapshot instead of having to wait for the data to be retrieved from the source system.
Snapshots are also a good way to maintain point-in-time views of the business. Building from the previous example of period-end reporting, the scheduled snapshots can be automatically saved in the report history providing a reference for how the business was performing at the end of each period.
Keep in mind that the default retention period for historical snapshots is 10 copies of the report. So if you have a monthly snapshot and need 12 months of historical snapshots, you will need to adjust this setting, which can be done at the server level (if you want it to apply to all reports) or at the individual report level.
The caching feature is very similar to snapshots in that it exists as a mechanism to improve report processing performance by keeping a stored copy of the report data and structure from which subsequent requests can be generated without having to go back to the source system. However, there are a few distinct differences you would do well to be aware of.
The main difference is that a cached copy of a report has a configurable lifespan. Once the cached copy of a report expires, the next execution of the report will run in its entirety—sending a query to the source system(s) to return up-to-date information to the user. This ability to control the lifespan of the cached copy of a report allows for a ceiling to be set for the maximum latency of the information contained in the report.
For example, the settings of a report can be configured such that the cached copy expires after 1 hour. During that 1-hour period after the cached copy is created, all executions of that report will be satisfied by the cached copy, and no query will be sent to the data source. After the 1-hour period has elapsed, the next user to execute the report will have to wait while the report is fully executed against the source system(s). That execution becomes the new cached copy from which all subsequent report requests are satisfied for the next hour.
The example scenario from the previous paragraph is not entirely accurate. It does not take into account parameterized reports and runtime parameter values, which is another difference between caching and snapshots. If you recall from the previous section, snapshots can only be created using the default parameter values. On the other hand, when caching is enabled, a separate cached copy of the report will be created for every combination of parameter values with which the report is executed. This may or may not be a desirable behavior. For example, if a report is structured such that there are many combinations of parameter values and each combination is typically only executed a few times, then the cached copies will rarely be used to satisfy a report execution request, which means most report executions will issue a query against the source system and users will be forced to wait for a full execution of the report. Furthermore, there is the additional overhead of storing the cached copies of each version of the report with very little benefit.
The two most common methods to expire cached copies of a report are through a recurring schedule or a configurable duration. The third option is to configure a cache refresh plan (shown in the following screenshot), which runs on a recurring schedule and updates the cached copy. The drawback with using cache refresh plans to keep the cached copies updated is that you must create one for every combination of parameters which could add considerable setup and maintenance overhead.
Example of cache refresh plan
You can learn more about caching reports from the TechNet article at http://technet.microsoft.com/en-us/library/ms155927.aspx.
Data alerts are a brand new feature in SQL Server Reporting Services 2012 that allows business users to define custom alerts when information contained in a report changes or crosses a configured threshold. As mentioned in the section Choosing an installation mode – SharePoint Integrated versus Native earlier, this feature is only available for installations in the SharePoint Integrated mode.
Data alerts remove the need for business users to review a stack of standard reports each day, searching for specific events or conditions that require action or intervention on their part. Instead, business users can simply set up a few data-driven alerts based on customizable rules that match the events or conditions that they are responsible for. When the alert is triggered, an e-mail will be sent to the user with a description of the issue and a link to the report that the alert was generated from. The user can then review the data and take the necessary actions.
The following screenshot shows an example of a data alert being defined for the Sales Amount By Sales Territory report. The alert is configured to check every day to see if there are any Sales Regions in Europe with a sales amount under $1,000,000.
Example of a data alert
The primary components of a data alert are as follows:
- Report data name: This is the report item (table, chart, and so on) on which the alert will be based. Users select the report item from a pre-populated drop-down list.
- Alert name: This is the name of the alert and should describe the event being monitored.
- Rule: This defines the criteria that an alert will be triggered for. The UI provides the ability to define simple or complex rules based on any of the fields available in the underlying dataset for the selected report item. It also takes into account the data types of the fields contained in the dataset in order to provide intuitive options to define the rules. For example, when defining a rule involving a numeric field, the user will see a different set of comparison operators (for example, greater than, less than) than when the rule involves a text/string field.
- Schedule settings: This section is where the user configures the frequency at which to evaluate the rule criteria. Start and end dates can also be defined to constrain alert to run only during a relevant time period.
- Email settings: This section is where users define the recipient list and details to include in the notification e-mail. Along with the subject and description of the alert, a link to the report that the alert was generated from will all be included in the e-mail message.
The Data Alert Manager page shown in the following screenshot can be used to manage existing data alerts. Regular users will only have access to the alerts they create, while site administrators will have access to all alerts created by all users.
Data Alert Manager
Data alerts work by running stripped-down copies of the reports on the recurring interval defined in the alert schedule and evaluating the data returned against the rule criteria. Across an entire user base, the additional load of alerts can be considerable. Therefore, business users should be trained to set appropriate schedules for the data-driven alerts they create. For example, an alert for a weekly sales report does not need to be set to run every minute. In addition to training users on appropriate alert usage, administrators should be reviewing data alerts on a regular basis to ensure the frequencies are appropriately configured.
My Reports is a feature that provides business users with a place on the report server where they can store and manage personalized reports. It is a server-wide setting that is disabled by default. The feature can be enabled by logging in to the Reporting Services instance through SQL Server Management Studio (SSMS ) and using the server properties shown in the following screenshot:
SSRS Server properties via SQL Server Management Studio (SSMS)
Once enabled, each user accessing the report server will have a folder named My Reports on the main page of the report server as shown in the preceding screenshot, in which they can create and store reports.
My Reports folder after enabling feature at server level
Regular users only have access to their own My Reports folder, while report server administrators will be able to view all folders.
A linked report is basically just a shortcut to another report on the report server. Users with appropriate permissions can create linked reports and save them to another folder, such as My Reports, on the report server.
One very useful characteristic of linked reports is that they can have their own configurations—completely separate from the original copy. This means that linked reports can have different default parameters, run on different schedules, have different snapshot or cache configurations, and so on.