Managing Reports on the Report Server

Now that we have moved some of our reports to the Report Server, you may be thinking our job is about done, but it is just beginning. Now we need to manage the reports and supporting materials to ensure the reports can be utilized properly by our users.

Two of the biggest concerns when it comes to managing reports are security and performance. Reports containing sensitive data must be secured, so they are only accessed by the appropriate people. Reports must return information to users in a reasonable amount of time, without putting undo stress on database resources. Fortunately, Reporting Services provides tools for managing both of these concerns. Security roles and item-level security give you extremely fine control over who has access to each report and resource. Caching, snapshots, and history let us control how and when reports are executed.

Security

In Reporting Services, security was designed with both flexibility and ease of management in mind. Flexibility is provided by the fact that individual access rights can be assigned to each folder and to each item within a folder. An item is either a report or a resource. We can specify exactly who has rights to each item and exactly what those rights are. Ease of management is provided by security inheritance, security roles, and integration with Windows security. We begin our discussion with the last entry in this list.

Note 

Remember, although we are creating and maintaining these role assignments using the Report Manager, the security rights apply to Reporting Services as a whole. No matter how you access folders and items—through the Report Manager or through the web service—these security rights are enforced.

Integration with Windows Security

Reporting Services does not maintain its own list of users and passwords. Instead, it depends entirely on integration with Windows security. When a user accesses either the Report Manager web application or the web service, that user must authenticate with the Report Server. In other words, the user must have a valid domain user name and password, or a local user name and password, to log on to the Report Server. Both the Report Manager web application and the web service are set up requiring integrated Windows authentication to ensure this logon takes place.

Note 

If it is impossible for each report user to have their own credentials on the Report Server, it is possible to create your own custom security. You can create a security scheme, such as forms-based security, to enable the users to authenticate and access reports. However, this is not a trivial process.

Once this logon occurs, Reporting Services utilizes the user name and the user's group memberships to determine what rights the user possesses. The user can access only those folders and items they have rights to. In Report Manager, users do not even see the folders they cannot browse and the reports they cannot run. There is no temptation for the user to try and figure out how to get into places they are not supposed to go, because they do not even know these places exist.

Local Administrator Privileges

In most cases, rights must be explicitly assigned to folders and items. One exception to this rule, however, is local administrator privileges. Any user who is a member of the local administrators group on the computer hosting the Report Server has content manager rights to all folders and all items. These automatic rights cannot be modified or removed.

Let's look at the Security page:

1. Open the Report Manager in your browser and navigate to the Home folder.

2. Select the Properties tab. You see the Security page for the Home folder, as shown in Figure 15-69.

Figure 15-69: The Security page for the Home folder

The Report Server maintains a Security page for each item in the Report Catalog—every folder, every report, and every supporting item. The Security page lists all the role assignments for an item. Each role assignment is made up of two things: a Windows user or group and a security role. The rights associated with the security role are assigned to the Windows user or group.

Initially, one role assignment is on the Security page for each item. This entry assigns the Content Manager security role to the BUILTIN\Administrators group. This entry is a reminder that any user who is a member of the local administrators group has rights to manage the contents of this folder.

Note 

You could delete the role assignment for BUILTIN\Administrators, and the members of the local administrators group would still have rights to manage the contents of this folder. These rights are hardwired into Reporting Services. The BUILTIN\Administrators assignment on the Security page is, in most cases, just a reminder of the rights held by anyone in the local administrators group.

Tasks and Rights

We can perform a number of tasks in Reporting Services. Each task has a corresponding right to perform that task. For example, we can view reports. Therefore, a corresponding right exists to view reports. The tasks within Reporting Services are shown in Table 15-1.

Table 15-1: Security Tasks Within Reporting Services

