Programmatically Creating SSRS Report in Microsoft SQL Server 2008

A step-by-step book and eBook to getting the most of Microsoft SQL Server Reporting Services 2008

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.

Copy and paste code

Remove the default code and replace it with the code shown.

Copy code the shown below and paste it to the Form1's code page. The relevant statements are annotated in the code.

Imports System.Data
Imports System.Data.SqlClient
Imports System.IO.FileStream
Imports System.Xml.XmlWriter
Imports System.Text
Public Class Form1
Private conn As New SqlConnection
Private CmdText As String
Private Flds As New ArrayList
Private Const CONSTRG As String = _
"Data Source=HODENTEK2SANGAM;Initial Catalog=TestNorthwind;" & _
"Integrated Security=True"
Sub connection()
If conn.State = ConnectionState.Open Then
conn.Close()
End If
Try
conn.ConnectionString = CONSTRG
conn.Open()
Catch e As System.Data.SqlClient.SqlException
Label2.Text = e.Message.ToString
End Try
End Sub
Sub GetFields()
Dim command As SqlCommand
Dim reader As SqlDataReader
connection()On Programmatically Creating a SSRS Report
' Executing a query to retrieve a fields list for the report
command = conn.CreateCommand()
CmdText = "Select EmployeeID, LastName, " & _
"FirstName, City, Country from Employees"
command.CommandText = CmdText
' Execute and create a reader for the current command
reader = command.ExecuteReader(CommandBehavior.SchemaOnly)
'For each field in the resultset, add the name to an array list
Flds = New ArrayList()
Dim i As Integer
For i = 0 To reader.FieldCount - 1
Flds.Add(reader.GetName(i))
Next i
End Sub
Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
connection()
TextBox2.Text = "Connection Open"
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button2.Click
GetFields()
Dim txt = ""
For I = 0 To Flds.Count - 1
txt = txt + Flds.Item(I) + ","
Next
TextBox1.Text = txt
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button3.Click
connection()
GetFields()
GenerateRdl()
TextBox3.Text = "File created at C:RDLGen2.rdl"
End Sub
Public Sub GenerateRdl()Chapter 10
' Open a new RDL file stream for writing
Dim stream As IO.FileStream
stream = IO.File.OpenWrite("C:RDLGen2.rdl")
'Dim Encoding As System.I
Dim writer As New Xml.XmlTextWriter(stream, Encoding.UTF8)
writer.Flush()
' Causes child elements to be indented
writer.Formatting = Xml.Formatting.Indented
' Report element
writer.WriteProcessingInstruction("xml", _
"version=""1.0"" encoding=""utf-8""")
writer.WriteStartElement("Report")
writer.WriteAttributeString("xmlns", Nothing, _
"http://schemas.microsoft.com/sqlserver/
reporting/2003/10/reportdefinition")
writer.WriteElementString("Width", "6in")
' DataSource element
writer.WriteStartElement("DataSources")
writer.WriteStartElement("DataSource")
writer.WriteAttributeString("Name", Nothing, "DataSource1")
writer.WriteStartElement("ConnectionProperties")
writer.WriteElementString("DataProvider", "SQL")
writer.WriteElementString("ConnectString", CONSTRG)
writer.WriteElementString("IntegratedSecurity", "true")
writer.WriteEndElement() ' ConnectionProperties
writer.WriteEndElement() ' DataSource
writer.WriteEndElement() ' DataSources
'DataSet element
writer.WriteStartElement("DataSets")
writer.WriteStartElement("DataSet")
writer.WriteAttributeString("Name", Nothing, "DataSet1")
' Query element
writer.WriteStartElement("Query")
writer.WriteElementString("DataSourceName", "DataSource1")
writer.WriteElementString("CommandType", "Text")
writer.WriteElementString("CommandText", CmdText)
writer.WriteElementString("Timeout", "30")
writer.WriteEndElement() ' Query
' Fields elements
writer.WriteStartElement("Fields")
Dim fieldName As StringOn Programmatically Creating a SSRS Report
For Each fieldName In Flds
writer.WriteStartElement("Field")
writer.WriteAttributeString("Name", Nothing, fieldName)
writer.WriteElementString("DataField", Nothing,
fieldName)
writer.WriteEndElement() ' Field
Next fieldName
' End previous elements
writer.WriteEndElement() ' Fields
writer.WriteEndElement() ' DataSet
writer.WriteEndElement() ' DataSets
' Body element
writer.WriteStartElement("Body")
writer.WriteElementString("Height", "5in")
' ReportItems element
writer.WriteStartElement("ReportItems")
' Table element
writer.WriteStartElement("Table")
writer.WriteAttributeString("Name", Nothing, "Table1")
'start border width
writer.WriteStartElement("Style")
writer.WriteElementString("BorderWidth", "2pt")
writer.WriteEndElement() ' Style
'end Border width
writer.WriteElementString("DataSetName", "DataSet1")
writer.WriteElementString("Top", ".5in")
writer.WriteElementString("Left", ".5in")
writer.WriteElementString("Height", ".5in")

writer.WriteElementString("Width", _
(Flds.Count * 1.5).ToString() + "in")
' Table Columns
writer.WriteStartElement("TableColumns")
For Each fieldName In Flds
writer.WriteStartElement("TableColumn")
writer.WriteElementString("Width", "1.5in")
writer.WriteEndElement() ' TableColumn
Next fieldName
writer.WriteEndElement() ' TableColumns
' Header RowChapter 10
writer.WriteStartElement("Header")
writer.WriteStartElement("TableRows")
writer.WriteStartElement("TableRow")
writer.WriteElementString("Height", ".25in")
writer.WriteStartElement("TableCells")
For Each fieldName In Flds
writer.WriteStartElement("TableCell")
writer.WriteStartElement("ReportItems")
' Textbox
writer.WriteStartElement("Textbox")
writer.WriteAttributeString("Name", _
Nothing, "Header" + fieldName)
writer.WriteStartElement("Style")
'add the next two lines to add style to header field
writer.WriteElementString("TextDecoration", "Underline")
writer.WriteElementString("FontWeight", "Bold")
writer.WriteEndElement() ' Style
writer.WriteElementString("Top", "0in")
writer.WriteElementString("Left", "0in")
writer.WriteElementString("Height", ".5in")
writer.WriteElementString("Width", "1.5in")
writer.WriteElementString("Value", fieldName)

writer.WriteEndElement() ' Textbox
writer.WriteEndElement() ' ReportItems
writer.WriteEndElement() ' TableCell
Next fieldName
writer.WriteEndElement() ' TableCells
writer.WriteEndElement() ' TableRow
writer.WriteEndElement() ' TableRows
writer.WriteEndElement() ' Header
' Details Row
writer.WriteStartElement("Details")
writer.WriteStartElement("TableRows")
writer.WriteStartElement("TableRow")
writer.WriteElementString("Height", ".25in")
writer.WriteStartElement("TableCells")
For Each fieldName In Flds
writer.WriteStartElement("TableCell")
writer.WriteStartElement("ReportItems")On Programmatically Creating a SSRS Report
' Textbox
writer.WriteStartElement("Textbox")
writer.WriteAttributeString("Name", Nothing, fieldName)
writer.WriteStartElement("Style")
writer.WriteEndElement() ' Style
writer.WriteElementString("Top", "0in")
writer.WriteElementString("Left", "0in")
writer.WriteElementString("Height", ".5in")
writer.WriteElementString("Width", "1.5in")
writer.WriteElementString("Value", _
"=Fields!" + fieldName + ".Value")
'writer.WriteElementString("HideDuplicates", "DataSet1")
writer.WriteEndElement() ' Textbox
writer.WriteEndElement() ' ReportItems
writer.WriteEndElement() ' TableCell
Next fieldName
' End Details element and children
writer.WriteEndElement() ' TableCells
writer.WriteEndElement() ' TableRow
writer.WriteEndElement() ' TableRows
writer.WriteEndElement() ' Details
' End table element and end report definition file
writer.WriteEndElement() ' Table
writer.WriteEndElement() ' ReportItems
writer.WriteEndElement() ' Body
writer.WriteEndElement() ' Report
' Flush the writer and close the stream
writer.Flush()
stream.Close()
End Sub 'GenerateRdl


End Class

Build and execute

Before clicking on Start Debugging, build the project after you make any changes to the code.

Build the project and run the form.

The Report Definition file will be created at the location C:RDLGen2.rdl. If the Generate Report button is clicked multiple times, backup copies of the report file will be created in the C: drive.

Notes on adding style

You may alter the <style/> attributes in the code using the Report Definition API to test custom styling. Some styling was added to the column headers in the code. But the attributes that are listed in the API are the only ones admissible. For example, the styles that are allowed for a textbox are as in the following quoted error message when an attempt was made to use an invalid attribute.

Deserialization failed: The element 'Textbox' in namespace 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefnition' has invalid child element 'FontWeight' in namespace 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefnition'. List of possible elements expected: 'Style, Action, Top, Left, Height, Width, ZIndex, Visibility, ToolTip, Label, LinkToChild, Bookmark, RepeatWith, CustomProperties, Value, CanGrow, CanShrink, HideDuplicates, ToggleImage, UserSort, DataElementName, DataElementOutput, DataElementStyle' in namespace 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefnition' as well as any element in namespace '##other'. Line 82, position 24.

A similar kind of procedure can be used for generating Crystal Reports.

Summary

A programmatic approach to authoring reports is sometimes required. Using an API enables developers to enhance features beyond what are available using built-in tools. SQL Server Reporting Services 2008 uses the strong support provided by the .NET Framework. Generating a very simple report file based on report definition language is described together with an introduction to the XMLTextWriter. A hands-on example is included for practicing with the code.

Books to Consider

comments powered by Disqus