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 the dataset that would be used to fill the report. What I'm trying to do is get a raw data extract from the data that would be used to fill the report, without actually showing the report to the user. Is this possible?

Answer 1

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

You can programmatically generate a report if you make use of your report server's built-in 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 render the report to a variety of formats, like XML, MHTML, or PDF, and then try to extract data from that. You should add a table of the data you care about to the report, hide the table by changing its Visibility to Hide, but set its DataElementOutput property to Output so that whenever the report is rendered, the table will be included. Give the table some distinctive name (e.g., replace 'Tablix1' with 'FlatData'). You can then render the report to XML format and use XSLT to extract only the rows within that table. Here is some XSLT that I have used before to extract data from a rendered Report Builder 2.0 report:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"&gt;
  <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>
          </Defect>
        </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">
            <Defect>
              <!-- 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 this XSLT depends upon you naming your hidden table of data in the report to be 'FlatData'. If you know some of the data that's in your report will have HTML tags or other things that would not be valid XML if placed between two XML tags, change the XSLT above to wrap that data in CDATA tags (e.g., replace my_first_text_field with the field name whose value will need CDATA tags).

Applying this XSLT to the rendered XML version of a report will produce yet more XML, this time containing only the data from the report that you care about. The problem with just using the rendered XML version of the report is that it contains all the charts, the appearance information, etc., and isn't just your data. Try rendering one of your reports in XML format and look at the source; it has all kinds of craziness that you probably don't want.

For a command-line tool to apply XSLT transformations to XML, I recommend xalan. Here's an example usage:

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 transformed.xml will have a format like the following:

<?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

It's an interesting problem. When I had to solve it (for unit testing of rdl files), I wrote a simple xml parser that would extract the sql statement from the rdl file and execute it. It's quite simple, but of course get more complicated if your statements have many parameters. However the parameter info is also available in the file so you should be able to code up a generic solution (but you do of course need to supply values for the parameters).

Answer 3

As Sarah Vessels already mentioned in her answer You can push report server to render data, in many formats, maybe EXCEL is not bad choice depends on complication of your report desing. I asked similar qestion and after many trays I ended up with #TempTaples in Mssql which is madded before report is rend, looks like to me that Report Server is the last instance of job processing and all data and storage should be done in back end of rendering reports