Managing Your Analysis Services Reports

Most likely you are the administrator on your machine and you are able to design, deploy, and view reports on your machines. However you do not want to provide administrative privileges to all your users. If you are a Report Server administrator then you would need to provide the appropriate access to your report designers who build and deploy reports as compared to the end users who consume the reports. You can also provide certain administrative privileges to certain users on your Report Server machine. Managing your Report Server by itself can be a separate chapter since it is so vast and is not covered completely in this section. However in this section you will learn a few basic operations of management including defining security permissions on how the reports have to be rendered, as well as creating permissions to end users to view reports which are specifically targeted towards reports built on top of Analysis Services databases. Finally, you will learn to automate reports so that they can be delivered to end users on a periodic basis.

Security and Report Execution

First and foremost you need to define the right permissions under which Reporting Services should retrieve the data from Analysis Services. In order to define the permissions click on the Properties tab of a report as shown in Figure 17-39. You will see the options General, Data Sources, Execution, History, and Security. The General tab provides information about the report such as the user who create the report along with the date and time when it was created or accessed. The Data Sources tab allows you to specify specific credentials under which Report Server should retrieve data from the data source. The Analysis Services Report shown in Figure 17-39 has a custom data source connecting to the AnalysisServices2005Tutorial. When the report is deployed from BIDS the default setting for Report Server connection to Analysis Services is Windows Integrated Security. You can modify this setting to one of the remaining three options. In general the users of the Analysis Services Report need not have access to query data from the database AnalysisServices2005Tutorial. In such circumstances you would need to choose the option Credential stored securely in the report server and provide a valid domain username and password which has access to query data from an Analysis Services instance. We recommend the use of this option compared to other options if you want to ensure that your users can view the report without appropriate permissions set on your Analysis Services database. If you want to provide access to users whose security is managed only through Analysis Services instance then choose the option Credentials supplied by the user running the report. Specify the right permissions and then click on the Execution tab.

Figure 17-39

The Execution tab shows the various ways in which the report can be rendered on the report server (see Figure 17-40). You can have the report run at the time when users access the report or schedule the report to be run every 30 minutes or specific time interval. In this way data is cached on your report server ensuring the reports are rendered immediately. If you do want the most recent data then you should not cache the report. The query to retrieve data from the data provider (in this example it is Analysis Services) can take a long time. You do have the option of specifying snapshots at which this specific report or additional reports get rendered and stored on the report server using the option Render the report from a report execution snapshot. Report server provides you certain options in report execution to limit the amount of time the report server should wait to retrieve the data. The last option under Report Execution Timeout is to define a specific timeout value. Use the default settings for report Execution and then click on the Security tab.

Figure 17-40

The Security tab provides you the option of specifying security to the end users accessing the report. You can add new users and provide access to specific roles from Content Manager who has administrative rights to just viewing reports. Figure 17-41 shows the default security settings for the reports.

Figure 17-41

If you want to add a new user and provide specific permissions for the user then click on New Role Assignment. You will now be in the New Role Assignment page as shown in Figure 17-42. Specify the domain username or a group next to Group or user name. You next need to select the predefined roles on your report server such as Browser, Content Manager, etc. If you want to see what type of tasks a specific role can accomplish, click on the role. You also have the ability to define new roles and select specific tasks by clicking on the New Role button. Once you have chosen the roles for the user then click OK. You will now be in the default Security page which lists all the users or groups who have specific access permissions for the report.

Figure 17-42

You have successfully learned to specify credentials under which Report Server needs to connect to your Analysis Services instance, report execution parameters, and finally provide access to your end users. You can perform all the management operations available through the web interface through SQL Server Management Studio by connecting to your Report Server.

Automating Your Reports

Your end users can access reports through the web interface. In addition to this, Reporting Services 2005 provides you certain ways of delivering the reports to end users through a file server or email. In order to deliver reports at periodic intervals you need to setup report subscriptions. Click on the My Subscription tab for a specific report. You will now be in the subscriptions page where you can define a regular subscription or data driven subscription. Click on the New Subscription button and you will see the subscription page as shown in Figure 17-43.

Figure 17-43

The subscription page allows you to choose a specific subscription type. Reporting Services 2005 supports two types of subscription delivery — email and file share. You can build your own subscription delivery mechanisms through the extensions provided by the Reporting Services platform. In the subscription page select the Report File Share delivery as shown in Figure 17-43. You now need to choose a format for the report (PDF, Excel, CSV, etc.), specify the path where the report needs to be delivered and credentials to access the file share where the report needs to be delivered. Once you specify the options for report file share delivery you need to specify when the report needs to be delivered. The report can be delivered based on a schedule that you define in the subscription page of a shared schedule that was already defined. Once you specify the delivery options click OK. The report is then scheduled to be run using SQL Agent and hence you need to have SQL Agent running on your machine. Reports will be delivered to the file share based on the defined schedule and the end users can access the reports from that file share.

The second delivery option is to deliver the reports via email. You need to setup your mail server configurations on your report server and enable the email delivery option. Refer to products documentation on setting up the email delivery option on your report server. Once the email delivery option is enabled you can choose this option and specify the email addresses for the TO, CC, and BCC lines along with the delivery schedule.