How to use the new reporting service of Microsoft inside your ASP.NET Application

Introduction

Microsoft has introduced a very different and extremely powerful reporting engine called SQL Reporting Services. It is relatively very easy to use reporting services, however it is a bit different from what we are used to in Crystal reports. 

Background

Reporting service is basically a reporting server that uses SQL server as its backend database, all reports are deployed on the reporting server and from their you can access any reports you have access rights to. The basic idea is to have a single location where all reports are deployed, and provides a single point of access; this created a very flexible environment to deploy your reports over the enterprise. The idea is a very similar to Crystal Reports Enterprise Reporting.

Requirements:

You will need the following tools before installing the reporting service, those tools are needed for development of reports for deployment.

   SQL Server 2000 with SP3

   IIS 5.0 or 6.0

  Visual Studio .NET

Accessing Report Server Management Interface:

You can start by accessing your reporting service by going to http://localhost/reports this is where you can manage your reporting service. You can view reports and other information directly from this web interface, manages subscriptions, security, data sources and other.

The Reporting Service Web Management provides browsing folders that contain reports, data source names that you have deployed. This tool provides viewing of reports, however for developing reports you must have Visual Studio .NET

Report server windows service

Report server windows service must be running to be able to access, view and deploy reports from your development tool. (You can see ReportServer window service status through services.msc)

Developing Your Own Reports

Ø  First you create a new project, and select Report Project this will create a reporting service project. From here you will find two folders shared data sources, and reports. Shared data sources is one very interesting feature, this is where your data source for your reports. You can have more than 1 shared data source or even a single data source for every report; however it wouldn't be a good idea to repeat the same data source twice if you are using the same database.

Ø  create a shared data source selecting your SQL server, required database (Northwind)

Ø  create new report

Ø  Selecting the data for your report through query/stored procedure.

Ø  After you are done selecting the data go to, report designer select the layout tab in your report, as you can see in the left toolbox you can use any of the report control to enhance your report functionality and design. You can include charts, images, matrix, etc... After you're done lets preview the report.

Ø  Previewing Report: One of the features I love about the reporting service, is the ability to preview your report before deployment, here you can view your report as if you are in the deployment environment.

Ø  Deploying Report on Report Service: all your reports are developed on Visual Studio .NET then they are deploying to a reporting server. To start deployment right clicks your application and select properties, you will find the property "OverwriteDataSources" to be false, make it to true, and then select the target folder; this can be anything you like. Then enter the location of your reporting server here it is localhost however it can be a domain, IP address or any location you want as long as reporting service is installed to it. After you are done press F5 or right click the

Ø  Project and select deploy, the minute this is done your reports are deployed on your reporting server.

Ø  Viewing Report from Report Service: As I said now your report is deployed on the reporting server you can access it directly by going to http://localhost/reports.

Reporting service Features

Ø  The SQL 2000 Server Reporting Services provide web based interactive reports, and is integrated with the Visual Studio 2003.

Ø  Although you can manipulate report programmatically, no programming is required if you want to use Reporting Services.

Ø  This XML based data reporting is extremely easy to use and supports importing MS Access reports as well.

Ø  Reports is generated *.rdl extension.

Ø  Microsoft SQL Server 2000 Reporting Services is designed with a modular, distributed architecture to help achieve both scalability and flexibility.

Ø  A high performance Server based reporting engine for processing and formatting reports.

Ø  A complete set of tools for creating, managing, and viewing reports.

Ø  Create reports with tables, graphs with data extracted from the database.

Ø  Can contain data from relational and/or multidimensional data sources.

Ø  Reports are viewed over the web.

Ø  Integration with Microsoft products and tools. Reporting Services integrates easily with familiar Microsoft tools such as Visual Studio and applications such as Office and SharePoint Portal Server, without requiring programming and customization.

Ø  Managing reports. Reporting Services includes a web-based tool for managing reports as well as integration with the new SQL Server Management Studio. Administrators can use this interface to organize reports and data sources, schedule report execution and delivery, and track reporting history. Or, an enterprise or ISV can use the Reporting Services Web Services APIs to write customized management tools.

Ø  Securing reports. Reporting Services implements a flexible, role-based security model to protect reports and reporting resources. The product includes extensible interfaces for integrating other security models if desired.

Ø  Delivering reports. You can post reports to a portal, email them to users, or allow users to use the web-based report server to access reports from a folder hierarchy. Navigation, search, and subscription features help users locate and run the reports they need. Personalized subscriptions let them select the rendering format they prefer.

Report Definition Language (RDL) – An Xml schema for representing reports.

What is a Report?

A report is a combination of three kinds of information:

Ø  Data or information on how to obtain the data (queries) as well as the structure of the data.

Ø  Layout or formatting information that describes how the data is presented.

Ø  Properties that the report such as author, parameters, images within the report, etc.

Use of Reporting Services

       We can develop read only reports faster in Reporting Services than ASP.NET.

       Maintenance with RS is easier than ASP.NET, as with most cases you don’t have to write any code.

       Flexibility with groupings and totals is easier. In ASP.NET you would need to iterate through the Dataset, keeping variables with the totals.

       Parameters are built-in. In ASP.NET there is code.

       Drilldown interactivity. In ASP.NET you need to code up a tree view control.

       Users can have reports automatically emailed to them on a schedule.

       Users can export natively to PDF and XLS, plus a variety of other popular formats.

Functionality

ü  This tool is currently in final beta stages but it is already becoming very popular with developers and architects, simply due to its elegant design.

ü  Needless to say, this tool requires SQL Server (not MSDE) to work. This does not mean that your application data should be in SQL Server. You can have your data in any ODBC or OLEDB compliant data source. In fact, you can even write your own custom data source just for reporting purposes, based upon your proprietary data. Most report writers used to store the metadata of the report itself in proprietary files and storage locations. This was a reason for reduced scalability and difficulty in maintaining report files. By using an RDBMS, these issues are effectively managed.

