Thursday, January 10, 2008

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.

No comments: