Table of Contents- Part 1
- Introduction
- Reporting Services Installation
- Programming Reporting Services
- Report Manager
- Part 2
- Parameterized Reports
- Calling Stored Procedures from Reports
- The Report Viewer Control
- Part 3
- Using Reporting Server Web Services
- Using Custom Code in Reports
- Subscription Scheduling in Reporting Server
- Caching and Rendering Preference
- Conclusion
- References and Further Readings
- About The Author
- Download Sample Code
Introduction Business Intelligence is a thriving discipline in the marvelous era of computing we live in. It's the process of analyzing and exploring the information, trends and patterns hidden in data. BI escalates the business roadmap and facilitates business users to deliver better strategic solutions. This process helps enterprise decision makers by providing data models, statistical analysis, forecasting for comprehensive data analysis. Microsoft provides the most comprehensive integrated business intelligence, data mining, analysis and reporting solution with its state of the art Microsoft SQL Server Analysis services and Microsoft SQL Server reporting services. With the modern architecture design based on XML web services, SQL Server Reporting Services provides highly flexible, scalable, customizable and high performance reporting solution in a cost effective way. This article is a developer's introduction to SQL server reporting services, its functionality and how it can be used to provide state of the art reporting solutions. SQL Server Reporting Services, due to its robust but user friendly architecture, is an obvious choice for
Enterprise or in-house reporting, for product management, sales, and human resource and finance departments.
Its flexibility makes it an ideal for usage in applications as well
(Deliver User-Friendly Reports from Your Application with SQL Server Reporting Services - MSDN Magazine August 2004). Reporting Services offer various delivery methods, from ftp to email and it provides various rendering formats therefore it makes adds easiness to business to business reporting. Similarly because of its flexibility and industry standard security model, it makes extranet and secure internet reporting easily achievable. Reporting Services - A Quick Overview - Product Overview
- Get an overview of SQL Server Reporting Services and how it can provide a cost-effective means for creating, managing, and delivering both traditional and Web-based reports.
- Datasheet
- Take a closer look at the key features of SQL Server Reporting Services.
- Features Comparison
- See what features are available in the different editions of SQL Server Reporting Services.
- Evaluation Guide
- Download the SQL Server Reporting Services evaluation guide.
- Demo: Features and Benefits
- This Flash demo provides a high-level overview of the features, architecture, and benefits available with SQL Server Reporting Services.
- Demo: How to Author, Manage, and Deliver Reports
- See SQL Server 2000 Reporting Services in action. This video demonstrates the process of authoring, managing, and delivering reports using Reporting Services.
- Trial Software
- Download or order the SQL Server Reporting Services 120-day evaluation software.
- System Requirements
- Find out what you need to run SQL Server Reporting Services.
- News and Reviews
- Read the latest news about SQL Server Reporting Services.
- Case Studies
- Find out how organizations are benefiting from SQL Server Reporting Services in real-world situations.
- Benefits of Leveraging Reporting Services in Your Application
- Get a brief overview of how using SQL Server 2000 Reporting Services with your legacy systems or custom applications can help you add more value to your reporting infrastructure.
Courtesy Microsoft Corporation - Reporting Services Product Information
The reporting system comprises for two main components. - Report Server & Report Server Database
Report Server is an integrated web service which controls report generation and management. Report Server database is a SQL server database which is used as data dictionary about reports (catalogue, groups) and caching. SQL server agent is used for scheduling the reports. - Report Manager
An ASP.NET based Web interface for managing the reports, setting security and user permissions.
Report Designer is considered as a part of reporting services in Microsoft documentation but because RDL (Report definition language) is an XML based open standard, any vendor can implement it and therefore a single tool can't be categorized as report designer. However, Microsoft provides a graphical report authoring tool with Visual Studio.NET 2003 for creating reports which automatically creates RDL markup at the backend. Reporting services were designed with concept of disparate data sources in mind. A single report can retrieve data from multiple heterogeneous databases and render it to make it look like it's from a single source. It provides built-in windows authentication security however one can write custom security wrapper to fit particular enterprise needs. Remember writing a report for Excel and then knowing that Marketing department requires it in PDF format via email every morning at 10:00 O Clock, webmaster needs it as HTM for publishing it on your corporate intranet and enterprise partners need it in XML to perform an XSLT transformation and show in their business apps? With SQL Server reporting services, multiple delivery methods and formats work like a charm. You design a generic report and reporting server takes care of exporting it into HTML, Excel, PDF, war (web archive), TIFF, CSV and XML format. As always, One can write his own custom format writer wrapper class for any custom format. 
Figure: Reporting Services delivery formats (courtesy Microsoft Corporation)
Reporting Services provide four distinct formats of report delivery also known as subscription in Reporting Services arena; Individual subscription, data driven subscription, SMTP delivery and file share directory (FTP) based subscriptions. Following diagram schematically explains Reporting services architecture 
Figure: Reporting Services system architecture diagram (courtesy Microsoft Corporation)
Being a developer, I found Reporting Services API as another reason of making a move towards SQL server reporting Services. Beside the code segments which can be written within a report in VB.NET, these API provides makes it more programmable. The application program interface can be classified into five categories. - Data processing extension application programming interface (API)
- Delivery extension API
- Rendering extension API
- Security extension API
- Web service API
- Windows Management Instrumentation (WMI) configuration API
Interactive interfaces are another salient feature of SQL Server Reporting Services. Reports designed in SQL server reporting services supports charts, document map, freeform, cross tab matrix, sub reports and tables. Reports can also be parameterized and event driven (supports actions). Management is one of the most important parts in any reporting system; SQL server reporting services has it all planned. It manages jobs from a user friendly console, provides personalization "my reports", tracks report history, manage shared data sources, provide search, subscription and snapshot features with shared subscription from one stop shop, the management console. Reporting Services supports report caching and stores reports execution data in execution logs, Following diagram explains the reporting services architecture in further technical detail 
Figure: Reporting Services architecture diagram (courtesy Microsoft Corporation)
As defined in the diagram above, the process of report generation and publishing consists of the following main steps. - Reporting server engine (Report Processor) receives the request for a particular report. A request includes parameters and formatting instructions.
- Report Processor retrieves the report definition on the basis of request.
- For the corresponding RDL, the report processor then retrieves the report data for specified data sources.
- Report Processor performs transformation on reporting data and sends the document data along with schema to rendering engine (rendering extension).
- The extension publishes the final rendered report.
The following steps are basics of how reporting services work. The extensions (data processing extensions, rendering extensions etc) can be custom built and wrap around the existing set of API to provide extended functionality.
Reporting Services Installation Reporting Services installation is fairly easy providing that you've all the required components installed. Along with a good system, you need to have a local or remote instance of SQL server database with SP3a or later. The SQL server reporting services works as a part of SQL server license. Also if you don't have updated service pack 3a, setup won't continue. 
Figure: SP3a required screen
Edition of SQL server should also correspond to that of reporting services i.e. Enterprise Edition reporting services along with SQL server Enterprise edition, professional along with professional and so forth. Report server can be installed on same as well as different SQL server box. For heavy enterprise reporting processing demands, its most probably recommended to keep the machines apart. Reporting Services Licensing Information
http://www.microsoft.com/sql/reporting/howtobuy/howtolicensers.asp Q. How is SQL Server 2000 Reporting Services licensed? A. Reporting Services is part of the SQL Server 2000 license. There is not a separate license for Reporting Services. If you have a licensed copy of SQL Server 2000, you may run Reporting Services on the same server for no additional license fee. If you want to run Reporting Services on a server that is not licensed for SQL Server 2000, you will need to obtain a SQL Server 2000 license for that additional server. This is the same licensing model used for SQL Server Analysis Services. For more information about licensing scenarios, see the How to License Reporting Services page. Q. Is Reporting Services free? A. No. Reporting Services is part of the SQL Server 2000 license. If you have a licensed copy of SQL Server 2000, you may run Reporting Services on the same server for no additional license fee. If you want to run Reporting Services on a server that is not licensed for SQL Server 2000, you will need to obtain a SQL Server 2000 license for that additional server. This is the same licensing model used for SQL Server Analysis Services. For more information about licensing scenarios, see the How to License Reporting Services page.
Reporting services need .NET framework 1.1, if its not installed, setup will install it for you. ASP.NET 1.1 is also required to be installed
and registered with web server. ASPNET_Regiis utility is mostly a handy solution for registering ASP.NET with web server. 
Figure: ASP.NET installation error screen
Installing SQL Server Reporting Services with Whidbey When installing Reporting Services with .NET framework 2.0.3, you may run into the following error. Error "ASP.NET v1.1 not installed" The following entry from Lance Whiteboard will help http://weblogs.asp.net/lhunt/archive/2004/04/05/107950.aspx
Visual Studio.NET 2003 is additional software required for report designing. Any edition of visual Studio.NET or individual copies of Visual C#, VB.NET or Visual C++ can be used to install report designer. Once all the System Check requirements are fulfilled, Reporting Services installation wizard creates all the virtual directories, set the permissions and provide you the interface to start working with reporting services. SQL Server Reporting Services SP1 Reporting Services service pack 1 is released on 22nd June 2004. It provides various enhancements which are listed follows. - Excel rendering extension has been improved and now supports viewing in Excel 97 and 2000.
- PDF rendering extension is more robust and has better performance.
- Chart control provides more control over display styles.
- References to external URLs (images and resources) from within a report are now supported.
- Data caching behavior for report preview is now supported.
- Newline in expressions is now supported.
- The style of the HTML Viewer toolbar can now be modified through a style sheet.
- New URL parameters offer more options for customizing report presentation at run time
- Report Manager proxy persists authentication cookies so that they can be used by custom security extensions.
- Hidden parameters are now supported.
- Temporary snapshots can be compressed as well as stored on the file system.
- Integrated security support for accessing report data sources can be disabled.
To access Reporting services "Report Manager", you'd require Internet explorer 6.0 or above on your system. You may also want to install the Adventure Works Database to try and study the sample reports provided with reporting services. 
Figure: Feature selection screen of Microsoft SQL server reporting services
Users Credentials, SQL Server 2000 Reporting Services Deliver the Data At Long & Foster Companies, the largest residential real-estate firm serving the Mid-Atlantic region of the United States, SQL Server 2000 Reporting Services has meant the difference between agents getting reports a week or a month after the fact - to wherever, whenever they want. Says Lance Morimoto, a senior manager in the company's e-commerce and software development group, "With Web-based report delivery, data that was once pushed out monthly is now available instantaneously. It's truly real-time reporting.
Some Reporting Services Trivia The First Live .NET Rocks Show EVER was on SQL Server Reporting Services. It's a 2 hours long, and was commenced Friday, January 30, 2004 at 12PM EST, 9AM PST. Carl's with co-host Rory Blyth (of Nepoleon.com), Bill Vaughn and Peter Blackburn talk about SQL Reporting Services, Microsoft's foray into the data reporting market. Archive of this show can be listened from here.
Reporting Services Development Walkthroughs Like quick start tutorials, following walkthroughs are provided by Microsoft - Walkthrough - Creating a Basic Report
- Provides a step-by-step tutorial for creating a report.
- Walkthrough - Adding Grouping, Sorting, and Formatting to a Basic Report
- Provides a step-by-step tutorial for expanding the report created in the first tutorial.
- Walkthrough - Using a Dynamic Query in a Report
- Provides a step-by-step tutorial for using dynamic queries in a report.
- Walkthrough - Creating a Data-Driven Subscription
- Provides step-by-step instruction for defining a subscription that builds a subscriber list from a data store.
- Walkthrough - Accessing the Reporting Services Web Service Using Visual Basic or Visual C#
- Provides step-by-step instructions for accessing the Reporting Services Web Service to retrieve item properties.
- Walkthrough - Generating RDL Using the .NET Framework
- Provides step-by-step instructions for generating Report Definition Language (RDL) programmatically using the .NET Framework.
Programming Reporting Services Without further ado, I think it's about time to show you how to create our first report in SQL Server Reporting Services. Source code can be downloaded from 15seconds website; you'd need Northwind database to run the samples. This is a step by step introduction to create a report. Some of the physical steps are combined to avoid mundane tasks you are already familiar with. Step 1: Start a Business Intelligence Project in Visual Studio.NET You can either use Wizard or Report Project to create a report. We will be using Report Wizard here. 
 Step 2: Create a data source connection string for Northwind database in SQL Server. Create a data source for report to retrieve data from Northwind database. The database shown in the example is local but you can use a remote connection for it as well. (sa empty password is not a good security practice, its for demo purposes only).  Step 3: Design the query to display the data in report. Write or (design in the Query Builder) the SQL query to retrieve data from database into the report. I'm selecting all records from table "orders" for display in the report.  Step 4: Select Report Type & Style This step is to chose the report type (tabular or Matrix) and table style for the report.  Step 5: Select Deployment Location and Review the Report This step is to choose the deployment location i.e. the web server location (default is http://localhost/ReportServer) for report. Finally you can review all your selections and can go back and change them if needed. 
 And voila, finally here is our rendered "Orders report". Within five wizard steps, a formatted data report is created which can be exported in a wide variety of formats, scheduled to be emailed or put on ftp and can be accessed over HTTP like any other webpage and supports built-in security. 
