Business Intelligence with Microsoft SQL Server Reporting Services - Part 1

By Adnan Masood

  • 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

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.

  1. 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.
  2. 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.

Reporting Services delivery formats
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

Reporting Services system architecture diagram
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

Reporting Services architecture diagram
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.

  1. Reporting server engine (Report Processor) receives the request for a particular report. A request includes parameters and formatting instructions.
  2. Report Processor retrieves the report definition on the basis of request.
  3. For the corresponding RDL, the report processor then retrieves the report data for specified data sources.
  4. Report Processor performs transformation on reporting data and sends the document data along with schema to rendering engine (rendering extension).
  5. 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.

SP3a required screen
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.

ASP.NET installation error screen
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.

  1. Excel rendering extension has been improved and now supports viewing in Excel 97 and 2000.
  2. PDF rendering extension is more robust and has better performance.
  3. Chart control provides more control over display styles.
  4. References to external URLs (images and resources) from within a report are now supported.
  5. Data caching behavior for report preview is now supported.
  6. Newline in expressions is now supported.
  7. The style of the HTML Viewer toolbar can now be modified through a style sheet.
  8. New URL parameters offer more options for customizing report presentation at run time
  9. Report Manager proxy persists authentication cookies so that they can be used by custom security extensions.
  10. Hidden parameters are now supported.
  11. Temporary snapshots can be compressed as well as stored on the file system.
  12. 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.

Feature selection screen of Microsoft SQL server 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.

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.

Orders Report in Report Designer Preview mode in VS.NET 2003 IDE
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.

Orders Report in Data view
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

Report Designer Toolbox
Figure: Report Designer Toolbox

Orders Report in Layout View
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.

Report Designer Expression Editor
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.

Order Report rendered in IE
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.



Downloads

Comments

  • GHD hÃ¥r fladjern det bedste valg til at beskytte dit hÃ¥r fra skader

    Posted by pletcherbrk on 06/15/2013 01:06am

    [url=http://glattejernghd.bloguedobebe.com/]Glattejern ghd[/url] Hvis du ikke kan bestemme den model, du ønsker at købe GHD, vil jeg give de særlige kendetegn ved hver model, og at forsøge at løse nogle af de problemer også fortælle dig om hårpleje, såsom den hyppige brug af tørretumblere, krøllejern, board meget underminere det!Endelig vil jeg fortælle dig min personlige erfaring, jeg har brugt og funktion, at give dig en generel idé om bord.Faktum er, at den GHD brand pleje og håret og andre poster udseende forårsagede en revolution på markedet!Hvis vi støtter, de gav denne berømthed, Katy Perry (hvem er billedet af mærket), hvis du ser samarbejdet (serie dukkede op i de kreditter, processioner, foto osv.), GHD billigt, er det ikke der har en sjælden personale mellem væksthormonmangel. Kort sagt, det tidspunkt pladen [url=http://www.glattejernghd.350.com/]glattejern ghd[/url] Hair Booth, en frisørsalon og leverandør af høj kvalitet hår pleje og skønhed mærker, tilbyder nu den ghd Metallic Collection, en limited-edition serie af ghd glattejern produkter, i sin eksklusive katalog af styling-produkter.Pladerne af disse ghd håret i Metallic Collection er smoothened at forhindre hår blokering under styling. Disse plader har også en konturform, så krøller og bølger kan udformes lettere. Derudover Metallic Collection ghd glattejern også kommer med en ekstra lang, 2,7-meter drejelig ledning, der vil give mere frihed i bevægelse, mens styling hår [url=http://ghdglattejerntilbud.webs.com/]ghd glattejern tilbud[/url] GHD glatning strygejern tilbyder forvandlet den faktiske kloden er forbundet med låse som absolut ingen yderligere element før. Hvilket er på grund af denne form for helt nye parabol forsyninger såsom keramik, der har indført dem alle i deres eget splinterny generationsskifte sted. Ikke bare udføre disse mennesker udfører varme langt bedre end standard stål retter, men de desuden fungerer med henblik på at distribuere hvilken varme. Men at besøge faktisk forbi hvordan den GHD element tilbyder desuden skabt den flade jern, som ikke blot tjener til at reducere den faktiske skade, men derudover kæmper de spørgsmål, der er forbundet med frizz som en række andre elementer og også de konventionelle håret udløst. Billige ghd glattejern 1st, skal du sørge for generelt at erhverve kommer fra en godkendt forhandler. Må ikke være tilbageholdende for dig at spørge ejeren bekymringer, herunder i tilfælde et element, der kan være hus til salg, vil være selve formålet i hele dit billede? Kan være denne specifikke en ny helt ny objekt samt anvendt objekt? Ofte tjek din sælgers udtalelser at afgøre, om du finde næsten eventuelle dårlige meninger. Hvis du gør, skal du sørge for at gå gennem netop den udfordring endte med at blive sammen med præcis, hvordan ejeren afgjort der vil noget. Spørge, hvis du finder nogen form for udvidet garanti sammen med netop, hvad hans eller hendes give tilbage forsikring planen kan være. En række eBayers kan også tilføje de oprindelige poster sammen med udvidet garanti data forsikrer, at det skal være en realistisk ghd fladjern.

    Reply
  • VenkatMSBI

    Posted by Venkat G on 08/21/2012 12:06pm

    Excellent

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Live Event Date: November 20, 2014 @ 2:00 p.m. ET / 11:00 a.m. PT Are you wanting to target two or more platforms such as iOS, Android, and/or Windows? You are not alone. 90% of enterprises today are targeting two or more platforms. Attend this eSeminar to discover how mobile app developers can rely on one IDE to create applications across platforms and approaches (web, native, and/or hybrid), saving time, money, and effort and introducing apps to market faster. You'll learn the trade-offs for gaining long …

  • Live Event Date: October 29, 2014 @ 11:00 a.m. ET / 8:00 a.m. PT Are you interested in building a cognitive application using the power of IBM Watson? Need a platform that provides speed and ease for rapidly deploying this application? Join Chris Madison, Watson Solution Architect, as he walks through the process of building a Watson powered application on IBM Bluemix. Chris will talk about the new Watson Services just released on IBM bluemix, but more importantly he will do a step by step cognitive …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds