Taking Advantage of Sample Reporting Services Reports

Monday Jun 22nd 2009 by Marcin Policht

Recent installments of our series dedicated to SQL Server 2005 Express Edition have discussed the Reporting Services component. This article provides an overview of sample reports that you can take advantage of in order to familiarize yourself with the more advanced graphical designer features present in SQL Server 2005 Express Edition-based Reporting Services.

In the recent installments of our series dedicated to the most prominent features of SQL Server 2005 Express Edition, we started coverage of its Reporting Services component (included as part of its Advanced Services download), focusing primarily on its core functionality, setup, and initial configuration. We also have demonstrated the process of creating a custom report, leveraging a query (referencing AdventureWorks database) described in the Sales and Marketing Scenario MSDN article, by utilizing both Report Project template and its wizard-based counterpart, available from the Business Intelligence Development Studio interface. In this article, we will provide an overview of sample reports that you can take advantage of in order to familiarize yourself with more advanced graphical designer features present in SQL Server 2005 Express Edition-based Reporting Services.

There are three basic types of sample reports made available by Microsoft to the general public. The first one leverages AdventureWorks database as the data source, which in the case of SQL Server 2005 Express Edition, needs to be separately downloaded (as we have mentioned in one of our earlier articles). The second one demonstrates ad hoc reporting capabilities (also utilizing data derived from AdventureWorks database), but, due to its dependency on Report Builder (absent from SQL Server 2005 Express Edition Reporting Services), is outside of the scope of our discussion. The third one is intended for SQL Server management (giving you the ability to generate schemas and table inventory in an arbitrary database) and relies on the master database as its data source.

All of the samples referenced above are published as a freely downloadable, Windows Installer-based package SQLServerSamples.msi (available in x32 and x64-bit versions). Invoking the package triggers an InstallShield driven wizard, prompting you to accept the Microsoft Permissive License and designate a destination folder (which, by default, is set to Program Files\Microsoft SQL Server\90\Samples). The installation process creates a directory structure in that location, consisting of three subfolders named AdventureWorks Sample Reports, Report Builder Sample Reports, and Server Management Sample Reports (further divided into Execution Log Sample Reports and Server Management Sample Reports) residing in the Reporting services\Report Sample folder. You can access any of the reports stored in these locations from Business Intelligence Development Studio (by selecting Open->Project/Solution... entries in the File menu and pointing to an appropriate solution or project file).

Let's take a closer look at the AdventureWorks sample reports (before you analyze them in more detail, it is advisable to refer to the documentation describing AdventureWorks schemas included in the MSDN Library, which should help you understand the structure and purpose of its tables, as well as the relationship between them). It is important to note that some of them contain (sometimes seemingly) erratic output, which might be the cause of unnecessary confusion. We will identify them shortly, but in order to demonstrate their shortcomings properly, we first need to step through their deployment (which, in turn, requires modifying their default configuration).

Start by opening the Adventure Works Sample Reports solution (AdventureWorks Sample Reports.sln) from within the Business Intelligence Development Studio (keep in mind that you should launch it with elevated privileges, if you are using Windows Vista). Once the solution and its project are loaded, you should notice a list of predefined reports (Company Sales.rdl, Employee Sales Summary.rpl, Product Catalog.rpl, Product Line Sales.rpl, Sales Order Detail.rpl, Sales Reason Comparisons.rpl, and Territory Sales Drilldown.rpl) as well as shared data sources (AdventureWorks.rds and AdventureWorksAS.rds) appearing in the Solution Explorer window. In order to properly deploy any of these reports, you have to adjust the value of TargetServerURL entry in the General section in the project's Property Pages dialog box by changing it from its default http://localhost/reportserver to http://localhost/ReportServer$instance_name (where instance_name designates the name of SQL Server 2005 Express Edition instance hosting the Report Server databases). In addition, you should set the OverwriteDataSources property to True. Furthermore, you also have to alter the connection strings pointing to the local AdventureWorks database of AdventureWorks.rds Shared Data Source from its original value Data Source=(local);Initial Catalog=AdventureWorks to Data Source=localhost\instance_name;Initial Catalog=AdventureWorks. Once these modifications are applied, you can publish individual reports to the local Report Server Web site using the Deploy option in their context-sensitive menu in the Solution Explorer window (as the result, they will appear in the AdventureWorks Sample Reports folder on the SQL Server Reporting Services home page).

The problematic reports include Product Line Sales and Territory Sales Drilldown. The first of them is supposed to provide a listing of the top five sales people and stores (on the product category and subcategory basis, within an arbitrary period), along with corresponding charts, tables, multi-valued parameters, calculated fields, and drilldown links (the last of these features requires deployment of Employee Sales Summary report). Unfortunately, the underlying queries used to calculate sales amounts are simply incorrect. Correcting this issue involves modifying TopEmployees and TopCustomers datasets (via the Data tab in the designer interface in Business Intelligence Development Studio) according to the T-SQL statements listed on the Runic Data Warehousing blog. In order to be able to display the other faulty report (its top level view gives you overall sales numbers on per territory basis, with the ability to drill down to the salesperson and order number levels, as well as drillthrough to details of each order) you need to deploy both Territory Sales Drilldown and Sales Order Detail reports (since they are linked). The primary issue in this case is a discrepancy between sales numbers for individual orders appearing on the Territory Sales page and the corresponding Sales Order Detail. As it turns out, this problem results from inaccuracies within AdventureWorks database, as pointed out on the same Runic Data Warehousing blog (apparently both of these issues have been fixed in the SQL Server 2008 version of sample reports). Despite these drawbacks, the sample AdventureWorks reports offer an opportunity to become familiar with a number of more advanced features such as conditional formatting (Product Catalog, Territory Sales), drillthrough and drilldown (Company Sales, Employee Sales Summary, Product Line Sales, Sales Order Detail, Territory Sales), multiple datasets (Employee Sales Summary), dynamic or multivalued parameters (Employee Sales Summary, Product Line Sales), or document maps (Product Catalog).

Now let's turn our attention to the server management reports. Just as AdventureWorks-based samples, they are installed as part of the SQLServerSamples.msi Windows Installer package, so assuming that you already have installed it on the computer hosting SQL Server 2005 Express Edition, you should be able to find them in the Reporting Services\Report Samples\Sever Management Sample Reports subfolder (under the Program Files\Microsoft SQL Server\90\Samples folder). The Server Management Sample Reports solution consists of two reports that are of interest to us. (You will also find Execution Log reports there, intended for analysis of the execution log data of the Report Server, which are not relevant in the context of our discussion, due to their dependency on Integration Services, not available in SQL Server 2005 Express Edition). The first one, named Schema Table Extended Properties, lists schemas, tables, columns, and their properties for an arbitrarily selected database. The second one, labeled Columns by Table and Schema Subreport is actually a sub-report utilized by the first one (it allows you to display properties of a column in an arbitrary table, once you specify its name, schema, and database it is residing in).

In order to deploy and examine them, start by loading the Server Management Sample Reports.sln via Open->Project/Solution... entry in the File menu of Business Intelligence Development Studio. As before, you will need to modify the Server Management Sample Reports project properties (via its Property Pages dialog box), including TargetServerURL (which should be changed from its default http://localhost/reportserver to http://localhost/ReportServer$instance_name) and OverwriteDataSources property (which should be set to True). In addition, you need to alter the connection string of the master.rds Shared Data Source from Data Source=(local);Initial Catalog=master to Data Source=localhost\instance_name;Initial Catalog=master.

As the result, a new folder labeled Server Management Sample Reports should appear on your SQL Server Reporting Services Home page. Once you reveal its content, you will be able to find the Schema Table Extended Properties report there, that allows you to select a target database (from the DatabaseName listbox) for which you want to display schemas and columns. After the selection is made, you can explore each schema and column (down to its property level) in the drilldown fashion.

This concludes our overview of graphical features available in SQL Server 2005 Express Edition-based Reporting Services. Starting with the next article, will explore topics dealing with report publishing and access management in more detail.

» See All Articles by Columnist Marcin Policht

Mobile Site | Full Site