Task	Description
Consume reports	Read report definitions.
Create linked reports	Create linked reports and publish them to a folder.
Manage all subscriptions	View, modify, and delete any subscription, regardless of who owns the subscription.
Manage data sources	Create, modify, and delete shared data sources.
Manage folders	Create, view, and delete folders. View and modify folder properties.
Manage individual subscriptions	Create, view, modify, and delete your own subscriptions.
Manage models	Create, view, and delete models. Modify model properties.
Manage report history	Create view, and delete report history snapshots. Modify report history properties.
Manage reports	Create, view, and delete reports. Modify report properties.
Manage resources	Create, modify, and delete resources. View and modify resource properties.
Set security for individual items	View and modify security settings for reports, folders, resources, and shared data sources.
View data sources	View shared data sources and their properties.
View folders	View folders and their properties.
View models	View models. Use models as report data sources. Query models for data.
View reports	View reports and linked reports, along with their report history snapshots and properties.
View resources	View resources and their properties.

In addition to the tasks listed in Table 15-1 are system-wide tasks with associated rights. These system-wide tasks deal with the management and operation of Reporting Services as a whole. The system-wide tasks within Reporting Services are shown in Table 15-2.

Table 15-2: System-wide Security Tasks Within Reporting Services

Task	Description
Execute Report Definitions	Start execution of a report from a report definition without deploying it to the Report Server.
Generate events	Provide an application with the capability to generate events within the Report Server.
Manage jobs	View and cancel running Report Server jobs.
Manage Report Server properties	View and modify configuration properties for the Report Server.
Manage Report Server security	View and modify system-wide role assignments.
Manage roles	Create, view, modify, and delete role definitions.
Manage shared schedules	Create, view, modify, and delete shared schedules used for snapshots and subscriptions.
View Report Server properties	View properties that apply to the Report Server.
View shared schedules	View a shared schedule.

Roles

The rights to perform tasks are grouped together to create roles. Reporting Services includes several predefined roles to help us with security management. In addition, we can create our own custom roles, grouping together any combination of rights we like. The predefined roles and their corresponding rights are listed here.

The Browser Role The Browser role is the basic role assigned to users who are going to view reports, but who are not going to create folders or upload new reports. The Browser role has rights to perform the following tasks:

• Manage individual subscriptions

• View folders

• View models

• View reports

• View resources

The Publisher Role The Publisher role is assigned to users who are going to create folders and upload reports. The Publisher role does not have rights to change security settings or manage subscriptions and report history. The Publisher role has rights to perform the following tasks:

• Create linked reports

• Manage data sources

• Manage folders

• Manage models

• Manage reports

• Manage resources

The My Reports Role The My Reports role is designed to be used only with a special folder called the My Reports folder. Within this folder, the My Reports role gives the user rights to do everything except change security settings. The My Reports role has rights to perform the following tasks:

• Create linked reports

• Manage data sources

• Manage folders

• Manage individual subscriptions

• Manage report history

• Manage reports

• Manage resources

• View data source

• View folders

• View reports

• View resources

The Content Manager Role The Content Manager role is assigned to users who are managing the folders, reports, and resources. All members of the Windows local administrators group on the computer hosting the Report Server are automatically members of the Content Manager role for all folders, reports, and resources. The Content Manager has rights to perform all tasks, excluding system-wide tasks.

The System User Role The system-wide security tasks have two predefined roles. The System User role has rights to perform the following system-wide tasks:

• Execute Report Definitions

• View Report Server properties

• View shared schedules

The System Administrator Role The System Administrator role provides the user with rights to complete any of the tasks necessary to manage the Report Server. All members of the Windows local administrators group on the computer hosting the Report Server are automatically members of the System Administrator role. This role has rights to perform the following system-wide tasks:

• Execute Report Definitions

• Manage jobs

• Manage Report Server properties

• Manage Report Server security

• Manage roles

• Manage shared schedules

Creating Role Assignments

Role assignments are created when a Windows user or a Windows group is assigned a role for a folder, a report, or a resource. Role assignments are created on the Security page for each item. These role assignments control what the user can see within a folder and what tasks the user can perform on the folder, report, or resource.

Let's try creating role assignments for some of our folders and reports.

Note 

To complete the next set of activities, you need a user who has rights to log on to the Report Server, but who is not a member of the local administrators group on that computer. You should know the password for this user, so you can log on as that user and view the results of your security settings.

Creating a Role Assignment for a Folder Let's try creating a new role assignment for the Home folder:

1. Open the Report Manager in your browser. You should be viewing the contents of the Home folder.

2. Select the Properties tab. You see the Security page for this folder.

3. Click New Role Assignment. The New Role Assignment page appears, as shown in Figure 15-70.

4. Type the name of a valid user for Group or User Name. If you are using a domain user or domain group, this must be in the format DomainName\UserName or DomainName\GroupName. If you are using a local user or local group, this must be in the format ComputerName\UserName or ComputerName\GroupName.

5. Check the check box for the Browser role.

6. Click OK to save your role assignment and return to the Security page. Reporting Services checks to ensure you entered a valid user or group for the role assignment. If this is not a valid user or group, you receive an error message and your role assignment is not saved.

Figure 15-70: The New Role Assignment page

Note 

A user needs to have viewing rights at least in the Home folder to view other folders and navigate to them.

Inherited Role Assignments By default, folders (other than the Home folder), reports, and resources inherit their role assignments from the folder that contains them. We can think of the nested folders as branches of a tree, with the reports and resources as the leaves. Inherited security means we can make security changes to one folder and have those changes take effect for all the branches and leaves further along the tree.

This makes managing security easy. We can maintain security for all the reports and resources within a folder simply by modifying the role assignments for the folder itself. We can maintain security for an entire branch of the tree structure by modifying the role assignments for the folder that forms the base of that branch.

The first time we make a role assignment for a folder, we first need to break the security inheritance that was in effect. A dialog box warns us that the inherited security structure will be broken. We need to confirm our decision to break inheritance before continuing with the role assignment.

Giving users only the rights they need is important. This prevents users from viewing data they should not see or from making modifications or deletions they should not be allowed to make. On the other hand, providing users with enough rights is important, so their reports function properly.

Role Assignments Using Windows Groups

As mentioned previously, role assignments can be made to Windows users or to Windows groups. If we create our role assignments using Windows users, we need to create a new set of role assignments every time a new user needs to access Reporting Services. This can be extremely tedious if we have a complex set of role assignments for various folders, reports, and resources.

In most cases, creating role assignments using Windows groups is better. Then, as new users come along, we simply need to add them to the Windows group that has the appropriate rights in Reporting Services. This is much easier!

Caution 

In some cases, Internet Information Services (IIS) and, therefore, Reporting Services do not immediately recognize changes to group membership. This is because IIS caches some Windows security information, and then works from that cache. Stopping and starting the IIS service causes the IIS security cache to be reloaded with the latest and greatest group membership information.

Linked Reports

In many cases, the security set up within Reporting Services restricts the folders a user can access. The sales department may be allowed to access one set of folders. The personnel department may be allowed to access another set of folders. The personnel department doesn't want to see sales reports and, certainly, some personnel reports should not be seen by everyone in the sales department.

This works well—a place for everything and everything in its place—until we come to the report that needs to be used by both the sales department and the personnel department. We could put a copy of the report in both places, but this gets to be a nightmare as new versions of reports need to be deployed to multiple locations on the Report Server. We could put the report in a third folder accessed by both the sales department and the personnel department, but that can make navigation in the Report Manager difficult and confusing.

Fortunately, Reporting Services provides a third alternative: the linked report. With a linked report, our report is deployed to one folder. It is then pointed to by links placed elsewhere within the Report Catalog, as shown in Figure 15-71. To the user, the links look just like a report. Because of these links, the report appears to be in many places. The sales department sees it in their folder. The personnel department sees it in their folder. The fact of the matter is the report is only deployed to one location, so it is easy to administer and maintain. Use the Create Linked Reports button on a report's General Properties page to create a linked report.

