Advanced Reporting Services development, part 1: the RDL object model

If you ever used SQL Server Reporting Services (SSRS), you might have been developing reports using Report Builder or Business Intelligence Development Studio (BIDS). Most of time, these tools are just fine for developing reports that fits your needs or your customer requirements. But sometimes you need to build up your own reporting tool, which means you will have to get your hands dirty and inject your own reporting soup into the SSRS engine. The goal of this article serie is to show you the way to building highly customized reporting tool that will fit your needs. These articles mainly targets SSRS 2008 R2 and the Denali CTP3 version and might differ from previous versions.

First stop: the RDL object model. RDL stands for Report Language Defintion. It is a XML based language developed by Microsoft and firstly introduced with the SSRS for SQL Server 2000 release. Every SSRS object you will have to play with are defined with RDL and that is why both the BIDS report designer and Report Builder are using the Microsoft.ReportingServices.RdlObjectModel namespace to build them up. Now let’s have a look on how we can take advantage of this model and go beyond the designers functionalities.

If you wonder how you can generate and manipulate reports you might bump onto this MSDN article. Basically, all it says is that you can use XmlDocument or XmlTextWriter to write your own custom reports. Quite boring and painful. But if BIDS and Report Builder uses a specific model to build up reports, why can’t we also take advantage of these functionalities? Of course we can!

Create a report from scratch

Open up Visual Studio, create a new project and first add references to Microsoft.ReportingServices.Designer.Controls.dll and Microsoft.ReportingServices.RichText.dll. If you installed BIDS you can find the assembly into your Visual Studio Common7\IDE\PrivateAssemblies directory. If you installed Report Builder you can find the asssembly right into the software installation directory. Then create a Report instance from the Microsoft.ReportingServices.RdlObjectModel namespace.

Report myReport = new Report();
myReport.Page.PageWidth = new ReportSize(210, SizeTypes.Mm);
myReport.Page.PageHeight = new ReportSize(297, SizeTypes.Mm);
myReport.Width = new ReportSize(210, SizeTypes.Mm);
myReport.Body.Height = new ReportSize(50, SizeTypes.Mm);

Here we created the base of our report where we set our page size to be standard a A4 format and our design surface to have the A4 width but with a more reasonable height.

Then we will add a simple DataSource object. It is not a requirement for the report to have a data source but this is an object you will absolutely require when building dynamic reports.

DataSource myDataSource = new DataSource
{
    Name = "myDataSource",
    ConnectionProperties = new ConnectionProperties
    {
        DataProvider = "SQL",
        IntegratedSecurity = true,
        ConnectString = @"Data Source=localhost\SQLSERVER;Initial Catalog=myDatabase"
    }
};;

Then you can start adding some basic element to the report body like a Textbox (yes it is a lower case “b”!).

Textbox myTextBox = new Textbox();
myTextBox.Name = "myTextBox";
myTextBox.Paragraphs[0].TextRuns[0].Value = "Hello World";

myReport.Body.ReportItems.Add(myTextBox);

Note that whenever you call the Body property from the Report object, it will return the body of the first ReportSection of the current report. Also setting the name of your report items is really important. If you forget to do so, you won’t be able to run your report and designers will not open it.

Create a report from template

So here we built a basic report that works and could run on SSRS. But as you have noticed something not really nice is that you have to set the layout manually and it is clearly not an optimized way of building custom reports. So what we will do is create a basic report that can be used as a template.

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns:cl="http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition">
  <AutoRefresh>0</AutoRefresh>
  <DataSources>
    <DataSource Name="myDataSource">
      <ConnectionProperties>
        <DataProvider>SQL</DataProvider>
        <ConnectString>Data Source=localhost\SQLSERVER;Initial Catalog=myDatabase</ConnectString>
        <IntegratedSecurity>true</IntegratedSecurity>
      </ConnectionProperties>
      <rd:SecurityType>Integrated</rd:SecurityType>
      <rd:DataSourceID>b0ed98bc-cc6f-4636-8e4b-9ebadbd5b6e2</rd:DataSourceID>
    </DataSource>
  </DataSources>
  <ReportSections>
    <ReportSection>
      <Body>
        <Height>50mm</Height>
        <Style>
          <Border>
            <Style>None</Style>
          </Border>
        </Style>
      </Body>
      <Width>210mm</Width>
      <Page>
        <PageHeight>29.7cm</PageHeight>
        <PageWidth>21cm</PageWidth>
        <LeftMargin>2cm</LeftMargin>
        <RightMargin>2cm</RightMargin>
        <TopMargin>2cm</TopMargin>
        <BottomMargin>2cm</BottomMargin>
        <ColumnSpacing>0.13cm</ColumnSpacing>
        <Style />
      </Page>
    </ReportSection>
  </ReportSections>
  <rd:ReportUnitType>Mm</rd:ReportUnitType>
  <rd:ReportID>21e8391f-35bc-4a68-bdc6-6c9f183653e8</rd:ReportID>
</Report>

In this template we only set the page size, the design surface size and added the data source. To get the exact same object as before we only have to instantiate the report from the template and add the Textbox.

Report myReport = Report.Load("TemplateA4.rdl");

Textbox myTextBox = new Textbox();
myTextBox.Name = "myTextBox";
myTextBox.Paragraphs[0].TextRuns[0].Value = "Hello World";

myReport.Body.ReportItems.Add(myTextBox);

As you can see it is a bunch of somewhat boring and hard coded instructions we saved here.

You are now ready to build customized reporting tool that gives you full control on how the report will be generated. I strongly suggest to build some business relevant reports and then analyse the XML structure to get more familiar with the most common RDL objects. This will give you a better understanding on how you can extend the designer capabilities and shape the report generation.

Also you probably wonder “how am I gonna serialize my report to a RDL file?”. That is a really good question. A crucial question in fact. The reply is the RdlSerializer, a subject that I will cover in the next part of this article serie.

Edit 15/10/11: Advanced SSRS, part 2: The RDL Serializer