How to dynamically generate SSRS Report

SSRS is a wonderful tool for quickly retrieving data and presenting it to the user in a format predefined at design-time. But what if there is a need to create a new report at run-time, based on some dynamic data structure or any specific user needs? Of course, for advanced business users Microsoft Report Builder is an option, but often developers may need to have full control on the generating the reports on their own, and in such case as a solution I would recommend just to create an RDL file which is nothing else than XML written using Report Definition Language for which the thorough specification exists in MSDN under the following links:

Report Definition Language (SSRS)

SQL Server RDL Specification

In this post, I’d like to provide a very simple example of using this technique in VB.Net application which demonstrates how to generate and then preview a new report in following steps:

1. A new RDL is being created based on a data returned by a given SQL statement.
2. Newly created RDL is used as source of LocalReport in ReportViewer control.
3. A data source (DataReader) is bind to LocalReport and, finally, the report is rendered and opened in ReportViewer.

For simplicity, the sample report contains only two elements: a title (TextBox) and a table (Tablix) in a predefined format. The dynamic part is that the title can be provided by a user, and the column list in the table is build based on the fields returned by SQL provided by the user.

The report template is saved as XML file RDLTemplate.xml – be sure when building the project that the file is copied to the output directory (property “Copy to Output Directory” is set to “Copy always”). In code we just fill some missed parts in this XML by using String.Format method and after all save the XML as RDL file:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
        Using streamReader As New StreamReader(Directory.GetCurrentDirectory + "\RDLTemplate.xml")
            '{0} - Report Title
            '{1} - Tablix Columns
            '{2} - Header Row Cells 
            '{3} - Detail Row Cells
            '{4} - Tablix Members
            '{5} - Connection String
            '{6} - SQL Statement
            '{7} - Table Fields
            '{8} - Report GUID
            '{9} - DataSource GUID
            xml = String.Format(streamReader.ReadToEnd(), _
                    txtReportTitle.Text, _
                    GetTablixColumns(dataReader.FieldCount), _
                    GetTableCells(dataReader, "lbl", "{0}"), _
                    GetTableCells(dataReader, "txt", "=Fields!{0}.Value"), _
                    GetTablixMembers(dataReader.FieldCount), _
                    txtConnectionString.Text, _
                    txtSQL.Text, _
                    GetFields(dataReader), _
                    System.Guid.NewGuid().ToString(), _
                    System.Guid.NewGuid().ToString())
        End Using
        Using streamReader As New StreamReader(Directory.GetCurrentDirectory + "\RDLTemplate.xml")
            '{0} - Report Title
            '{1} - Tablix Columns
            '{2} - Header Row Cells 
            '{3} - Detail Row Cells
            '{4} - Tablix Members
            '{5} - Connection String
            '{6} - SQL Statement
            '{7} - Table Fields
            '{8} - Report GUID
            '{9} - DataSource GUID
            xml = String.Format(streamReader.ReadToEnd(), _
                    txtReportTitle.Text, _
                    GetTablixColumns(dataReader.FieldCount), _
                    GetTableCells(dataReader, "lbl", "{0}"), _
                    GetTableCells(dataReader, "txt", "=Fields!{0}.Value"), _
                    GetTablixMembers(dataReader.FieldCount), _
                    txtConnectionString.Text, _
                    txtSQL.Text, _
                    GetFields(dataReader), _
                    System.Guid.NewGuid().ToString(), _
                    System.Guid.NewGuid().ToString())
        End Using

Please note, that even though the Data Source is defined in RDL, when using it as a local report (ReportViewer.LocalReport) you need to provide your ReportViewer with an actual data source, which can be either of type DataTable, DataReader or any other appropriate:

1
2
3
4
        Dim reportDataSource As ReportDataSource = New ReportDataSource("ReportDataSet", dataReader)
 
        ReportViewer1.LocalReport.DataSources.Clear()
        ReportViewer1.LocalReport.DataSources.Add(reportDataSource)
        Dim reportDataSource As ReportDataSource = New ReportDataSource("ReportDataSet", dataReader)

        ReportViewer1.LocalReport.DataSources.Clear()
        ReportViewer1.LocalReport.DataSources.Add(reportDataSource)

Other way of using the generated RDL is to upload it to existing SSRS server using the SSRS web service and then link ReportViewer.ServerReport to it. In this case you won’t need to bind a data source to ReportViewer, since SQL Server will do all the magic for you itself.
If you successfully build the project, run it, change SQL connection string and SQL as you need and click “Generate and Run Report” button, and if everything went fine, you will see the result of your work:

screenshot

The full source of the sample you will find here: RDLSample.zip.Hope it will be a good starting point in developing of your own nice report builder.

One thought on “How to dynamically generate SSRS Report

  1. Great piece of work: Absolute life saver for me. I have to do some additional code on CLSCompliance as some of the legacy stuff I have to deal with has spaces in the field names. I will send you the snippet if you like.

    Cheers

Leave a comment

Your email address will not be published. Required fields are marked *