Figure 15-71: A linked report

Report Caching

One of the best features of Reporting Services is that the data is requeried each time the report is executed. The user is not viewing information from a static web page that is weeks or months old. Reporting Services reports include data accurate up to the second the report was run.

This feature can also be the source of one of the drawbacks of Reporting Services. The user is required to wait for the data to be requeried each time a report is run. If our query or stored procedure runs quickly, this may not be a problem. However, even fairly quick queries can slow down a server if enough of them are running at the same time. Fortunately, Reporting Services has a solution to this problem. The solution is report caching.

With many reports, it is not essential to have up-to-the-second data. We may be reporting from a data source that is only updated once or twice a day. The business needs of our users may only require data that is accurate as of the end of the previous business period, perhaps a month or a quarter. In these types of situations, it does not make sense to have the data requeried every time a user requests a report. Report caching is the answer.

Report caching is an option that can be turned on individually for each report on the Report Server. When this option is turned on, the Report Server saves a copy, or instance, of the report in a temporary location the first time the report is executed. On subsequent executions, with the same parameter values chosen, the Report Server pulls the information necessary to render the report from the report cache, rather than requerying data from the database. Because these subsequent executions do not need to requery data, they are, in most cases, faster than the report execution without caching.

Cached Report Expiration

Once an instance of the report is stored in the report cache, it is assigned an expiration date and time. The expiration date and time can be calculated in one of two ways. The expiration date can be calculated based on a certain number of minutes after the creation of the cached instance. For example, the cached instance of the report exists for 30 minutes, and then it is deleted. Or, the expiration date can be determined by a set schedule. For example, the cached instance of the report is deleted at 2:00 A.M. every Sunday morning.

The first type of expiration calculation is appropriate for a report that requires a large amount of database resources and is run often, but does not require up-to-the-second data. We can decrease the workload on the database server by fulfilling most of the requests for the report from the report cache. Every 30 minutes, we throw the cached report away. The next person who requests the report causes a new instance of the report, with updated data, to be placed in the report cache.

The second type of expiration calculation is appropriate for reports run against data that changes on a scheduled basis. Perhaps you have a report being run from your data mart. The data mart is updated from your transactional database each Sunday at 12:30 A.m. The data in the warehouse remains static in between these loads. The cached report is scheduled to expire right after the data load is completed. The next time the user requests the report after the expiration, a new instance of the report, with the updated data, is placed in the cache. This cached report contains up-to-date data until the next data load.

Cached Reports and Data Source Credentials

To create a cached instance of a report, the report must be using stored credentials. These can be credentials for either a Windows logon or a database logon, but they must be stored with the data source. If you think about this from a security standpoint, this is how it has to be.

Suppose for a minute that Reporting Services allowed a cached report to be created with Windows Integrated Security. The Windows credentials of the first person to run the report would be used to create a cached instance of the report. Subsequent users who request this report would receive this cached instance. However, this would mean the subsequent users are receiving data in the report created using the credentials from another user.

If the results of the database query or stored procedure that populates this report vary based on the rights of the database login, we have the potential for a big problem. If the vice president (VP) of sales is the first person to run the report and create the cached instance, all subsequent users would receive information meant only for the VP! Conversely, if a sales representative is the first person to run the report and create the cached instance, when the VP comes along later and requests the report, he will not receive all the information he needs.

The same problem exists if the report prompts for credentials. The first person who runs the report and creates the cached instance is the one who supplies the credentials. Everyone who views the cached instance is essentially using someone else's logon to see this data.

The only way that caching works without creating the potential for a security problem is with credentials stored with the report. In this situation, the same credentials are used to access the database—whether it is the VP or a lowly sales representative running the report. There is no risk that the cached instance of the report will create a breach in database security.

Enabling Report Caching

Let's try enabling caching for the Manufacturing By Machine Report. (The database credentials must be stored in the shared data source to complete the following exercise.)

1. Open the Report Manager and navigate to the BIStudioDeploy folder.

