Tuesday, August 7, 2007

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>

13 comments:

mary said...

This looks great. I like the solution. Do you have an implementation of this code? I am new to .net and xml files so I am not sure what I need to set up to get this to work. Thanks!

Dan said...

Do you mean an implementation beyond the code I've displayed? Yes, I have implemented this solution but the code is customized for the needs of the specific application. That is, the XSLT and RLDC generation engine have been modified.

I have a basic XSLT (which is the one displayed in the post) and basic C# class that helps you generate the RDLC. I'll add a new post with this code.

Olivier said...

I have test your code and i like your solution.
I have a problem.
My report doesn't appear.
I can export into excel but i don't see the table in reportviewer.
Do you have any suggestion

Oliver

Dan said...

Olivier, please check my reply in the later revision post

mary said...

This may be a repeat. I did not see my post from earlier today. I really like this solution. Thanks for the sharing. If I wanted to include a report header and report footer section. How would I go about doing that? Also, if I wanted friendlier column names to display, where can I get that information. Once again thanks for a great solution.

md2dax said...

I have a report designed in VS and I need to add graphics (rectangles) at runtime. Is the solution to open the .rdlc-file and edit with xpath?

David

Unknown said...

It's a perfect tool for reports on fly. I developed one complete module on this which generates reports based on the list of table and columns selected

imthisgurl said...

Hi!

I found this blog very useful. Thanks for sharing. There is one thing I want to ask. How can I enable the ReportViewer toolbar for dynamically created RDLCs?

Unknown said...

It is with great pleasure that I offer my congratulations for this code , THAnks and gOOd work.

deepak said...

This is really very good Solution.

SouravBanerjee said...

Many Many thanks for this project, but i am getting error on the line
Line 94: // the rdlc.xsl does not account for multiple DataTables
Line 95: string physicalSchema = Server.MapPath(virtualSchema);
Line 96: data.WriteXmlSchema(physicalSchema);
Line 97:
Line 98: // load the DataSet schema in a DOM
Shema canot be found

SouravBanerjee said...

Your code i blindly copy and past in my project and call two function with dataset and name and i create a xslt folder and past your code in .xslt file....Line 94: // the rdlc.xsl does not account for multiple DataTables
Line 95: string physicalSchema = Server.MapPath(virtualSchema);
Line 96: data.WriteXmlSchema(physicalSchema);
Line 97:
Line 98: // load the DataSet schema in a DOM
Shema canot be found...........
please reply

Kumaran said...

Hi, thanks for your code. I have query regarding this method "BindReportViewer(DataSet dataSet)..." What data set does it expects to be passed?. Sorry if the question was very dumb but I got little confused in this.

Kindly let me know. Thanks