How can I get a dataset to populate from an RDL file?

Question

I have an RDL report file, and I would like to somehow “run” the report and get a dataset that will be used to populate the report. What I'm trying to do is get an unprocessed extract from the data that will be used to populate the report, without actually displaying the report to the user. Is it possible?

Answer 1

If I understand what you want to do, then yes, it is possible, but it is some kind of pain. I did this for various snapshots (in the Report Manager report) of Report Builder 2.0 reports.

You can programmatically generate a report if you use the integrated report server web services. See ReportExecutionService.Render Method for some sample code (note that I use the ReportExecution2005 web service even with SQL Server 2008). You can display the report in various formats, such as XML, MHTML or PDF, and then try to extract data from it. You must add the table of data that you need for the report, hide the table by changing its Visibility to Hide, but set the DataElementOutput property to Exit so that whenever the report is displayed, the table will be included. Give the table some kind of unique name (for example, replace "Tablix1" with "FlatData"). Then you can place the report in XML format and use XSLT to retrieve only the rows in this table. Here are some XSLTs that I used earlier to extract data from a Report Builder 2.0 report:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:output method="xml" indent="yes" encoding="utf-8"/> <xsl:variable name="reportID" select="*[local-name()='Report']/@Name"/> <!-- Uppercase and lowercase alphabets for case-insensitive string comparisons --> <xsl:variable name="up" select="'ABCDEFGHIJKLMNOPQRSTUVWXYZ'"/> <xsl:variable name="lo" select="'abcdefghijklmnopqrstuvwxyz'"/> <xsl:template match="/"> <xsl:element name="ContainerElementOfMyData"> <xsl:attribute name="ReportID"> <xsl:value-of select="$reportID"/> </xsl:attribute> <xsl:for-each select="*[local-name()='Report']/*[local-name()='FlatData']"> <!-- If the FlatData node has attributes on its tag, insert all of those attributes in a single node --> <xsl:if test="count(@*) &gt; 0"> <MyNode> <xsl:for-each select="@*"> <xsl:variable name="parentAttrName" select="name(.)"/> <xsl:element name="{$parentAttrName}"> <xsl:value-of select="."/> </xsl:element> </xsl:for-each> </MyNode> </xsl:if> <!-- Go through each tag in FlatData that starts with 'Details' --> <xsl:for-each select="//*[substring(local-name(), 1, 7)='Details']"> <xsl:if test="count(@*) &gt; 0"> <MyNode> <!-- For each attribute of the Details tag: --> <xsl:for-each select="@*"> <xsl:variable name="attrName" select="name(.)"/> <xsl:variable name="lowerAttrName" select="translate($attrName,$up,$lo)"/> <xsl:variable name="attrValue" select="."/> <!-- Write the attribute name as its own tag --> <xsl:element name="{$attrName}"> <xsl:choose> <xsl:when test="$attrValue = ''"> <!-- Do nothing because no value to output and we don't want empty CDATA tags --> </xsl:when> <!-- When field might have HTML tags, we want to wrap it in CDATA tags: --> <xsl:when test="$lowerAttrName = 'my_first_text_field' or $lowerAttrName = 'my_other_text_field'"> <xsl:text disable-output-escaping="yes"><![CDATA[<![CDATA[]]></xsl:text> <xsl:value-of select="$attrValue"/> <xsl:text disable-output-escaping="yes">]]</xsl:text> <xsl:text disable-output-escaping="yes">></xsl:text> </xsl:when> <!-- When field will not have HTML tags, just output its value as normal --> <xsl:otherwise> <xsl:value-of select="$attrValue"/> </xsl:otherwise> </xsl:choose> </xsl:element> </xsl:for-each> </MyNode> </xsl:if> </xsl:for-each> </xsl:for-each> </xsl:element><!--End of ContainerElementOfMyData--> </xsl:template> </xsl:stylesheet> 

Note that this XSLT depends on how you specify your hidden data table in the report as "FlatData". If you know some data that in your report would have HTML tags or other things that would not be valid XML if placed between two XML tags, change the XSLT above to wrap this data in CDATA tags (for example, replace my_first_text_field with name of the field whose value will contain CDATA tags).

Applying this XSLT to the rendered XML version of the report will create even more XML, this time containing only the data from the report that you are interested in. The problem with using the just submitted XML version of the report is that it contains all the charts, appearance information, etc., and not just your data. Try to make one of your reports in XML format and look at the source; he has all kinds of craziness you probably don't want.

For a command line tool for applying XSLT to XML transformations, I recommend xalan . Here is a usage example:

 PS C:\Program Files\xalan-j_2_7_0> java org.apache.xalan.xslt.Process -IN rdl_rendered_to_xml.xml -XSL xsl_shown_above.xsl -OUT transformed.xml 

The resulting transform.xml file will have the following format:

 <?xml version="1.0" encoding="utf-8"?> <ContainerElementOfMyData ReportID="MyReportName"> <MyNode> <Key1>Value 1</Key1> <Key2>Second value of your data</Key2> </MyNode> </ContainerElementOfMyData> 

Answer 2

As Sarah Sousse mentioned in her answer, you can click the report server to process the data, in many formats, perhaps EXCEL is a good choice, depending on the complexity of your report. I asked for a similar qestion , and after many trays I ended up C # TempTaples in Mssql, which is crazy before the report is distributed, it looks like the report server is the last instance of job processing and all the data and storage should be done at the end of the rendering reports

Answer 3

This is an interesting problem. When I had to solve it (for unit testing rdl files), I wrote a simple xml parser that would extract the sql statement from the rdl file and execute it. This is pretty simple, but of course it gets complicated if your operators have many parameters. However, parameter information is also available in the file, so you should be able to code the general solution (but you, of course, need to specify values ​​for the parameters).