2. Click Show Details.

3. Click the icon in the Edit column for the Manufacturing By Machine Report. The Properties page for the Manufacturing By Machine Report appears.

4. Select Execution from the left side of the screen. The Execution Properties page appears, as shown in Figure 15-72.

5. Select the option "Cache a temporary copy of the report. Expire copy of report after a number of minutes."

6. Set the number of minutes to 45.

7. Click Apply.

Figure 15-72: The Execution Properties page

The first time the Manufacturing By Machine Report runs after caching is turned on, the report needs to perform its regular execution process to gather the data for the intermediate format. This intermediate format is then copied to the report cache before it is rendered for you in the browser.

Report Cache and Deploying

When a cached report instance expires, either because of a schedule or because it has existed for its maximum length of time, it is removed from the report cache. One other circumstance can cause a cached report instance to be removed from the report cache. If a new copy of a report is deployed from the Report Designer or uploaded using the Report Manager, any cached instances of that report are removed from the report cache.

Report Caching and Report Parameters

What happens with our report caching if different users enter different parameters when the report is executed? Fortunately, the Report Server is smart enough to handle this situation. As part of the instance of the report in the report cache, the Report Server stores any parameter values used to create that cached instance. The cached instance is used to satisfy requests made by a subsequent user only if all the parameters used to create the cached instance match the parameters entered by the subsequent user.

Report Caching and Security

Not all users can change report-caching properties. To change the report-caching properties for a report, you must have rights to the Manage Reports task. Of the four predefined security roles, the Content Manager, My Reports, and Publisher roles have rights to this task.

Execution Snapshots

Report caching is a great tool for improving the performance of reports with long execution times, but one problem still exists. The first user who requests the report after the cached instance has expired must wait for the report to be created from the underlying data. It would be nice if there were a way to have cached report instances created automatically, so no user has to endure these wait times. Fortunately, Reporting Services can do this as well.

An execution snapshot is another way to create a cached report instance. Up to this point, we have discussed situations where cached report instances are created as the result of a user action. A user requests a report, and a copy of that report's intermediate format is placed in the report cache. With execution snapshots, a cached report instance is created automatically.

Execution snapshots can create cached report instances on a scheduled basis or they can be created as soon as this feature is turned on for a particular report. If a schedule is used, each time the schedule is run, it replaces the current cached instance with a new one. Cached report instances created by an execution snapshot are used to satisfy user report requests the same as any other cached report instance. Execution snapshots can be either created manually or created automatically on a scheduled basis.

Execution Snapshots and Security

Not all users can change execution snapshots. To change the execution snapshot properties for a report, you must have rights to the Manage Reports task. Of the four predefined security roles, the Content Manager, My Reports, and Publisher roles have rights to this task.

Report History

The report history feature of the Report Manager enables us to keep copies of a report's past execution. This lets us save the state of our data without having to save copies of the data itself. We can keep documentation of inventory levels, production schedules, or financial records. We can look back in time, using the report history to do trend analysis or to verify past information.

Report History Snapshots and Report Parameters

To make a report work with report history snapshots, we have to provide a default value for each report parameter. These parameters cannot be changed when each snapshot is created. (They can be changed, however, if the report is run normally through the Report Manager.) Essentially, we are saving report history snapshots for one set of parameters. To save report history snapshots for other sets of parameters, we need to create linked reports with different sets of default parameters for each report. Report history snapshots can then be set up for each linked report.

Managing Report History Snapshots

Report history snapshots can start to pile up if we are not careful. Making business decisions about the number of history snapshots to save for each report is important. Even more important, then, is to implement those business decisions and manage the number of history snapshots being saved on the Report Server.

Updating Report Definitions and Report History Snapshots

One of the best features of report history snapshots is this: they are not lost if the definition of the underlying report is changed. Just like the cached report instance, the report history snapshot contains both the report definition and the dataset. Therefore, it is unaffected by subsequent changes to the report definition.

