Taking Advantage of Sample Reporting Services Reports

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 FilesMicrosoft SQL Server90Samples).
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 servicesReport
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=localhostinstance_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 ServicesReport
SamplesSever Management Sample Reports subfolder (under the Program FilesMicrosoft SQL Server90Samples
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=localhostinstance_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