Generate a RDLC dynamically for the VS 2005 Report Viewer Control

The main drawback to the Report Viewer control in VS 2005 is that it relies on a pre-defined xml definition for the Report, or RDLC file. In order to construct this file, you need to have you data on hand. This makes it difficult to use the Report Viewer control with any kind of Ad-Hoc report utility. You may still want to use the Report Viewer because it allows exporting to PDF or Excel.

I had just such a problem. I wanted the ability to run several queries / SPs against my database and return the results in the Report Viewer. But, I wanted the ability to add or edit any one of my reports on the fly. Doing so would require a custom RDLC file.

So, what did is I wrote an XSLT transform to transform the Schema XML of a DataSet into an RDLC. Now the XLST is very simple. It just takes a DataSet with a single DataTable and generates an RDLC in a 2 dimensional table.

You can extend the XSLT to do more complex things. The XSLT becomes a template for your reports.

Anyway, here is the code to bind the RDLC and the DataSet to the Report Viewer control, the XLST, and my Transform function. Please comment if you find this code useful or have any suggestions...

private void BindReportViewer(DataSet data)
{
 string rdlcName = "MyReport";
 string virtualRdlc = BuildRDLC(data, rdlcName);
        
 ReportViewer1.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Local;

 Microsoft.Reporting.WebForms.ReportDataSource rds = new Microsoft.Reporting.WebForms.ReportDataSource();
 rds.Name = rdlcName + "_Table";
 rds.Value = data.Tables[0];

 Microsoft.Reporting.WebForms.LocalReport lr = ReportViewer1.LocalReport;
 lr.ReportPath = virtualRdlc;
 lr.DataSources.Add(rds);
}


/// <summary>
/// constructs a simple report RDLC file based on a DataSet
/// </summary>
/// <param name="data"></param>
/// <param name="name"></param>
/// <returns></returns>
private string BuildRDLC(DataSet data, string name)
{
 // establish some file names
 string virtualXslt = "xslt/rdlc.xsl";
 string virtualRdlc = "rdlc/" + name + ".rdlc";
 string virtualSchema = "rdlc/" + name + ".schema";

 // set the NAME on the DataSet
 // this may or may not be necessary, but the RDLC and DataSet
 // will both have the same name if this is done.
 data.DataSetName = name;

 // write the DataSet Schema to a file
 // we should be passing a DataSet with only one DataTable
 // the rdlc.xsl does not account for multiple DataTables
 string physicalSchema = Server.MapPath(virtualSchema);
 data.WriteXmlSchema(physicalSchema);

 // load the DataSet schema in a DOM
 System.Xml.XmlDocument xmlDomSchema = new System.Xml.XmlDocument();
 xmlDomSchema.Load(physicalSchema);

 // append the NAME to the schema DOM 
 // this is so we can pick it up in the rdlc.xsl
 // and use it
 xmlDomSchema.DocumentElement.SetAttribute("Name", name + "_Table");

 // transform the Schema Xml with rdlc.xsl
 string physicalXslt = Server.MapPath(virtualXslt);
 string xml = TransformXml(xmlDomSchema.OuterXml, physicalXslt);

 // save off the resultng RDLC file
 string physicalRdlc = Server.MapPath(virtualRdlc);
 XmlDocument xmlDomRdlc = new XmlDocument();
 xmlDomRdlc.LoadXml(xml);
 xmlDomRdlc.Save(physicalRdlc);

 // return the virtual path of the RDLC file
 // this is needed by the asp:ReportViewer
 return virtualRdlc;
}

public static string TransformXml(string xml, string xslFile)
{
 string result = string.Empty;
 using (System.IO.MemoryStream memory = new System.IO.MemoryStream(System.Text.Encoding.ASCII.GetBytes(xml)))
 {
  System.Xml.Xsl.XslCompiledTransform transform = new System.Xml.Xsl.XslCompiledTransform();
                transform.Load(xslFile);

                System.Xml.XPath.XPathDocument xpathDoc = new System.Xml.XPath.XPathDocument(memory);
                System.Text.StringBuilder sb = new System.Text.StringBuilder();
                System.IO.StringWriter sw = new System.IO.StringWriter(sb);
                transform.Transform(xpathDoc, null, sw);
            
                result = sb.ToString();
            }
            return result;
        }
}


