Working with Report Definition Language (RDL) and Change it to add a column

We often come across a requirement to change the RDL generated through a wizard. This post will show you how to add a column to the RDL.

Step 1: You can get the xsd against which the rdl file is validated from following URL in the browser

For Default Instance

http://<servername>:<port number>/<report server>/reportdefinition.xsd
or

For Named Instance

http://<servername>:<port number>/<report server_instance name>/reportdefinition.xsd

Port number need not be specified if it is default port.

With this schema we see a lot of elements available. We will be concentrating on a few of them specific to a report.

Step 2: This report is based on AdventureWorks database and the Person.Contact table in it. The select query is

SELECT FirstName, LastName, EmailAddress FROM Person.Contact

This report is created using report creation wizard, with table and all the elements in details for the table. The style ‘Corporate’ is selected for the report.

The xsd for this report looks as follows:

<?xml version="1.0" encoding="utf-8"?>
<xs:schema xmlns:tns="http://schemas.microsoft.com/SQLServ
er/reporting/reportdesigner"
attributeFormDefault="unqualified"
elementFormDefault="qualified"
targetNamespace="http://schemas.microsoft.com/S
QLServer/reporting/reportdesigner"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="DataSourceID"
type="xs:string" />
<xs:element name="SecurityType"
type="xs:string" />
<xs:element name="TypeName" type="xs:string" />
<xs:element name="UseGenericDesigner"
type="xs:boolean" />
<xs:element name="DefaultName" type="xs:string" />
<xs:element name="ReportID" type="xs:string" />
<xs:element name="ReportUnitType"
type="xs:string" />
</xs:schema>

Step 3: Let us see how to change certain properties by changing the RDL. You can view this code by right clicking on the .RDL file and selecting the option for ‘View Code’

Step 4: We will add a column in the existing table. First we need to change the select statement for it.

SELECT Title,FirstName, LastName, EmailAddress FROM Person.Contact

Step 5: We need to add this in the dataset also. We do the changes as follows:

<Field Name="Title">
  <DataField>Title</DataField>
  <rd:TypeName>System.String</rd:TypeName>
</Field>

This element Field gets added in the Fields

Step 6: We need to incorporate these changes in the table by adding a column, specifying the heading for it and also giving the field from which it gets populated. This being the first column in the table, we need to add it before First Name

We do the change to the TablixBody, in ReportItems in Body as follows:

<TablixColumn>
  <Width>0.5in</Width>
</TablixColumn>

For this column, we are keeping the width less as compared to other columns to .5 inches

Step 7: We need to add TablixCell as follows:

<TablixCell>
     <CellContents>
         <Textbox Name="textTitle">
         <CanGrow>true</CanGrow>
         <KeepTogether>true</KeepTogether>
         <Paragraphs>
      <Paragraph>
                 <TextRuns>
      <TextRun>
      <Value>Title</Value>
      <Style>
                  <FontFamily>Tahoma</FontFamily>
          <FontSize>11pt</FontSize>
          <FontWeight>Bold</FontWeight>
          <Color>White</Color>
      </Style>
      </TextRun>
      </TextRuns>
      <Style />
      </Paragraph>
         </Paragraphs>
  <rd:DefaultName>textTitle</rd:DefaultName>
  <Style>
  <Border>
  <Color>DimGray</Color>
  <Style>Solid</Style>
  </Border>
  <BackgroundColor>#1c3a70</BackgroundColor>
  <PaddingLeft>2pt</PaddingLeft>
  <PaddingRight>2pt</PaddingRight>
  <PaddingTop>2pt</PaddingTop>
  <PaddingBottom>2pt</PaddingBottom>
  </Style>
</Textbox>
</CellContents>
</TablixCell>

Step 8: We now need to add the detail line for the new column title as follows

<TablixCell>
<CellContents>
<Textbox Name="Title">
<CanGrow>true</CanGrow>
<KeepTogether>true</KeepTogether>
 <Paragraphs>
 <Paragraph>
  <TextRuns>
 <TextRun>
 <Value>=Fields!Title.Value</Value>
 <Style>
 <FontFamily>Tahoma</FontFamily>
 </Style>
</TextRun>
</TextRuns>
<Style />
</Paragraph>
</Paragraphs>
<rd:DefaultName>Title</rd:DefaultName>
<Style>
<Border>
<Color>DimGray</Color>
<Style>Solid</Style>
</Border>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
 </Style>
 </Textbox>
 </CellContents>
 </TablixCell>

Step 9: Finally we need to add <TablixMember /> in TablixMembers in TablixColumnHierarchy.

And the task is done!