Chapter 28. Custom Report Definition Language (RDL) Generators and Customizing Report Definition

Report Definition Language (RDL) is an XML-based language that contains data retrieval and layout information for a report.

Just like an XML, RDL can be easily read and edited in any text editor. RDL is designed to provide interchangeable editing capabilities for tools that "understand" RDL's schema. Ad hoc reporting capabilities and Report Builder has significantly reduced the need to programmatically generate RDL.

In some cases, however, a company might have a need to programmatically generate RDL. This is the case, for example, for a software development company in a business of writing development tools. In a limited number of cases, a company might also want to extend RDL to describe additional report items that are not available in SSRS. This could be a "fancy table" item, for example.

As an illustration, suppose that Adventure Works needs a quick (but not necessarily very "friendly") web-based mechanism to generate reports with limited functionality. You can expand the example further as needed.

To simplify XML processing, you can leverage members of the System.Xml namespace, such as classes XmlDocument and XmlTextWriter . SQL Server 2005 comes with a tutorial on how to create a custom RDL.

The tutorial, which can be found in SQL Server Books Online at the following location SQL Server 2005 Tutorials/Reporting Services Tutorials/Generating RDL Using the .NET Framework (or online at http://msdn2.microsoft.com/ms170667.aspx), starts with a blank report and creates a report's RDL piece by piece, using XmlTextWriter .

We use the term RDL template describes an XML that conforms to an RDL schema, but does not necessarily describe a functional report. This chapter demonstrates how to create and reuse the RDL template. For simplicity, the sample uses most of the RDL from the template and customizes just a few fields, such as <Query> , <Fields> , and <ConnectString> .

Suppose that the goal is to create a web application that will use an RDL template and will programmatically modify this template, based on user input.

The template is a report with a three-column table. The application allows customizing the template's query and the template's connection string. For simplicity, the application only uses the first three fields from a query and considers all of the fields being the System.String type.

To create a template, you can leverage Report Designer and start with a simple report that connects to the AdventureWorks database, selects three fields from any of the tables, and displays results in a table report item.

After the report is completed, you can convert it to an RDL template:

1.

Right-click on the report in Solution Explorer and select View Code or view the report's RDL file in a text editor.

Note

The accompanying code contains changes to the <CommandText> value to SELECT F1, F2, F3 FROM TBL ; however, the value of this node is irrelevant because it gets completely replaced by the sample application. You can decide if complete replacement of <CommandText> is desirable for each individual template.

2.

Edit the RDL and replace every occurrence of the first query field name with some unique value, such as F1. Repeat the procedure for the rest of the fields (F2 and F3, respectively). When going through the replacement process, please keep in mind that Report Designer adds spaces between capitalizations (that is, OrderId gets changed to Order Id ). This happens when Report Designer assigns values for the <Header> row report items.

The resulting RDL/XML should look similar to the following (only key points are shown) :

[View full width]

 
 [View full width] 
 ... <ConnectString> Data Source=localhost;Initial Catalog=AdventureWorks; Integrated Security=SSPI; </ConnectString>     ...     <ReportItems>       <Table Name="table1">         <Details>           <TableRows>             <TableRow>               <TableCells>                 <TableCell>                   <ReportItems>                     <Textbox Name="F1">                       <rd:DefaultName>F1</rd:DefaultName>                       <CanGrow>true</CanGrow>                                             <Value>=Fields!F1.Value</Value>  </Textbox>                   </ReportItems>                 </TableCell>         ...         </Details>         <Header>           <TableRows>             <TableRow>               <TableCells>                 <TableCell>                   <ReportItems>                        <Textbox Name="textbox2">                             <CanGrow>true</CanGrow>                             <Value>F1</Value>                        </Textbox>                   </ReportItems>                 </TableCell>         ...         </Header>       ...       </Table>     </ReportItems>     ...     <DataSets>       <DataSet Name="DataSource">         <Query>           <CommandText>SELECT F1, F2, F3 FROM TBL </CommandText>  <DataSourceName>DataSource</DataSourceName>           </Query>           <Fields>             <Field Name="F1">               <rd:TypeName>System.String</rd:TypeName>               <DataField>F1</DataField>             </Field>             ...       </DataSet>     </DataSets>> 

After changes are completed, you can deploy this RDL template. Because an RDL template conforms to RDL specifications, SSRS does not "complain" about such deployment despite the fact that this is not really a functional report.

To achieve the desired functionality, the sample uses the following namespaces:

• System.IO to access stream handling, particularly the MemoryStream class

• ReportService2005 to interact with SSRS to retrieve a template and to store a report

• System.Web.Services.Protocols to handle SOAP exceptions

• System.Xml to access XML handling, particularly the XmlDocument class

• System.Data.SqlClient to validate a query and get field names

First, the sample loads a template and displays a connection string and the query ( CommandText ) from the template:

 byte[] reportDefinition = rs.GetReportDefinition(templatePath); MemoryStream stream = new MemoryStream(reportDefinition); System.Xml.XmlDocument doc = new System.Xml.XmlDocument(); doc.Load(stream); txtConnection.Text = doc.GetElementsByTagName("ConnectString")[0].InnerXml; txtQuery.Text = doc.GetElementsByTagName("CommandText")[0].InnerXml; 

As a next series of steps, the sample

1.	Collects an updated string and a query from the UI and replaces the original values in the RDL template doc.GetElementsByTagName("ConnectString")[0].InnerXml = txtConnection.Text; doc.GetElementsByTagName("CommandText")[0].InnerXml = txtQuery.Text;
2.	Executes a query to validate the query syntax SqlConnection con = new SqlConnection(txtConnection.Text); con.Open(); System.Data.SqlClient.SqlCommand cmd = new SqlCommand(txtQuery.Text, con); SqlDataReader reader = cmd.ExecuteReader();
3.	Gets the names of fields and replaces template strings with field names reader.Read(); String strTmp = doc.InnerXml.Replace("F1", reader.GetName(0)); strTmp = strTmp.Replace("F2", reader.GetName(1)); strTmp = strTmp.Replace("F3", reader.GetName(2)); doc.InnerXml = strTmp;
4.	Writes the result of changes as a report to a specified location MemoryStream stream = new MemoryStream(); doc.Save(stream); Byte[] definition = stream.ToArray(); stream.Close(); Warning[] warnings = rs.CreateReport(txtResultName.Text, txtResultPath.Text, false, definition, null);

After the final report is deployed, it can be used just like any report. The purpose of the sample is to demonstrate the basic capabilities of an RDL generation. The sample provides rudimentary error handling and can use some performance improvements. For example, you can find better alternatives to validate a query and to retrieve fields than executing a SqlDataReader .