Report Manager

In the previous section, we learned how to create wonderful reports from our relational, OLAP, and data mining data. Creating the report, however, is only half the battle. We need to place these reports where they can be put to use and, once they are there, we need to manage them.

First, we need to know how to deploy our report to the Report Server. This can be done from the Business Intelligence Development Studio or from Visual Studio 2005. It can also be done from the Report Manager web application.

Once on the Report Server, we can organize our reports to assist users in finding what they need. We can set up security to control access to the reports. We can also define caching schemes to make the most efficient use of the computing resources we have available.

Folders

Before we deploy reports to the Report Server, we need to have an understanding of the way the Report Server organizes reports in the Report Catalog. In the Report Catalog, reports are arranged into a system of folders similar to the Windows file system. Folders can contain reports, supporting files such as external images and shared data sources, and even other folders. The easiest way to create, view, and maintain these folders is through the Report Manager.

Although the Report Catalog folders look and act like Windows file system folders, they are not actual file system folders. You cannot find them anywhere in the file system on the computer running the Report Server. Report Catalog folders are screen representations of records in the Report Catalog database.

The Report Manager

The Report Manager web application provides a straightforward method for creating and navigating folders in the Report Catalog. When we initially install Reporting Services, the Home folder is created by default. This is the only folder that exists at first.

By default, the Report Manager site is installed in the default website on the server. It is located in a virtual directory called Reports. Therefore, the default URL for the Report Manager is

 http://ComputerName/reports 

where ComputerName is the name of the computer hosting Reporting Services.

No matter how we get there, the Report Manager appears similar to Figure 15-63.

Figure 15-63: The Report Manager with no folders defined

Note 

Figure 15-63 shows the Report Manager as it appears for a user with content manager privileges. If you do not see the New Folder, New Data Source, Upload File, and Report Builder buttons in the toolbar on the Contents tab, you do not have content manager privileges and will be unable to complete the exercises in this section of the chapter. If possible, log out, and then log in with a Windows login that has local administration privileges on the computer running the Report Server.

To use the Report Manager, we must be using Microsoft Internet Explorer 6.0 with Service Pack 1 (SPI) or Internet Explorer 5.5 with Service Pack 2 (SP2). In either case, scripting must be enabled.

Deploying Reports Using the Report Designer

The most common method of moving reports to the Report Server is by using the Report Designer. Once we are satisfied with a report, we can make it available to our users without leaving the development environment. This capability to create, preview, and deploy a report from a single authoring tool is a real plus.

Note 

As we have done in other chapters, we are not going to do a specific Learn By Doing exercise here. Instead, you are encouraged to follow along and try each of these activities.

Deploying Reports in the MaxMinReports Project Using the Report Designer

Let's try deploying the reports in our report project:

1. Start the Business Intelligence Development Studio and open the MaxMinReports project, if it is not already open.

2. Select Project | MaxMinReports Properties from the Main menu. The MaxMinReports Property Pages dialog box appears.

3. Enter MaxMinReports/DataSources for TargetDataSourceFolder.

4. Enter MaxMinReports/BIStudioDeploy for TargetReportFolder.

5. Type http://ComputerName/ReportServer for TargetServerURL, where ComputerName is the name of the computer where the Report Server is installed. If you are using a secure connection, you should replace http: with https:. The MaxMinReports Property Pages dialog box appears as shown in Figure 15-64.

6. Click OK to exit the MaxMinReports Property Pages dialog box.

7. Right-click the MaxMinReports project entry in the Solution Explorer window and select Deploy from the Context menu.

8. The Report Designer builds all the reports in the project, and then deploys all the reports, along with their supporting files, to the Report Server. (During the build process, the Report Designer checks each report for any errors that would prevent it from executing properly on the Report Server.) The results of the build and deploy are shown in the Output window.

9. Open the Report Manager in your browser. Click the MaxMinReports folder to view its content. Click the BIStudioDeploy folder to view its content. All the items in the MaxMinReports project—four reports and a shared data source—were deployed.

10. Click the Wholesale Sales By Month report. You see the HTML version of the report.

Figure 15-64: The MaxMinReports Property Pages dialog box

Note 

You can also deploy the contents of a project by selecting Build | Deploy {Project Name} from the Main menu.

Deploying a Single Report

In Step 7, you used the project's Context menu to deploy all the items in the project. Alternatively, you could have right-clicked a report and selected Deploy from the report's Context menu. This would have deployed only this report, not the entire project.

On some occasions, you might want to deploy a single report, rather than the entire project. At times, one report is going to be completed and ready for deployment, while the other reports in the project are still under construction. At other times, one report is revised after the entire project has already been deployed. In these situations, it is only necessary to redeploy the single revised report.

Uploading Reports Using Report Manager

Another common method of moving a report to the Report Server is by using the Report Manager. This is known as up loading the report. Deploying reports from the Report Designer can be thought of as pushing the reports from the development environment to the Report Server, whereas uploading reports from the Report Manager can be thought of as pulling the reports from the development environment to the Report Server.

You may need to use the Report Manager upload in situations where your report authors do not have rights to deploy reports on the Report Server. The report authors create their reports and test them within the Report Designer. When a report is completed, the report author can place the RDL file for the report in a shared directory or send it as an e-mail attachment to the Report Server administrator. The Report Server administrator can upload the RDL file to a quality assurance Report Server and test the report for clarity, accuracy, and proper use of database resources. Once the report has passed this review, the Report Server administrator can upload the report to the production Report Server.

Uploading Reports in the MaxMinReports Project Using the Report Manager

Let's try uploading some of the Manufacturing By Machine Report to a different folder:

1. Open the Report Manager in your browser. Click the MaxMinReporls folder to view its content.

2. Click New Folder in the toolbar of the Contents tab.

3. Enter RMUpload for Name.

4. Enter Uploaded Reports for Description.

5. Click OK to create the new folder.

6. Select the new folder to view its contents.

7. Click the Upload File button in the toolbar on the Contents tab. The Upload File page appears, as shown in Figure 15-65.

8. Click Browse. The Choose File dialog box appears. Navigate to the folder where you created the MaxMinReports project.

9. Select the RDL file for the Manufacturing By Machine Report and click Open to exit the Choose File dialog box.

10. Click OK to upload the file.

11. The Manufacturing By Machine Report has been uploaded to the RMUpload folder.

12. Click the Manufacturing By Machine Report to execute it. You see an error similar to the one in Figure 15-66. You received this because, unlike the deployment from the Report Designer, the upload in Report Manager did not bring the shared data source along with the report.

13. Click the link to the RMUpload folder at the top of the page.

Figure 15-65: The Upload File page

Figure 15-66: The Reporting Services Error page

Creating a Shared Data Source in the Report Manager

To get the Manufacturing By Machine Report functioning, you need to provide it with a shared data source. One way to do this is to create a new shared data source using the Report Manager. Follow these steps:

1. Click the New Data Source button in the toolbar on the Contents tab. The New Data Source page for a shared data source appears, as shown in Figure 15-67.

2. Type MaxMinManufacturingDM for Name.

3. Type Connection to the MaxMinManufacturing Database for Description.

4. Make sure Microsoft SQL Server is selected in Connection Type. Other options here are OLE DB, Microsoft SQL Server Analysis Services, Oracle, ODBC, and XML.

5. Type Data Source=(local); initial catalog=MaxMinManufacturingDM for Connection String. If the MaxMinManufacturingDM database is not on the Report Server, but is on a different computer, put the name of that computer in place of (local) in the connection string.

   Note 

   Do not include the parentheses if you use a computer name in place of (local).

6. Select the option Windows Integrated Security.

7. Click OK to save the data source and return to the RMUpload folder.

8. Click the Manufacturing By Machine Report to execute it. You receive the same error message page because we have not yet told the report to use our new data source.

9. Select the Properties tab. The Properties page for the Manufacturing By Machine Report appears.

10. Click the Data Sources link on the left side of the screen. The Data Sources page for an individual report appears.

11. A shared data source should be selected. Click Browse. The Select a Shared Data Source page appears.

12. Expand each folder in the tree view under Location until you can see the MaxMinManufacturingDM shared data source in the RMUpload folder. Click the MaxMinManufacturingDM shared data source. The path to the MaxMinManufacturingDM shared data source is filled in Location. (You can also type this path into Location if you do not want to use the tree view.)

13. Click OK to exit the Select a Shared Data Source page.

14. Click Apply at the bottom of the page.

    Note 

    It is easy to forget to click Apply when making changes to a report's data sources. If you do not click Apply, none of your changes are saved. This can lead to confusion, frustration, and wasted troubleshooting time. At least, that is what I have been told.

15. Select the View tab to view the report. The report now generates using the new shared data source.

16. Once the report has completed generating, click the RMUpload link at the top of the page.

Figure 15-67: The New Data Source page

Hiding an Item