Standard Subscriptions

Reporting Services supports several types of subscriptions. The first is the standard subscription, which is a request to push a particular report to a particular user or set of users. The standard subscription is usually a self-serve operation. A user logs on to the Report Manager site and finds the report they want. The user then creates the subscription by specifying the schedule for the push delivery and the delivery options.

Standard subscriptions have two delivery options: e-mail and file share. The e-mail delivery option, of course, sends an e-mail to the specified e-mail addresses with a link to the report or with the report itself either embedded as HTML or as an attached document. The file share option creates a file containing the report in a specified folder on a file share. The file share option can be used to place the report into a document store managed and/or indexed by another application, such as Microsoft's SharePoint Portal Services.

Multiple Subscriptions on One Report

Nothing prevents a user from creating more than one subscription on the same report. Perhaps the user wants a report delivered every Friday and on the last day of the month. We can't do this with one subscription, but we can certainly do it with two—a weekly subscription for the Friday delivery and a monthly subscription for delivery on the last day of the month.

Another reason for multiple subscriptions is to receive a report run for multiple sets of parameters. It is possible to specify parameter values as part of the subscription properties. Using this feature, we can have one subscription send us a report with one set of parameters and another subscription send us the same report with a different set of parameters.

Standard Subscriptions and Security

Not all users can create standard subscriptions. In fact, it is possible to view a report, but not be able to subscribe to it. To subscribe to a report or create a subscription for delivery to others, you must have rights to the Manage Individual Subscriptions task. Of the four predefined security roles, the Browser, Content Manager, and My Reports roles have rights to manage individual subscriptions.

Data-Driven Subscriptions

A better name for a data-driven subscription might be "mass mailing." The data-driven subscription enables us to take a report and e-mail it to a number of people on a mailing list. The mailing list can be queried from any valid Reporting Services data source. The mailing list can contain fields, in addition to the recipient's e-mail address, which are used to control the content of the e-mail sent to each recipient.

Data-Driven Subscriptions and Security

Not all users can create data-driven subscriptions. To create a data-driven subscription for a report, you must have rights to the Manage All Subscriptions task. Of the four predefined security roles, only the Content Manager role has rights to this task.

Data-Driven Subscriptions and Event-Driven Behavior

We can do a couple of tricks with data-driven subscriptions that make them even more powerful. For instance, we might not want a subscription sent out until after a certain event has occurred. We may also want to e-mail a report to a number of recipients after a specific data update process has completed. While a data-driven subscription is a scheduled process rather than triggered by a particular event, we can make it behave almost as if it were event-driven.

We need a field in a status table that contains the completion date and time of the last data load. We also need a field in a status table that contains the date and time when the report was last distributed. With these two flag fields in place, we can simulate event-driven behavior for our data-driven subscription.

First, we need to build a stored procedure that returns the mailing list for the report distribution. To this stored procedure, add logic that checks the date and time of the last data load, and the date and time of the last report distribution. If the data load is complete and the report has not yet been distributed today, the stored procedure returns the mailing list result set. If the data load is incomplete or if the report has already been distributed today, the stored procedure returns an empty result set.

Now we create a series of data-driven subscriptions based on this stored procedure. If the data load completes sometime between 1:00 A.M. and 3:00 A.M., we might schedule one data-driven subscription to execute at 1:00 A.M., another at 1 :30 A.M., another at 2:00 A.M., and so on. When each data-driven subscription executes, the stored procedure determines whether the data load is complete and whether the report was already distributed. If the stored procedure returns a result set, the data-driven subscription e-mails the report to the mailing list. If the stored procedure returns an empty result set, the data-driven subscription terminates without sending any e-mails.

This same approach can be used to e-mail reports only when the report data has changed. We create a stored procedure that only returns a mailing list result set if the data has changed since the last time the report was e-mailed. This stored procedure is used to create a data-driven subscription. Now the data-driven subscription only sends out reports when the data has changed; otherwise, it sends nothing.