ASP.NET and SQL 2000 Reporting Services

SQL 2000 Reporting Services

The SQL 2000 Server Reporting Services is server based for comprehensive reporting applications. This service can deliver not only hardcopy reports but can also provide web based interactive reports, and is integrated with the Visual Studio 2003. This service forms part of the Microsoft Business Intelligence Framework. This XML based data reporting is extremely easy to use and supports importing MS Access reports as well. Sitting along with Crystal Reports, there are now two reporting options from Visual Studio, albeit this service is more specific to reporting from Microsoft data sources. The next several screen shots show the extreme ease with which reports can be generated, piping in data from a SQL 2000 Server (MSDE is also OK).

Create a new Project, and in the Project Types tabbed window, choose Business Intelligence Projects, and click on Report Project icon, and if you like, choose a name for the project as shown in the next picture.

This slide generally shows how the Solution Explorer would look like. Reports generated in this IDE have a *.rdl extension.

Your choosing a Report, or right clicking the Reports folder and choosing Add a report, opens up the Wizard as shown in this picture.

Clicking Next will take you to the next screen. Here you can give a name of your choice, for the Data Source. If the MS SQL Server is installed, you should see the item in the drop-down box. Clicking 'Next' will take you to the screen where you make a connection with the database.

This is a familiar screen where you choose the database. First, click 'Refresh', then click on the drop-down to choose the database server. There could be more than one server in the server group. In the next step, choose the method of authentication, a Windows authentication is used in the next slide. In the third step, choose a database from among the different databases on the chosen server, as shown in the next slide. The choices are, Server:XPHTEK, Authentication:Windows Integrated, and the Database Name;Food Mart.

You may click and test the connection.

When you click 'OK' to the above finally, you see a summary view of the data connection you made.

In this step, you are going to invoke the Query Analyser to create the type of query you want to use to get the data for the report. Clicking on 'Edit' opens up the next screen where you add tables, fashion the query, and go on to the next step. You click an empty area at the top of this screen to bring up the 'Add table' dialogue and choose the table you want. Here, the 'customer' table is chosen. Next, you make your selection, sort order, and any filtering of data you want to make.

This screen and the next shows the selection, sorting, and filtering. The SQL statement is also shown in the next screen. It is also possible to run the query to verify the results you would get.

In the next step, you choose the type of report [layout] you want to generate. The default is taken since this is not a cross-tab, or OLAP type of report.

Well, finally you have successfully created a report. With experience, this is a very fast way of generating a report, thanks to the Wizard. The report details are shown in this slide.

Clicking on 'Report1.rdl' tab in the Visual Studio IDE brings up three tabbed panes marked, 'Data', 'Layout', and 'Preview'. The next three screens show the three panes.

Finally, the Report Project's property page is shown here wherein you can make further customization as to deployment, etc. The report generation is completely XML based which makes it extremely convenient to send over the wire.