The MaxMinManufacturingDM shared data source we just created can be seen when browsing through folders using the Report Manager. We may not want these types of items to be readily visible to the user. It makes more sense to have the shared data sources out of sight of the users. Fortunately, Report Manager provides a way to do just that:

1. Click the MaxMinManufacturingDM data source. The Data Source Edit page appears.

2. Check the Hide in List View check box.

3. Click Apply to save this change.

4. Click the RMUpload link at the top of the page.

The MaxMinManufacturingDM data source is no longer visible in the list view. You can use this same technique to hide reports you do not want to have generally available to users browsing through the folders.

If you do need to edit the MaxMinManufacturingDM data source, you can view it by using the detail view of the folder. Follow these steps:

1. Click the Show Details button in the toolbar on the Contents tab. The MaxMinManufacturingDM data source is now visible in this detail view, as shown in Figure 15-68. By default, the detail view is in alphabetical order by name.

2. Click the Type column heading. The detail view is now sorted by type in ascending order. (In an ascending sort by type, the reports are at the top of the list, with supporting items, such as shared data sources, at the bottom.) Note, the downward, black arrow is now next to the Type column heading on your screen.

3. Click the Type column heading again. The detail view is now sorted by type in descending order. Now the black arrow is pointing upward next to the column heading.

Figure 15-68: RMUpload folder detail view

Note 

The name of the sort order (ascending or descending) and the direction of the black arrow may seem opposite to one another. Remember this: in an ascending sort, you move from smaller values (A,B,C…)to larger values (…X,Y, Z). When you move through the list in the direction of the arrow, you also move from smaller values to larger values.

Uploading Other Items Using Report Manager

In addition to reports and shared data sources, other items can be uploaded to Report Server folders. External images needed as part of the reports can be uploaded, for example, as well as documentation and other supporting materials. Anything that can be viewed in Internet Explorer—even Word documents and PowerPoint presentations—can be uploaded to the Report Server and accessed by the users.

Printing from Report Manager

No matter how convenient you make it for your users to access reports in a browser, and no matter how many interactive drilldown and drillthrough features you provide, your users always want to print their reports on paper. You can explain all the wonders of the multiple, cascading parameters you have created until you are blue in the face, but some users always need to touch and feel the numbers on paper. They need to be able to put something in a briefcase and take it home with them at night. It doesn't matter that they could receive up-to-date numbers through their VPN at home. They want ink on paper.

Reporting Services provides several options for printing a report from Report Manager. Each provides some advantages and disadvantages for the user.

HTML Printing

These users could just press the Print button in their browser and get whatever type of printout HTML printing provides. As you are probably aware, HTML printing is not a good choice when formatting is important, as it usually is for reports. Lines of text can wrap in unusual ways or simply be cut off. A line of text at the bottom of the page can even be cut right in half, with the top-half on one page and the bottom-half on the next page.

Fortunately, the Report Manager provides a couple of alternatives to HTML printing.

Printing from a PDF Document or a TIFF File

A PDF document or a TIFF file does an excellent job of maintaining report format when a report is printed. Therefore, when users want to have a high-quality report printout, they can export the report to a PDF document or a TIFF file. Once this is complete, they can view the exported report using the appropriate viewer: Adobe Acrobat Reader for the PDF document and the Windows Picture and Fax Viewer for a TIFF file. The report can then be printed using the viewer.

This process provides the user with a quality printout. However, not all users are comfortable with saving a file to a local disk, finding that file and opening it in the appropriate viewer, and then printing the report. There is another printing alternative, which is even more straightforward.

Client-Side Printing

You may have noticed a button with a printer icon on the report toolbar. This button is for the client-side printing feature of Reporting Services. Client-side printing works through an ActiveX object downloaded to the user's computer. From then on, whenever the Client-Side Printing button is clicked, this ActiveX object provides the user interface and controls the printing.

The first time a user activates the client-side printing feature, they may be prompted with a security warning about the ActiveX download. After taking the appropriate precautions, such as making sure the ActiveX object is signed by Microsoft, the user should approve the download to enable client-side printing. Once the ActiveX has been downloaded by this first use, it does not need to be downloaded again.

If a user has trouble downloading the ActiveX control, they may need to set the Report Manager as a trusted site in their browser. This is done on the Security tab of the Internet Options dialog box. The user should not lower their security setting for all sites in general to accomplish the ActiveX download.

Once downloaded, client-side printing enables users to set various report attributes. These include margins, page size, and even page orientation. Users can also preview a report before putting it on paper.