Click here to Skip to main content
15,867,939 members
Articles / Web Development / ASP.NET
Article

ASP.NET and SQL 2000 Reporting Services

Rate me:
Please Sign up or sign in to vote.
4.33/5 (11 votes)
9 Aug 2004CPOL3 min read 164.5K   40   23
Walkthrough of generating a report from a SQL 2000 Server using the 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.

Image 1

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

Image 2

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

Image 3

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.

Image 4

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.

Image 5

You may click and test the connection.

Image 6

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

Image 7

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.

Image 8

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.

Image 9

Image 10

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.

Image 11

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.

Image 12

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.

Image 13

Image 14

Image 15

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.

Image 16

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Technical Writer Hodentek
United States United States
Worked in the area of electrical discharges, high energy lasers, high voltage technology, plasma technology, lithography, thin film plastics, superconducting thin films, diamond thin films, electron accelerators, and free electron lasers for several years. Mentored/guided MS and PhD students at several universities in USA, Brazil, Australia, and India.
Reading books and photography are my hobbies.

Also trained workforce clients with legacy computer skills in web related technologies.

I recently authored a beginner level book on MS SQL Server Integration Services. Details available at the following link:

http://www.packtpub.com/sql-server-integration-services-visual-studio-2005/book

My second book was released in 2008
Learn SQL Server Reporting Services 2008

Get book details at the following site:
http://www.packtpub.com/learning-sql-server-2008-reporting-services/book

This is for anyone who is interested in Reporting Services a la Microsoft. It has over 50 hands-on exercises and covers all aspects of Reporting Services.

Recent new books:

Microsoft SQL Azure Enterprise Application Development 2010
-A Comprehensive book on SQL Azure

Microsoft Visual Studio LightSwitch Business Application Development 2011
A step-by-step approach that is sure to work

Learning SQL Server Reporting Services 2012 Packt Publishers, ISBN: 978-1-84968-992-2 , 2013

Comments and Discussions

 
QuestionUsing Crystal Reprot 10 within MS Access 2003 Pin
aftab8318-Jul-09 0:40
aftab8318-Jul-09 0:40 
AnswerRe: Using Crystal Reprot 10 within MS Access 2003 Pin
mysorian18-Jul-09 6:54
professionalmysorian18-Jul-09 6:54 
GeneralIs it possible to use visual studio 2005 Pin
BarryHolleran14-Jun-07 23:45
BarryHolleran14-Jun-07 23:45 
GeneralRe: Is it possible to use visual studio 2005 Pin
mysorian15-Jun-07 4:07
professionalmysorian15-Jun-07 4:07 
GeneralRe: Is it possible to use visual studio 2005 Pin
BarryHolleran15-Jun-07 5:15
BarryHolleran15-Jun-07 5:15 
GeneralRe: Is it possible to use visual studio 2005 Pin
mysorian15-Jun-07 7:57
professionalmysorian15-Jun-07 7:57 
GeneralRe: Is it possible to use visual studio 2005 Pin
BarryHolleran15-Jun-07 8:03
BarryHolleran15-Jun-07 8:03 
GeneralRe: Is it possible to use visual studio 2005 Pin
mysorian15-Jun-07 9:22
professionalmysorian15-Jun-07 9:22 
GeneralRe: Is it possible to use visual studio 2005 Pin
BarryHolleran15-Jun-07 9:32
BarryHolleran15-Jun-07 9:32 
GeneralRe: Is it possible to use visual studio 2005 Pin
BarryHolleran15-Jun-07 9:38
BarryHolleran15-Jun-07 9:38 
GeneralBusiness Intelligence Project didn't show in .Net 2003 Pin
Member 349999822-Mar-07 2:10
Member 349999822-Mar-07 2:10 
GeneralRe: Business Intelligence Project didn't show in .Net 2003 Pin
RZOE22-May-07 10:32
RZOE22-May-07 10:32 
GeneralBusiness Intelligence Project didn't show in .Net 2003 Pin
Member 349999822-Mar-07 2:10
Member 349999822-Mar-07 2:10 
QuestionProblems with Windows Authentication Pin
brandy2315-Feb-06 22:48
brandy2315-Feb-06 22:48 
AnswerRe: Problems with Windows Authentication Pin
mysorian16-Feb-06 3:13
professionalmysorian16-Feb-06 3:13 
Generalcalling reports in the same frame. Pin
SQL reporting services user28-Aug-05 20:13
sussSQL reporting services user28-Aug-05 20:13 
Generalhelp plz Pin
jimmyhenry17-Aug-05 8:14
jimmyhenry17-Aug-05 8:14 
GeneralRe: help plz Pin
Anonymous17-Aug-05 9:33
Anonymous17-Aug-05 9:33 
GeneralCreate your Report (RDL) programmatically Pin
Anonymous28-Feb-05 8:12
Anonymous28-Feb-05 8:12 
GeneralNames problem during deployment Pin
Anonymous28-Dec-04 23:15
Anonymous28-Dec-04 23:15 
GeneralRe: Names problem during deployment Pin
Anonymous29-Dec-04 3:34
Anonymous29-Dec-04 3:34 
QuestionHow can I install Microsoft Business Intelligence Framework? Pin
Anonymous8-Sep-04 21:43
Anonymous8-Sep-04 21:43 
AnswerRe: How can I install Microsoft Business Intelligence Framework? Pin
mysorian9-Sep-04 2:53
professionalmysorian9-Sep-04 2:53 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.