Introduction to SQL Server 2005 Express Edition Reporting Services Content Management

In the recent installments of our series dedicated to the most prominent
features available in SQL Server 2005 Express Edition, we have been discussing
Reporting Services. So far, besides presenting an overview of its basic
characteristics and stepping through its installation, we have also covered the
process of creating reports, employing functionality built-into the Report
Designer and leveraging sample reports published by Microsoft on its CodePlex
Web site. In this article, we will turn our attention to the process of
publishing the resulting reports, focusing in particular on content management.

As we have briefly demonstrated earlier, once a report is loaded into the
Business Intelligence Development Studio (in the form of an .rdl file and the shared data source associated
with it, incorporated into a project or solution), you have the ability to
deploy it to local Report Server. In order to accomplish this, you need to make
sure that the properties of the project accurately reflect the designated
location of the target virtual directory. To determine whether this is the
case, launch the Reporting Services Configuration Manager (ensure you are
invoking it using elevated privileges, which in the case of Windows Vista,
might require using the Run as
administrator option) and point to the local instance of the SQL
Server 2005 Express Edition in the Instance
Selection dialog box. Once the resulting window is displayed,
switch to the Report Server Virtual
Directory section and take note of a value appearing in the Name: text box (which, in our case, will
most likely be set to ReportServer$instance_name,
where instance_name
represents the designated SQL Server installation). Next, switch to the
Business Intelligence Development Studio, locate project node in the Solution Explorer window, and display
its Property pages. In the General
section of Configuration Properties,
adjust (if needed) the value of TargetServerURL,
such that it matches the name of Report Server virtual directory you just
identified (yielding as the result http://localhost/ReportServer$instance_name
entry). Note that within the same dialog box, you also have an option to assign
an arbitrary TargetReportFolder
and TargetDataSourceFolder
(which affect the location of the report on the Report Manager Web site), as
well as to specify whether to OverwriteDataSources
(which should be set to False
if there is an existing Report Server data source, different from the one used
during development). In addition, you can take advantage of multiple
configurations (most commonly, consisting of Debug,
DebugLocal, and Production) in order to quickly switch
between different deployment targets without modifying project properties.
Finally, keep in mind that it might be necessary (for example, when leveraging
sample reports) to adjust connection strings of data sources (.rds files) appearing under Shared Data Sources within the Solution
Explorer, so they point to local databases residing in the SQL Server 2005
Express instance.

Once all required changes are made, use the Deploy item in the context sensitive menu of your project
(note that the same option is also available for each individual report) to
make it available on the Report Server (where it can be accessed by end-users
via the Report Manager virtual directory). Providing that you have sufficient
permissions (we will discuss this subject in more detail later), such action
will publish the project’s reports and the data sources associated with them on
the Report Manager site (located at http://localhost/Reports$instance_name
URL), accessible through links whose names are derived from values of TargetReportFolder and TargetDataSourceFolder parameters you
assigned earlier. (To monitor progress of the deployment, observe messages
displayed in the Output window within the Business Intelligence Development
Studio).

In order to verify the outcome, launch Internet Explorer (make sure that you
run it in the security context of the same privileged user account that was
used to publish the reports) and point it to the Report Manager virtual
directory at the http://localhost/Reports$instance_name
URL, which is designated as the SQL Server
Reporting Services Home page. Its content is divided into two
tabs, labeled Content and Properties. For the time being, we will
focus on the first of them (and leave the discussion about the second until our
next article) that allows you to organize report hierarchy presented to
end-users. The page should contain all published up-to-date projects, along
with their reports and data sources (specifics of their placement depends on
the values of TargetReportFolder
and TargetDataSourceFolder
project properties you assigned in the Business Intelligence Development
Studio, although you can easily make changes to the Report Manager site
layout after deployment is completed). All newly published items are marked
with the !NEW label.
Navigation is intuitive, allowing you to drill down through project hierarchy
to a desired report.