<?xml version="1.0"?>
<!-- Stylesheet for creating ReportViewer RDLC documents -->
<xsl:stylesheet version="1.0"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:msxsl="urn:schemas-microsoft-com:xslt"
  xmlns:xs="http://www.w3.org/2001/XMLSchema"
  xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
  xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"  xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
  >
 
 <xsl:variable name="mvarName" select="/xs:schema/@Name"/>
 <xsl:variable name="mvarFontSize">8pt</xsl:variable>
 <xsl:variable name="mvarFontWeight">500</xsl:variable>
 <xsl:variable name="mvarFontWeightBold">700</xsl:variable>


 <xsl:template match="/">
  <xsl:apply-templates select="/xs:schema/xs:element/xs:complexType/xs:choice/xs:element/xs:complexType/xs:sequence">
  </xsl:apply-templates>
 </xsl:template>

 <xsl:template match="xs:sequence">
  <Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition">
   <BottomMargin>1in</BottomMargin>
   <RightMargin>1in</RightMargin>
   <LeftMargin>1in</LeftMargin>
   <TopMargin>1in</TopMargin>
   <InteractiveHeight>11in</InteractiveHeight>
   <InteractiveWidth>8.5in</InteractiveWidth>
   <Width>6.5in</Width>
   <Language>en-US</Language>
   <rd:DrawGrid>true</rd:DrawGrid>
   <rd:SnapToGrid>true</rd:SnapToGrid>
   <rd:ReportID>7358b654-3ca3-44a0-8677-efe0a55c7c45</rd:ReportID>
   
   <xsl:call-template name="BuildDataSource">
   </xsl:call-template>

   <xsl:call-template name="BuildDataSet">
   </xsl:call-template>

   <Body>
    <Height>0.50in</Height>
    <ReportItems>
     <Table Name="table1">
      <DataSetName><xsl:value-of select="$mvarName" /></DataSetName>
      <Top>0.5in</Top>
      <Height>0.50in</Height>
      <Header>
       <TableRows>
        <TableRow>
         <Height>0.25in</Height>
         <TableCells>

          <xsl:apply-templates select="xs:element" mode="HeaderTableCell">
          </xsl:apply-templates>

         </TableCells>
        </TableRow>
       </TableRows>
      </Header>
      <Details>
       <TableRows>
        <TableRow>
         <Height>0.25in</Height>
         <TableCells>

          <xsl:apply-templates select="xs:element" mode="DetailTableCell">
          </xsl:apply-templates>
 
         </TableCells>
        </TableRow>
       </TableRows>
      </Details>
      <TableColumns>

       <xsl:apply-templates select="xs:element" mode="TableColumn">
       </xsl:apply-templates>

      </TableColumns>
     </Table>
    </ReportItems>
   </Body>
  </Report>
 </xsl:template>

 <xsl:template name="BuildDataSource">
  <DataSources>
   <DataSource Name="DummyDataSource">
    <ConnectionProperties>
     <ConnectString/>
     <DataProvider>SQL</DataProvider>
    </ConnectionProperties>
    <rd:DataSourceID>84635ff8-d177-4a25-9aa5-5a921652c79c</rd:DataSourceID>
   </DataSource>
  </DataSources>
 </xsl:template>

 <xsl:template name="BuildDataSet">
  <DataSets>
   <DataSet Name="{$mvarName}">
    <Query>
     <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
     <CommandText/>
     <DataSourceName>DummyDataSource</DataSourceName>
    </Query>
    <Fields>

     <xsl:apply-templates select="xs:element" mode="Field">
     </xsl:apply-templates>

    </Fields>
   </DataSet>
  </DataSets>
 </xsl:template>

 <xsl:template match="xs:element" mode="Field">
  <xsl:variable name="varFieldName"> 
   <xsl:value-of select="@name" />
  </xsl:variable>

  <xsl:variable name="varDataType">
   <xsl:choose>
    <xsl:when test="@type='xs:int'">System.Int32</xsl:when>
    <xsl:when test="@type='xs:string'">System.String</xsl:when>
    <xsl:when test="@type='xs:dateTime'">System.DateTime</xsl:when>
    <xsl:when test="@type='xs:boolean'">System.Boolean</xsl:when>
   </xsl:choose>
  </xsl:variable>

  <Field Name="{$varFieldName}">
   <rd:TypeName><xsl:value-of select="$varDataType"/></rd:TypeName>
   <DataField><xsl:value-of select="$varFieldName"/></DataField>
  </Field>
 </xsl:template>

 <xsl:template match="xs:element" mode="HeaderTableCell">
  <xsl:variable name="varFieldName"> 
   <xsl:value-of select="@name" />
  </xsl:variable>

  <TableCell>
   <ReportItems>
    <Textbox Name="textbox{position()}">
     <rd:DefaultName>textbox<xsl:value-of select="position()"/>
     </rd:DefaultName>
     <Value><xsl:value-of select="$varFieldName"/></Value>
     <CanGrow>true</CanGrow>
     <ZIndex>7</ZIndex>
     <Style>
      <TextAlign>Center</TextAlign>
      <PaddingLeft>2pt</PaddingLeft>
      <PaddingBottom>2pt</PaddingBottom>
      <PaddingRight>2pt</PaddingRight>
      <PaddingTop>2pt</PaddingTop>
      <FontSize><xsl:value-of select="$mvarFontSize"/></FontSize> 
      <FontWeight><xsl:value-of select="$mvarFontWeightBold"/></FontWeight> 
      <BackgroundColor>#000000</BackgroundColor> 
      <Color>#ffffff</Color>
      <BorderColor>
       <Default>#ffffff</Default>
      </BorderColor>
      <BorderStyle>
       <Default>Solid</Default>
      </BorderStyle>
     </Style>
    </Textbox>
   </ReportItems>
  </TableCell>
 </xsl:template>

 <xsl:template match="xs:element" mode="DetailTableCell">
  <xsl:variable name="varFieldName"> 
   <xsl:value-of select="@name" />
  </xsl:variable>
  
  <TableCell>
   <ReportItems>
    <Textbox Name="{$varFieldName}">
     <rd:DefaultName><xsl:value-of select="$varFieldName"/></rd:DefaultName>
     <Value>=Fields!<xsl:value-of select="$varFieldName"/>.Value</Value>
     <CanGrow>true</CanGrow>
     <ZIndex>7</ZIndex>
     <Style>
      <TextAlign>Left</TextAlign>
      <PaddingLeft>2pt</PaddingLeft>
      <PaddingBottom>2pt</PaddingBottom>
      <PaddingRight>2pt</PaddingRight>
      <PaddingTop>2pt</PaddingTop>
      <FontSize><xsl:value-of select="$mvarFontSize"/></FontSize> 
      <FontWeight><xsl:value-of select="$mvarFontWeight"/></FontWeight> 
      <BackgroundColor>#e0e0e0</BackgroundColor> 
      <Color>#000000</Color> 
      <BorderColor>
       <Default>#ffffff</Default> 
      </BorderColor>
      <BorderStyle>
        <Default>Solid</Default>
      </BorderStyle>
     </Style>
    </Textbox>
   </ReportItems>
  </TableCell>
 </xsl:template>

 <xsl:template match="xs:element" mode="TableColumn">
  <TableColumn>
   <Width>0.75in</Width>
  </TableColumn>
 </xsl:template>

 <xsl:template name="replace-string">
  <xsl:param name="text"/>
  <xsl:param name="from"/>
  <xsl:param name="to"/>
  <xsl:choose>
   <xsl:when test="contains($text, $from)">
    <xsl:variable name="before" select="substring-before($text, $from)"/>
    <xsl:variable name="after" select="substring-after($text, $from)"/>
    <xsl:variable name="prefix" select="concat($before, $to)"/>
    <xsl:value-of select="$before"/>
    <xsl:value-of select="$to"/>
    <xsl:call-template name="replace-string">
     <xsl:with-param name="text" select="$after"/>
     <xsl:with-param name="from" select="$from"/>
     <xsl:with-param name="to" select="$to"/>
    </xsl:call-template>
   </xsl:when>
   <xsl:otherwise>
    <xsl:value-of select="$text"/>
   </xsl:otherwise>
  </xsl:choose>
 </xsl:template>
</xsl:stylesheet>