MicrosoftВ® SQL Server(TM) 2005 Reporting Services Step by Step (Step by Step (Microsoft))

Before finishing, let’s consider a few items that can make Reporting Services more efficient and easier to manage. These best practices are general rules of thumb that help things run smoother in most Reporting Services installations. As with all rules of thumb, exceptions always exist. However, as you create your Reporting Services installation and the business practices to go with it, consider these practices and the benefits that go with them.

Report-Authoring Practices

The following practices can make your report-authoring process more efficient and more consistent. A standard look and feel is usually desirable as users move from one report to the next. The ability to be responsive to your users and create reports in a timely manner is always a plus.

Use Report Templates

A number of tasks in report authoring can be repetitive, such as placing the company name and logo at the top of the page and placing the page number and date of execution at the bottom. Rather than wasting time creating these items afresh on each report, use one or more report templates. The report templates enable you to start your report layout with these redundant items already present.

In addition, the report templates let you provide a common look and feel to your reports. Templates can help ensure that certain style elements, such as a logo image or a page number in a certain location, are always present. The templates can help to enforce this common look and feel across a number of report authors.

Use Visual SourceSafe

Because the report-authoring environment for Reporting Services is also a development environment, seamless support for Visual SourceSafe is built right in. Use it! It takes very little additional time and effort to store your reports in Visual SourceSafe.

Visual SourceSafe has two advantages. First, no one has to wonder who has the latest source code for a report. This is especially important when modifying and then deploying reports to the Report Server. You do not want to have a report author deploy an old version of a report on top of a newer version. Second, Visual Source-Safe provides versioning of your report source code. If you decide you don’t like the latest changes to a report, you can roll back to an older version. If an older version of an RDL file is pulled off of the Report Server on top of your newer version, Visual SourceSafe can save the day.

Use Shared Data Sources

Shared data sources can help cut down on management headaches. They centralize the storage of database credentials. If a database is moved to a new server, fewer places exist to change the connection information. If the database logon credentials are changed, fewer locations must be modified.

Shared data sources also facilitate the use of production and development database servers. Report development can be done using a shared data source pointing to the development database server. A shared data source with the same name can exist on the production Report Server pointing to the production database server. With the Overwrite Data Sources option turned off, the shared data source from the development environment does not overwrite the shared data source in the production environment. Instead, the report goes seamlessly from querying development data in the development environment to querying production data in the production environment. Isn’t that the way it’s supposed to work?

Use Views and Stored Procedures

Give your report authors rights to query views and execute stored procedures. Avoid giving them rights to the underlying tables. Having them operate with views and stored procedures makes it easier to enforce security and maintain privacy. It also prevents accidental data modifications and deletions.

Use Navigation Features

Take advantage of the document map, bookmark, drilldown, and drillthrough capabilities to make your reports more usable. These navigation features make it easier for your users to find the information they are looking for. Drilldown and drillthrough make it possible to hide complex detail until your user specifically requests it. Finally, drillthrough allows several reports to be linked together into a working unit.

Remember, the goal of reporting is to convey information to the end user. This is done best when a user can quickly navigate to desired information and follow the data intuitively from one level of detail to another or from one report to another. The Reporting Services navigation features make this possible.

Report Deployment Practices

The practices listed here can help you move reports from the development environment to the production Report Server. You need to make sure there is some level of control over which reports can access your production data. You also need to control who can do what on your production Report Server.

Create a Backup of the Encryption Key

This tip is not a report deployment practice, but it does help protect all the reports and shared data sources you have deployed to the Report Server. Occasionally, the key used to encrypt all the sensitive information stored on the Report Server becomes corrupt. When this happens, all that sensitive information is no longer accessible. The report credentials stored with each shared data source can no longer be decrypted and used. Worse yet, the credentials stored in the RSReportServer.config file cannot be decrypted, so the Report Server Windows service can no longer connect to the Report Catalog. In short, everything comes to a screeching halt.

If you do not have a backup copy of the encryption key, the only way to recover from this situation is to create a new encryption key and then reenter all the credential information. That is why the encryption key backup can be so important. With an encryption key backup, recovery from a corrupt key is trivial!

Review Reports Before Deploying

It is generally a good idea to have reports reviewed before they are put into production. This is especially true if you have nondevelopers creating their own reports. You need to make sure efficient queries are being used to extract the data, so an undue burden is not placed on the database server. You also need some level of assurance the information the report claims to present is the information being pulled from the database.

Use Linked Reports

Rather than deploying duplicate copies of the same report to your Report Server, use linked reports. Each linked report can have its own default parameters and its own security. At the same time, updates to that report are done in one centralized location. This helps prevent the confusion that can arise from having multiple versions of the same report running in the production environment at the same time.

Use Folders and Descriptions to Organize Reports

If your Reporting Services installation is as successful as we all hope, soon tens or even hundreds of reports will reside on your Report Server. With this number of reports, organizing the reports properly to aid both the end users and the administrators is important. Otherwise, both the users and the administrators can become frustrated.

Organize your reports into logical groupings in folders. Use the tree structure of the folders to create a multiple-level structure. You should create enough folders so no folder contains too many reports, but not so many folders that the structure becomes cumbersome.

Use meaningful report names and add informational descriptions to each report. Remember, both the report name and the description are searchable in Report Manager. Then make sure your users know how to use this search function.

Assign Security at the Folder Level

Make your security role assignments at the folder level. Let the reports inherit their security from the folders they reside in. Assigning individual security roles to individual reports is cumbersome and easily leads to errors. Your security practices should be relatively easy to implement; otherwise, they will not be followed.

Assign Security to Domain Groups

By the same token, it makes more sense to assign roles to domain groups than try to assign roles to each individual user. Just as with assigning security at the report level, making assignments at the user level causes things to become very complex very rapidly. The simpler security policy is usually better, because it is the one more likely to be followed.

Assign Only the Rights Needed

Only give each user the rights they need to complete their required tasks. Assigning broad rights rather than narrow is easier, but this can lead to security breaches and problems managing the Report Server. Take the time to create custom security roles that provide users with only those rights they need. Then use these custom roles as you are granting access to domain groups. The additional time taken during setup is more than made up for in the time saved not having to clean up after users who were doing things they shouldn’t have been able to do in the first place.

Hide Items

Keep the folders looking as clean and uncluttered as possible. Use “Hide in list view” to hide items the user does not need to interact with. This might include shared data sources or subreports. If the user should not click on an item, then the user has no reason to see it in the folder.

Remember, however, this is not a security measure. The user can easily click on Show Details to reveal any of these hidden items. Security rights provide security; “Hide in list view” is a means of keeping things neat.

Deploy Supporting Items to the Report Server

The Report Server has the capability to store and serve supporting information. Documentation for your reports should be created as HTML pages, Word documents, PDF documents, Excel spreadsheets, and even PowerPoint presentations. These items can then be deployed to the Report Server right in the folders with the reports. This makes it easier for your users to understand the content and appropriate use of each report.

Use Caching and Snapshots

Use caching and snapshots to reduce the load on your Report Server and increase performance. Set up scheduled snapshots to execute long-running reports during off-hours. Believe me, users will not care if their data is eight hours old when they can get their reports back in seconds!