The previous sections showed you how to create different types of reports. This section describes how you can move reports to a managed environment, where they can be used by different groups of users.
NOTE Managing reports is an advanced task, performed by an administrator. For this reason, I will give you just an overview of the SSRS management tasks. For a detailed description of how reports can be managed, please see Microsoft Docs.
All created reports are available through the report server. The web application of SSRS allows you to create folders in the Report Catalog, which contains reports and all their supporting files. Each folder has a name and a description. Both the folder name and its description can be used by a user to find a report.
NOTE Report Catalog folders are different from usual file system folders because they are screen representations of records in the Report Catalog database, and therefore cannot be accessed in the usual way.
SSRS manages reports using a web application called Reporting Services web portal, which is described next.
1. Reporting Services Web Portal
Reporting Services web portal is a web application that allows you to create reports in the Report Catalog. During the installation process of SSRS, the home folder is created. The name of the default URL for accessing Reporting Services web portal is http://Computer_name/ reports.
NOTE Reporting Services web portal replaces the Report Manager. This portal extends the functionality of the Report Manager by introducing several new features, which will be explained next.
The following features, among others, are supported by Reporting Services web portal:
- Publishing reports on mobile devices
- Exporting reports to several formats
- Features for subscriptions and delivery of reports
Besides these three features, which will be covered in the following subsections, SSRS allows you to access reports with different browser types. This feature will be described first.
1.1. Accessing Reports with Different Browser Types
When SSRS was initially added to SQL Server, it was optimized for Internet Explorer. Since then, several browsers that support newer web standards have emerged. The disadvantage of previous versions of SSRS is that they do not render reports consistently in these browsers.
Since SQL Server 2016, SSRS is extended with a new renderer that supports HTML5 and has no dependency on features specific to Internet Explorer.
It is still possible that a report does not render correctly with the new rendering engine. In that case, the rendering process can be reverted to the previous rendering style by clicking the Switch to Compatibility Mode link on the right side of the report viewer toolbar.
1.2. Publishing Reports on Mobile Devices
In the earlier days, reports were delivered in the traditional form of paginated documents. The main property of these documents is that their layout is fixed for different computer types and different screen resolutions. In other words, paginated documents lay out their content on fixed-size pages.
In the meantime, users are doing more and more on their mobile devices and need to view reports on smartphones and tablets. Solutions based upon paginated documents often deliver a suboptimal solution for these devices. For this reason, SQL Server provides a solution that allows you to create mobile reports that are optimized for smartphones and tablets. You can create mobile reports using the SQL Server Mobile Report Publisher app.
NOTE Mobile Report Publisher is used to design and publish mobile reports in the same way as the Report Builder is used to design and publish paginated reports.
1.3. Exporting a Report to PowerPoint
One of the advantages of SSRS is the ability to export a report to a variety of different formats, such as Excel or Word. Since SQL Server 2016, PowerPoint has been added to the existing list of supported formats. To use this format, click the Export button in the report viewer toolbar and select PowerPoint.
When you select this export option, the .pptx file will be downloaded to your computer. If you have PowerPoint installed on your computer, you can open the file. In general, each page of your report becomes a separate slide in PowerPoint, although some report items might span multiple slides.
1.4. Subscriptions and Delivery of Reports
Before I discuss several features in relation to report subscriptions, let’s take a look at how SSRS allows you to subscribe to and deliver reports.
SSRS supports standard and data-driven subscriptions. A standard subscription usually consists of specific parameters for parameterized reports as well as report presentation options and delivery options. You can use different tools to manage standard subscriptions.
An important step in management of standard subscriptions is to configure a schedule. You can specify a schedule for a particular report only or use a shared schedule for several subscriptions. (If your report has parameters, you have to determine which values are assigned to parameters when the subscription is started.)
A data-driven subscription delivers reports to a list of recipients determined at run time. This type of subscription differs from a standard subscription in the way it gets subscription information: some settings from a data source are provided at run time, and other settings are static (that is, they are provided from the subscription definition). Static aspects of a data-driven subscription include the report that is delivered, the delivery extension, connection information to an external data source that contains subscriber data, and a query. Dynamic settings of the subscription are obtained from the row set produced by the query, including a subscriber list and user-specific delivery extension preferences or parameter values.
Subscriptions include a feature for enabling and disabling them. To enable or disable a subscription, browse to the subscription from the My Subscriptions page of an individual subscription. Select a subscription and then click either the Disable button or Enable button on the ribbon. (This task can be performed for several subscriptions if they are selected at once.) SSRS writes a row in the Reporting Services log when a subscription is disabled and another entry when the subscription is enabled again.
Another feature in relation to subscriptions is the option to include a file share delivery extension so that a report can be delivered to a folder. The file share delivery extension requires no additional configuration. In order for file delivery to succeed, you must set write access permissions on the shared folder. The account that requires write permissions is usually a file share account configured for the report server. To distribute a report to a file share, you define either a standard subscription or a data-driven subscription.
NOTE Subscriptions are not available in every edition of SQL Server. See Microsoft Docs for information about which editions support standard and/or data-driven subscriptions.
2. Performance Issues
Each time a report is executed, SQL Server Reporting Services loads the data. That way, the user can always view the current data. This feature is advantageous, but has one drawback: the user has to wait for the data each time a record is executed. SSRS solves this performance problem through report caching, which is discussed next.
2.1. Cached Reports
Caching means that a report is generated only for the first user who opens it, and thereafter is retrieved from the cache for all subsequent users who work with the same report. A report server can cache a copy of a processed report and return that copy when a user opens the report. To a user, the only evidence available to indicate the report is a cached copy is the date and time that the report ran. If the date or time is not current and the report is not a snapshot, the report was retrieved from cache.
As you probably guessed, caching shortens the time to retrieve frequently accessed reports. Also, this technique is recommended for large reports. If the server is rebooted, all cached instances are reinstated when the server is restarted.
The contents of the cache are volatile and can change as new reports are added or existing ones dropped. If you require a more predictable caching strategy, you should create a report snapshot, which is described next.
Report Snapshots The main disadvantage of cached reports is that the first user who wants to use the particular report has to wait until the system creates it. A more user-friendly method would be for the system to create the report automatically, so even the first user doesn’t have to wait. This is supported by report snapshots.
A report snapshot is a way to create cached reports that contain data captured at a specific point in time. The benefit of a report snapshot is that no user has to wait, because the data has already been accessed from the report’s data source(s) and stored in the Report Server temporary database. That way, the report will be rendered very quickly. The disadvantage of report snapshots is that data can become stale if the time difference between the creation of the report snapshot and access of the report is too long.
The main difference between report snapshots and cached reports is that cached reports are created as a result of a user action, while report snapshots are created automatically by the system.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.