Click here to Skip to main content
15,867,594 members
Articles / Web Development / HTML
Article

SQL Server 2000 Reporting Services: Creating a report from scratch

Rate me:
Please Sign up or sign in to vote.
4.89/5 (12 votes)
10 Sep 2004CPOL6 min read 326.2K   64   72
A step-by-step approach to creating a report from scratch using the SQl Server 2000 Reporting Services.

Introduction

In an earlier posting, the creation of a report using the RAD capability of the Visual Studio 2003 interface and the SQL 2000 Reporting Services was described. In this 'How to' posting, a report generation from scratch using these tools will be described. The data for the report created in this posting will be retrieved from a Biblio database on a MSDE server [XPHTEK\TEST]. MSDE also called the Desktop SQL Server is a smaller version of the SQL 2000 Server with not all the functionalities of the full [Professional, Enterprise] versions. The Report Services creates a DataSet object using the configuration details provided by the designer. The DataSet is the data source for the report. The layout of the report follows the, by now popular, banded style. If one is familiar with Access, or Crystal Reports, the layout follows a similar pattern. In what follows, a step-by-step, screen-by-screen description is given for creating a report from scratch.

Contents

Generating a report

Create a report project called SQLRepSvc along the lines described in the previous posting. This comes with a Shared Data Resources and a Reports folder as shown. Right click on the Reports folder, and from the pop-up menu and submenu, choose to Add New Item.

Data Access

Image 1

This kicks up the Add New Item - SqlRepSvc window. Here, choose to add the Data Source by highlighting and clicking open.

Image 2

This pops-up the, Data Link Properties window. In the present example, the server chosen is an MSDE SQL Server instance called XPHTEK\Test, the database chosen is Biblio with Windows authentication. The connection may be tested.

Image 3

When you click OK to the above window, a data source object called Biblio.rds will be added to the Shared Data Sources folder as seen here:

Image 4

If you right click this folder, you can choose to review its properties as shown here. You may edit this if you need to make changes.

Image 5

Adding a report

Right click on the Reports folder in the Solution Explorer and pick up Add New Report. This adds the report with default name Report1.rdl to the Reports folder as shown. The report addition adds the three tabs, Data, Layout, and Preview to the report designer plane. The default name was renamed as scratch.rdl as shown in the next succeeding screen shot:

Image 6

Configuring the Dataset

In the drop-down box right next to Dataset: in the Data tab, click on the ellipsis and choose <New Dataset..> as shown below:

Image 7

This brings up the Dataset window as shown with focus on the Query tab. You have choice of three command types, Text, Stored Procedures, and TableDirect. In this example, the Text option has been chosen.

Image 8

In the Data tab of the designer, you can choose to edit a DataSet, or create a query to use in the report, by appropriately clicking at the indicated points in this next screen shot. You may be wondering how come Dataset1, became Dataset3 all of a sudden. This screen with DataSet3 was taken on a separate occasion to highlight the points that needs to be clicked.

Image 9

The next screen shows the Query Builder for creating the DataSet. When the query builder shows up for the first time, all the different regions of the designer are empty. You can add a table from the database to the query plane and fashion the query the way you want as shown. The previous posting discusses this in some detail. In the present example, the Publishers table has been added and the query shown in the middle of the designer has been created. The SQL syntax can be checked as well as the query can be run to verify the results.

Image 10

Next, moving on to the next tab, Fields will bring up a tabbed pane which is empty. Here, you may indicate the fields in the DataSet as shown. You may have to type-in the Field Name column; the Value can be picked up by clicking the available options in the Value column. You may also go and explore the other tabs, but for this example, it suffices to just pick the fields.

Image 11

When you click away OK to the above screen, you will see the following. You have an additional Fields window, showing all the fields you have chosen. The query is also shown below the DataSet as shown here:

Image 12

Right clicking on any of the field items in the Fields collection brings up a pop-up menu which can be used to Add, Edit, or Delete a field as shown here:

Image 13

Configuring the layout

The next item to configure is the report layout. Click on the tab Layout, to open the design plane as shown in this screen. Click on the point shown in this screen shot to open the tabbed, Report Properties page as shown on the next screen:

Image 14

The information filled in the General tab will be useful in describing the authorship and report description and, the layout provides the basis for the size of the printed report. It should be possible to make the choice of the language as well.

Image 15

Graphic images can be brought into the report using the Add Existing Item pop-up. This is shown in the next screen shot. The graphic can be dragged and dropped into the design plane. An example of dragging the image to the PageHeader band of the report is shown here. A text box from the Report Items tabbed menu of the Toolbox has been added below the graphic by dragging and dropping.

Image 16

This screen shows the Body of the report in addition to the PageHeader. A Table element from the Report Items tabbed menu from Toolbox is dragged and dropped into the body of the report. The table has its own Header, Details, and Footer rows. Into the header row, report column headings are inserted by first placing text boxes from the Report Items and typing in the textboxes, the column headings. Into the details sections (row), corresponding elements from the Fields List are dragged and dropped (notice the bang operator). Into the footer section, a Globals!ExecutionTime property of the global node shown in the previous screen shot is inserted. The Table Design has excellent support for table design in terms of merging cells, etc. What is perhaps lacking is configuring the row color of alternating rows inside the Details section that would have improved the visual effect.

