Programmatically Creating SSRS Report in Microsoft SQL Server 2008

0
146
3 min read

Introduction

In order to design the MS SQL Server Reporting Services report programmatically you need to understand what goes into a report. We will start with a simple report shown in the next figure:

Learning SQL Server 2008 Reporting Services

The above tabular report gets its data from the SQL Server database TestNorthwind using the query shown below:

Select EmployeeID, LastName, FirstName, City, Country from Employees.

A report is based completely on a Report Definition file, a file in XML format. The file consists of information about the data connection, the datasource in which a dataset is defined, and the layout information together with the data bindings to the report.

In the following, we will be referring to the Report Server file called RDLGenSimple.rdl. This is a file written in Report Definition Language in XML Syntax. The next figure shows this file opened as an XML file with the significant nodes collapsed. Note the namespace references.

Learning SQL Server 2008 Reporting Services

The significant items are the following:

  • The XML Processing instructions
  • The root element of the report collapsed and contained in the root element are:
    • The DataSources
    • Datasets
    • Contained in the body are the ReportItems
    • This is followed by the Page containing the PageHeader and PageFooter items

In order to generate a RDL file of the above type the XMLTextWriter class will be used in Visual Studio 2008. In some of the hands-on you have seen how to connect to the SQL Server programmatically as well as how to retrieve data using the ADO.NET objects. This is precisely what you will be doing in this hands-on exercise.

The XMLTextWriter Class

In order to review the properties of the XMLTextWriter you need to add a reference to the project (or web site) indicating this item. This is carried out by right-clicking the Project (or Website) | Add Reference… and then choosing SYSTEM.XML (http://msdn.microsoft.com/en-us/library/system.xml.aspx) in the Add Reference window.

After adding the reference, the ObjectBrowser can be used to look at the details of this class as shown in the next figure. You can access this from View | Object Browser, or by clicking the F2 key with your VS 2008 IDE open. A formal description of this can be found at the bottom of the next figure. The XMLTextWriter takes care of all the elements found in the XML DOM model (see for example, http://www.devarticles.com/c/a/XML/Roaming-through-XMLDOM-An-AJAX-Prerequisite).

Learning SQL Server 2008 Reporting Services

Hands-on exercise: Generating a Report Definition Language file using Visual Studio 2008

In this hands-on, you will be generating a server report that will display the report shown in the first figure. The coding you will be using is adopted from this article (http://technet.microsoft.com/en-us/library/ms167274.aspx) available  at Microsoft TechNet (http://technet.microsoft.com/en-us/sqlserver/default.aspx).

Follow on

In this section, you will create a project and add a reference. You add code to the page that is executed by the button click events. The code is scripted and is not generated by any tool.

Create project and add reference

You will create a Visual Studio 2008 Windows Forms Application and add controls to create a simple user interface for testing the code.

  1. Create a Windows Forms Application project in Visual Studio 2008 from File | New | Project… by providing a name. Herein, it is called RDLGen2.
  2. Drag-and-drop two labels, three buttons and two text boxes onto the form  as shown:
  3. Learning SQL Server 2008 Reporting Services

    When the Test Connection button Button1 in the code is clicked, a connection to the TestNorthwind database will be made. When the button is clicked, the code in the procedure Connection () is executed. If there are any errors, they will show up in the label at the bottom. When the Get list of Fields button Button2 in the code is clicked, the Query will be run against the database and the retrieved field list will be shown in the adjoining textbox. The Generate a RDL file button Button 3 in the code, creates a report file at the location indicated in the code.

LEAVE A REPLY

Please enter your comment!
Please enter your name here