Best Practices

Before finishing up, we will 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, there are always exceptions. 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 allow you to start your report layout with these redundant items already present.

In addition, the report templates allow you to 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, there is no more wondering 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 SourceSafe provides versioning of your report source code. If you decide you really 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 really help cut down on management headaches. They centralize the storage of database credentials. If a database is moved to a new server, there are fewer places to change the connection information. If the database logon credentials are changed, there are fewer locations to 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 will not overwrite the shared data source in the production environment. Instead, the report will seamlessly go 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.

Report Deployment Practices

The practices listed here will 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.

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 non-developers creating their own reports. You need to make sure that efficient queries were used to extract the data so that there is not an undue burden placed on the database server. You also need to have some level of assurance the information the report claims to present is actually the information that is 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.

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 to 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 that is more likely to be followed.

Assign Only the Rights Needed

Only give each user the rights they need to complete their required tasks. It is easier to assign broad rights rather than narrow, 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 really need. Then use these custom roles as you are granting access to domain groups. The additional time taken during setup will be more than made up for in the time saved not having to clean up after users who were doing things that they shouldn’t have been able to do in the first place.

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!