ü  The beauty is that the entire report and data source definition is stored as a simple XML file. This is the file the reporting engine uses to render reports. The elements and attributes required for defining a report format are fully documented. Further, you can even add your custom elements if you want to enrich available functionality. Most report writers available today never provided this functionality.

ü  XML based report definition allows you to directly design reports programmatically and render them. This was very difficult to achieve in currently available report writers.

ü  The default report designer is integrated with Visual Studio .NET so that you can create application and its reports in the same environment.

ü  The report designer eliminates the traditional bands very effectively. It provides three types of elements—Table, Matrix and List. Table is equivalent to the traditional report with header, footer, detail and groups. You can have multiple tables rendering different data arranged side by side!

ü  For each type of reporting element, you have to attach a dataset to it. Dataset is based upon data source.

ü  The matrix is like a pivot table. It has rows, columns and cells containing computed data. Very useful and very easy. I am sure all of you remember how much we have to struggle today to create a simple cross-tab report. Write complex queries, struggle with table formatting and so on. With this new tool, just drag the matrix on the report, specify row, column and data fields and that’s it.

ü  The list is free form data. It can be descriptive and cannot be represented as a structured table, somewhat like a data repeater. Whatever data elements you put in the list are repeated for each row in the base dataset. This allows you to create free form layout reports that still repeat for each data item.

ü  The report items can be recursive. For example, one list can contain another list. What’s more one report can be defined as a sub-report of the base report. This provides more than just drill down. The sub report also need not be hard coded. Parameters can be passed online to it based upon the area of base report clicked.

ü  Now, about rendering. This is the most sophisticated part. By default rendering is in HTML. But while you are viewing the report, you can simply click on the toolbar and render it in many different ways.

ü  The most important part is that all the reports are stored on the central SQL Server database. Usually, we have reports for each application stored and managed separately. This leads to a lot of confusion and administrative headaches.

ü  The reports are viewed and administered by using a Web-based implementation of the entire reporting engine. The default website provides a base structure which is folder based. Typically you will have folders created for each application or user functionality.

ü  How do you access reports usually? By instancing the report writer runtime. Here you don’t have to do that. Because all reports are accessible in one of the two ways:

-      By specifying the URL identifying the report on the report server or
- By calling the Web service.

ü  The best part of the reporting server is that the entire functionality is exposed as a single Web service! You can create, manage, and view reports using various methods provided by the web service.

ü  The security is managed in a role-based manner and can be applied to folders as well as reports.

ü  User can manage their own reporting needs by accessing reports ad-hoc or by subscribing to the reports. Subscription based reports are automatically sent by mail to the users.

ü  All reports require parameters to be accepted from users. Here once parameters are defined, the UI for these parameters is automatically generated.

ü  Finally, you have many ways of rendering the reports:

ü  HTML (MHTML)

ü  Excel

ü  Acrobat

ü  Tiff (image)

ü  XML

ü  CSV

ü  And of course, you can write your own rendering mechanism, if you like!

ü  Depending upon the type of output you want, some features are disabled. For example, HTML reports do not support pagination. Whereas, XML and CSV will not support drilldown. This is primarily due to the nature of the output format and not due to inadequate rendering capabilities of the reporting services.

Scripting

Another nice feature available here is scripting. There is a command-line tool called RS.EXE. You can create scripts to manage batch processing of reports or programmatically manipulate any exposed functionality of reporting service. The script is written in a simple text editor and the file is labeled as .RSS. The script is not written in VB SCRIPT. It is written in VB.NET syntax. You don’t have to declare any object, import any namespaces.

Just assume all that is already done and a reference to the entire functionality of the reporting server web service is available to you through a pre-created object called RS. Now you simply write VB.NET code, save the file and run it with RS.EXE with relevant parameters.

No compilation, no assemblies, no deployment, it simply works. Another brilliant way to ensure that in the excitement of great UI, services based architecture and web based functionality, the good old scripting was also supported. Something to learn here about application design and enrichment.

Caching

The entire code of the reporting engine is written in .NET. Therefore, for reports based upon data that changes infrequently, it supports caching. Further it also supports snapshot based pre-execution of reports. For example, there are some reports that are periodically generated by default and viewed by users whenever required. End of day, month, quarter type of reports are typically time consuming to create. Therefore, reporting services supports pre-execution of these reports and saves them as snapshots. These snapshots can be seen by users and quickly rendered. Thus you will typically have snapshots for each month stored throughout the year for quick reference.

Key Reporting Scenarios

Reporting Services combines a single, complete reporting platform with a scalable and extensible

architecture to meet a wide variety of reporting needs.

·         Enterprise reporting. Enterprises can use Reporting Services for their operational reporting or business intelligence applications. Using Reporting Services, corporate IT staff can design a variety of reports and deploy them to individuals throughout the enterprise, using a combination of e-mail distribution and publishing on a corporate portal.

·         Ad Hoc reporting. SQL Server 2005 Reporting Services includes Report Builder, a new ad hoc reporting tool that enables business users to create their own reports and explore corporate data. Report Builder incorporates a user-friendly business query model that enables users to build reports without deep technical understanding of the underlying data sources.

·         Embedded reporting. Independent Software Vendors (ISVs) can use Reporting Services to deliver pre-defined or ad hoc reports as part of a packaged application. The customer's IT organization can access these reports as-is, customize reports or create new ones for specific business needs.

·         Web-based reporting for partners/customers. Organizations can deploy interactive web-based reports to deliver information to customers or partners over extranets of the Internet. Reporting Services isolates report consumers from the complexity of the underlying data sources, while providing personalization and interactivity.