Image 17

Finished Report

By clicking the Preview tab, the report can be viewed as it would appear in print. The preview will be available only after processing the report has completed, which requires a definite amount of time. Errors at this stage may vary from data feed errors to GUI errors.

Report in PDF

The report can be printed to file in a number of formats including PDF, as shown in this screen:

Image 18

A zoomed-in copy of the report in PDF printed from ©Acrobat Reader 6 is shown in this screen:

Image 19

Report File Structure

The reports generated by the Report Services are XML based, and this next screen shot shows the XML rendering of the scratch.rdl file in IE browser. Certain sections have not been fully expanded to keep the graphic small.

Image 20

Conclusions

Report generation using this tool can be highly productive, if the report developer is conversant with Microsoft's other products and SQL syntax. The report layout design is excellent with several enhancements and support for languages, Unicode bidirectional language support, and rich in other formatting [I am not a Microsoft Vendor!] enhancements. It will be nice to see how it works out given that Crystal Reports was always bundled with VB offsprings.

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

 
GeneralNeed a help Pin
T. Ravindran17-Jun-08 15:20
T. Ravindran17-Jun-08 15:20 
GeneralRe: Need a help Pin
mysorian18-Aug-08 17:24
professionalmysorian18-Aug-08 17:24 
GeneralCalling Report from Windows Application Pin
Joe_Tron10-Jul-07 9:16
Joe_Tron10-Jul-07 9:16 
Now that I can create a report using the above guideline, how do I call this report from my windows application?

I know VS 2005 has a report viewer windows control but I am using VS 2003. How can I use SSRS 2000 to print my report from my application using VS2003?

GeneralRe: Calling Report from Windows Application Pin
mysorian10-Jul-07 9:32
professionalmysorian10-Jul-07 9:32 
GeneralThanks a lot!! Pin
Domingo M. Asuncion10-Mar-07 21:16
Domingo M. Asuncion10-Mar-07 21:16 
GeneralRe: Thanks a lot!! Pin
mysorian11-Mar-07 3:15
professionalmysorian11-Mar-07 3:15 
GeneralRe: Thanks a lot!! Pin
Domingo M. Asuncion1-Nov-07 22:34
Domingo M. Asuncion1-Nov-07 22:34 
GeneralRe: Thanks a lot!! Pin
mysorian2-Nov-07 9:32
professionalmysorian2-Nov-07 9:32 
GeneralRe: Thanks a lot!! Pin
Domingo M. Asuncion19-May-08 1:53
Domingo M. Asuncion19-May-08 1:53 
GeneralRe: Thanks a lot!! Pin
mysorian19-May-08 6:08
professionalmysorian19-May-08 6:08 
GeneralRe: Thanks a lot!! Pin
Domingo M. Asuncion20-May-08 16:14
Domingo M. Asuncion20-May-08 16:14 
Questionconversion of seconds to hh:mm:ss format Pin
MadhaviPendyala22-Feb-07 0:54
MadhaviPendyala22-Feb-07 0:54 
QuestionError Deploying Report From VS 2003 Pin
bburke2323-Jan-07 5:51
bburke2323-Jan-07 5:51 
AnswerRe: Error Deploying Report From VS 2003 Pin
mysorian23-Jan-07 6:28
professionalmysorian23-Jan-07 6:28 
GeneralRe: Error Deploying Report From VS 2003 Pin
bburke2323-Jan-07 6:55
bburke2323-Jan-07 6:55 
GeneralRe: Error Deploying Report From VS 2003 Pin
mysorian23-Jan-07 7:02
professionalmysorian23-Jan-07 7:02 
GeneralRe: Error Deploying Report From VS 2003 Pin
bburke2323-Jan-07 7:04
bburke2323-Jan-07 7:04 
GeneralDynamic scale in charts Pin
MadhaviPendyala23-Jan-07 0:42
MadhaviPendyala23-Jan-07 0:42 
GeneralRe: Dynamic scale in charts Pin
mysorian23-Jan-07 3:15
professionalmysorian23-Jan-07 3:15 
GeneralRe: Dynamic scale in charts Pin
MadhaviPendyala23-Jan-07 20:02
MadhaviPendyala23-Jan-07 20:02 
GeneralUrgent Pin
Member 192833729-Dec-06 6:35
Member 192833729-Dec-06 6:35 
GeneralRe: Urgent Pin
mysorian30-Dec-06 3:30
professionalmysorian30-Dec-06 3:30 
QuestionGeneration of report in pdf format and file to be saved Pin
MadhaviPendyala6-Dec-06 18:47
MadhaviPendyala6-Dec-06 18:47 
AnswerRe: Generation of report in pdf format and file to be saved Pin
mysorian7-Dec-06 3:59
professionalmysorian7-Dec-06 3:59 
GeneralRe: Generation of report in pdf format and file to be saved Pin
MadhaviPendyala12-Dec-06 23:49
MadhaviPendyala12-Dec-06 23:49 

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.