Another way to populate the Report Manager site (along with the Report
Server database) involves options available from its Web interface (which is
convenient considering that eliminates the dependency on Business Intelligence
Development Studio). Each of the content pages offers relevant functionality
via command buttons appearing in their common toolbar. New Folder is intended for building a
custom hierarchy of reports. Their definitions can be imported via the Upload File button, effectively adding
them into the Report Server database. (Incidentally, the same mechanism can be
used to populate Report Manager pages with other types of resources, such as
image files). In order to ensure that all data sources that a report
incorporated in such manner might require are also present, switch to the
detailed view (by clicking on the Show
Details in the upper right corner of the Report Manager page) and
click on the icon appearing in the Edit
column next to the report name. This will display the content of the Properties tab, with a link to Data Sources. By following it, you will
be able identify whether a valid data source exists. If this is not the case,
you can either leverage one of the existing shared data sources (Browse command button on the same page
allows you to locate them within the Report Manager site hierarchy) or define a
custom one (this is done directly from Data
Sources section of the Properties
tab or by invoking New Data Source
option in the toolbar on the report’s Content
page). In this case, you will be expected to specify a connection type (the
only option available in our case is Microsoft
SQL Server), connection string (in the form Data Source=localhostinstance_name;Initial
Catalog=database_name), and security context in which the
report will be executed. The last of these options allow you choose among
credentials supplied by the user running the report (requested via a
customizable prompt), credentials stored securely in the Report Server database
(which you type in directly within designated textboxes on the same page),
Windows integrated security, and anonymous connection.

It is also possible to deploy a report (along with its data sources) using
custom .NET scripts, which can be invoked via the rs command line utility, accompanying SQL Server 2005
Express Edition Reporting Services. (Note that this utility is simply a vehicle
to execute arbitrary code against a Report Server and has other potential uses,
as we will demonstrate in our future articles). To examine its purpose in the
currently discussed context, we will leverage the PublishSampleReports.rss file, included with SQLServerSamples.msi we referenced
in the previous installment of our series, residing in Program FilesMicrosoft SQL
Server90SamplesReporting ServicesScript Samples folder. To
deploy it, run rs -i PublishSampleReports.rss
-s http://localhost/ReportServer$instance_name from the
Command Prompt (where instance_name
is the name of the local SQL Server 2005 Express Edition instance), while
logged on as a privileged user to a target computer. As the result, all of the
AdventureWorks sample reports (and their corresponding data sources) will
appear on the Report Manager Web site.

By clicking on the Show Details
link in the upper right corner of Report Manager pages, you should be able to
view additional data about each of their items, identifying their type,
description, the latest modification and execution dates, as well as the name
of a user who performed the most recent edit. There are also several actions
that you can invoke from this interface. In particular, you have the ability to
delete or move individual folders, reports, and shared data sources. By
clicking on an icon in the Edit
column, you can alter the display name or description of a corresponding item,
as well as hide it in the list view (on the Properties
tab of a report page). From this interface, it is also possible to load the
report definition in the form of an XML formatted .rdl file (via Edit
link in the Report Definition
section) and subsequently update its Report Server-resident version (via Update link).

Clicking on a report link triggers its generation, rendering output
displayed in the same browser window (as the result, View tab replaces Content). Note that, unlike in Reporting
Services implemented in full-fledged editions of SQL Server 2005, there is no
capability to schedule report execution (this feature is exposed via the Execution Properties section on the Properties tab, missing from our
scaled-down implementation of Report Manager). Interacting with the report
displayed in the HTML Viewer interface is simplified through the use of a
toolbar, which includes such functionality as paging, scaling its view, search,
export (in Acrobat PDF or Excel format), refresh, and printing.

In the next article of our series, we will continue our discussion about
Report Manager capabilities, focusing on more advanced content management
features.

»


See All Articles by Columnist Marcin Policht