Click here to Skip to main content
Click here to Skip to main content

SQL Server 2000 Reporting Services: Creating a report from scratch

, 10 Sep 2004
Rate this:
Please Sign up or sign in to vote.
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

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

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.

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:

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.

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:

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:

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.

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.

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.

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.

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:

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:

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:

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.

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.

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.

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:

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

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.

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)

Share

About the Author

mysorian
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
Follow on   Twitter   LinkedIn

Comments and Discussions

 
GeneralNeed a help PinmemberT. Ravindran17-Jun-08 15:20 
GeneralRe: Need a help Pinmembermysorian18-Aug-08 17:24 
GeneralCalling Report from Windows Application PinmemberJoe_Tron10-Jul-07 9:16 
GeneralRe: Calling Report from Windows Application Pinmembermysorian10-Jul-07 9:32 
GeneralThanks a lot!! PinmemberDomingo M. Asuncion10-Mar-07 21:16 
GeneralRe: Thanks a lot!! Pinmembermysorian11-Mar-07 3:15 
GeneralRe: Thanks a lot!! PinmemberDomingo M. Asuncion1-Nov-07 22:34 
GeneralRe: Thanks a lot!! Pinmembermysorian2-Nov-07 9:32 
GeneralRe: Thanks a lot!! PinmemberDomingo M. Asuncion19-May-08 1:53 
GeneralRe: Thanks a lot!! Pinmembermysorian19-May-08 6:08 
GeneralRe: Thanks a lot!! PinmemberDomingo M. Asuncion20-May-08 16:14 
Questionconversion of seconds to hh:mm:ss format PinmemberMadhaviPendyala22-Feb-07 0:54 
QuestionError Deploying Report From VS 2003 Pinmemberbburke2323-Jan-07 5:51 
AnswerRe: Error Deploying Report From VS 2003 Pinmembermysorian23-Jan-07 6:28 
GeneralRe: Error Deploying Report From VS 2003 Pinmemberbburke2323-Jan-07 6:55 
GeneralRe: Error Deploying Report From VS 2003 Pinmembermysorian23-Jan-07 7:02 
GeneralRe: Error Deploying Report From VS 2003 Pinmemberbburke2323-Jan-07 7:04 
GeneralDynamic scale in charts PinmemberMadhaviPendyala23-Jan-07 0:42 
GeneralRe: Dynamic scale in charts Pinmembermysorian23-Jan-07 3:15 
GeneralRe: Dynamic scale in charts PinmemberMadhaviPendyala23-Jan-07 20:02 
GeneralUrgent PinmemberMember #192833729-Dec-06 6:35 
GeneralRe: Urgent Pinmembermysorian30-Dec-06 3:30 
QuestionGeneration of report in pdf format and file to be saved PinmemberMadhaviPendyala6-Dec-06 18:47 
AnswerRe: Generation of report in pdf format and file to be saved Pinmembermysorian7-Dec-06 3:59 
GeneralRe: Generation of report in pdf format and file to be saved PinmemberMadhaviPendyala12-Dec-06 23:49 
GeneralRe: Generation of report in pdf format and file to be saved Pinmembermysorian13-Dec-06 5:46 
GeneralCalling SQL Report through ASP.Net 2.0 Application PinmemberDineshSharma7-Oct-06 1:48 
GeneralRe: Calling SQL Report through ASP.Net 2.0 Application Pinmembermysorian7-Oct-06 2:11 
GeneralNew to Reporting Services PinmemberKodanda Pani19-Jun-06 19:33 
GeneralRe: New to Reporting Services Pinmembermysorian20-Jun-06 3:54 
GeneralRS2000-Get the Total Value Pinmembersiewhui21-May-06 21:42 
GeneralRS2000 - CountDistinct in Report Pinmembersiewhui4-May-06 22:07 
GeneralRe: RS2000 - CountDistinct in Report Pinmembermysorian5-May-06 0:58 
GeneralRe: RS2000 - CountDistinct in Report Pinmembersiewhui7-May-06 16:09 
GeneralRe: RS2000 - CountDistinct in Report Pinmembermysorian8-May-06 0:58 
GeneralRe: RS2000 - CountDistinct in Report Pinmembersiewhui8-May-06 16:54 
GeneralRe: RS2000 - CountDistinct in Report Pinmembermysorian8-May-06 17:00 
GeneralRe: RS2000 - CountDistinct in Report Pinmembersiewhui8-May-06 22:51 
GeneralRe: RS2000 - CountDistinct in Report Pinmembermysorian9-May-06 0:18 
GeneralRe: RS2000 - CountDistinct in Report Pinmembermysorian9-May-06 1:46 
Generaldynamic header in sql server 2005 reporting service Pinmemberakashverma011-May-06 22:47 
GeneralRe: dynamic header in sql server 2005 reporting service Pinmembermysorian2-May-06 2:06 
GeneralRe: dynamic header in sql server 2005 reporting service PinmemberGasparDax7-Jun-06 0:03 
GeneralRe: dynamic header in sql server 2005 reporting service Pinmembermysorian7-Jun-06 3:06 
GeneralRe: dynamic header in sql server 2005 reporting service PinmemberGasparDax7-Jun-06 18:06 
GeneralDiplay field containing data in HTML format Pinmembersiewhui27-Apr-06 21:58 
GeneralRe: Diplay field containing data in HTML format Pinmembermysorian28-Apr-06 3:56 
GeneralRe: Diplay field containing data in HTML format Pinmembersiewhui28-Apr-06 20:44 
QuestionRemoving export feature from report Pinmemberjim_cool7-Dec-05 19:13 
AnswerRe: Removing export feature from report Pinmembermysorian8-Dec-05 3:06 

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

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

| Advertise | Privacy | Mobile
Web04 | 2.8.140827.1 | Last Updated 10 Sep 2004
Article Copyright 2004 by mysorian
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid