Programmatically Creating SSRS Report in Microsoft SQL Server 2008

Exclusive offer: get 50% off this eBook here
Learning SQL Server 2008 Reporting Services

Learning SQL Server 2008 Reporting Services — Save 50%

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

$29.99    $15.00
by Jayaram Krishnaswamy | April 2009 | Microsoft

In this article by Dr. Jayaram Krishnaswamy, the process of programmatically creating the SQL Server Reporting Services (SSRS) tabular report is described. You will be creating a very simple report using the provided code. The approach is to introduce the programming by creating the three parts of a report: connection, dataset, and layout.

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.

Learning SQL Server 2008 Reporting Services A step-by-step book and eBook to getting the most of Microsoft SQL Server Reporting Services 2008
Published: March 2009
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:

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.

Learning SQL Server 2008 Reporting Services A step-by-step book and eBook to getting the most of Microsoft SQL Server Reporting Services 2008
Published: March 2009
eBook Price: $29.99
Book Price: $49.99
See more
Select your format and quantity:

About the Author :


Jayaram Krishnaswamy

Jayaram Krishnaswamy studied at the Indian Institute of Science in Bangalore India and Madras University in India and taught at the Indian Institute of Technology in Madras. He went to Japan on a Japanese Ministry of Education Research scholarship to complete his PhD in Electrical Engineering from Nagoya University. He was a Post-Doctoral Fellow at Sydney University in Australia; a Government of India Senior Scientific Officer at the Indian Institute of Science in Bangalore and Indian Institute of Technology at Kanpur; a Visiting Scientist at the Eindhoven Institute of Technology in the Netherlands; a visiting Professor of Physics at the Federal University in Brazil; an Associate Research Scientist at a government laboratory in São Jose dos Campos in São Paulo, Brazil; a visiting scientist at the National Research Council in Ottawa, Canada before coming to USA in 1985. He has also taught and worked at the Colorado State University in Fort Collins and North Carolina State University in Raleigh, North Carolina. He worked with Northrop Grumman Corporation on a number of projects related to high energy electron accelerators and Free Electron Lasers. These projects were undertaken at the Brookhaven National Laboratory in Long Island and in the Physics Department at Princeton University. He has over 80 publications in refereed and non-refereed publications and 8 issued patents. He is fluent in Japanese and Portuguese and lives in Honolulu, Hawaii, USA.

He has been working in IT-related fields since 1997. He was once a Microsoft Certified Trainer in Networking and a Siebel Certified developer. He has worked with several IT related companies, such as the Butler International in their Siebel practice, with several IBM sub-contractors and smaller companies. Presently he is active in writing technical articles in the IT field to many online sites such as http://CodeProject.com, http://APSFree.com, http://DevShed.com, http://DevArticles.com, http://OfficeUsers.org, http://ASPAlliance.com, Egghead Café, http://SSWUG.org, Packt Article Network, http://databasedev.co.uk, http://cimaware.com, and many others. Between 2006 and 2010 he wrote more than 400 articles mostly related to database and web related technologies covering Microsoft, Oracle, Sybase, ColdFusion, Sun, and other vendor products.

He has written four books all published by Packt related to Microsoft Database and Application Development: SQL Server Integration Services Using Visual Studio 2005, Learning SQL Server Reporting Services 2008, Microsoft SQL Azure; Enterprise Application Development, and Microsoft Visual Studio Lightswitch Business Application Development. He regularly writes for his four blogs on Blogger; http://Hodentek.blogspot.com, http://HodentekHelp.blogspot.com, http://HodentekMobile.blogspot.com, and http://HodentekMSSS.blogspot.com. He received the 2011 Microsoft Community Contributor award.

Contact Jayaram Krishnaswamy

Books From Packt

 

  C# 2008 and 2005 Threaded Programming: Beginner's Guide
C# 2008 and 2005 Threaded Programming: Beginner's Guide

Small Business Server 2008 – Installation, Migration, and Configuration
Small Business Server 2008 – Installation, Migration, and Configuration

Implementing Microsoft Dynamics NAV 2009
Implementing Microsoft Dynamics NAV 2009

ASP.NET Data Presentation Controls Essentials
ASP.NET Data Presentation Controls Essentials

Software Testing with Visual Studio Team System 2008
Software Testing with Visual Studio Team System 2008

VSTO 3.0 for Office 2007 Programming
VSTO 3.0 for Office 2007 Programming

LINQ Quickly
LINQ Quickly

SOA Patterns with BizTalk Server 2009
SOA Patterns with BizTalk Server 2009

 

Your rating: None Average: 5 (1 vote)

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
W
2
h
6
9
p
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software