Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jul 13, 2009

Introduction to SQL Server 2005 Express Edition Reporting Services Content Management

By Marcin Policht

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=localhost\instance_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 Files\Microsoft SQL Server\90\Samples\Reporting Services\Script 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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM