SQL Server 2000 Reporting Services: Creating a report from scratch

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
• Data Access
• Adding a Report
• Configuring the DataSet
• Configuring the Layout
• Finished Report
• Report in PDF
• Report File Structure
• Conclusions

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.

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:

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.

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.

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:

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.

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.