Figure: Orders Report in Report Designer Preview mode in VS.NET 2003 IDE.
Report designer has three different views for a report; Data view, Layout view and Preview. Data view provides the database selection and query writing support for reports so if someone didn't want to use the wizard interface to create and publish reports, can use the data view to create datasets and define custom queries. 
Figure: Orders Report in Data view
Similarly layout view provides the interface to set of tools for creating the elements on a report. Fields can be dragged and dropped from Fields toolbar in layout view and it will populate the table or matrix.will populate 
Figure: Report Designer Toolbox
Figure: Orders Report in Layout View
Expression editor is another useful tool provided with Report Manager. As an enhancement to this basic Orders report, I'm adding a header to it which will show the report generation time, name and number of pages in this report. This meta data is provided as global constants which are added into text boxes in the header pane. Other useful global variables for instance Execution time, Report Server URL etc can be seen in the screenshot below. 
Figure: Report Designer Expression Editor.
However we've seen the report in Preview mode, its time to execute and view it in browser. After pressing F5 (or directly accessing the URI of report server), you'll see an animated gif saying report is being generated. The following report shows the time generated and report name along with pages as defined above using global variables. As you can see in the query string below, it defines the set of parameters from report. These parameters serve a useful purpose as you'll see further in this article.  
Figure: Order Report rendered in IE
The drop down menu shows the export formats available for this report. As discussed earlier, Reporting services can export in HTML, Excel, War, PDF, TIFF, CSV and XML format as well you can define custom format specifier but will have to code wrapper for it.
Report Manager Report Manager is the interface provided by Reporting server to manage reports. Microsoft is promoting web based management consoles as you can see ASP.NET 2.0 is also equipped with a website administration and management console. Using the Report Manager interface, depending on their access privileges, one can upload report files (RDL), set subscriptions to receive reports, create a new data source and add / modify user report access roles.  As shown in figure above, OrdersReport is published by Report Designer in Visual Studio.NET for us. One can also manually upload an RDL file and it will be shown in Report Manager. Upon clicking the Orders Report report manager executes and show the output of report. It provides us four tabs with a report i.e. View, Properties, History and subscription. One can perform a wide variety of tasks with these reporting tabs.  For instance you can setup a new role for an NT group of a single user. There are four roles in general; Browser, Content Manager, My Reports and Publisher as shown in the figure below.  From the history tab, one can set the execution snap shots and further history settings.  Probably the most important tab in report property is Data Source tag. It helps setting up the data source used by the report and impersonation settings as well. Connection type, string, credentials and further details required to execute a report are stored via this interface.  RDL stands for Report Definition Language which is an XML based reporting definition initiative by Microsoft to share the different reporting formats. As defined by Microsoft Report Definition Language (RDL) is an XML-based schema for defining reports. The goal of RDL is to promote the interoperability of commercial reporting products by defining a common schema that allows interchange of report definitions. To encourage interoperability, RDL includes the notion of compliance levels that products may choose to support. http://www.microsoft.com/sql/reporting/techinfo/rdlspec.asp
RDL Specs can be downloaded from the link above. One can manually upload the RDL file using the report manager's general tab and then by update link in the properties as shown in the figure below.  Like code-behind files, the report manager in VS.NET also supports the editing of RDL file generated during the visual exercise performed above. In the figure below, you'd see the query, fields and data source selected in the XML file. 
|
