Sunday, 11 January 2015

SSRS static analysis. Dependencies and health checks

As discussed in this morning's post, we've stitched together a utility to go through the folders containing SSRS source code, then write out the dependency tree like this

<?xml version="1.0" encoding="utf-8"?>
 <ReportHierarchy>
<ReportDirectory> <!-- with no subreports -->
<Name>GaugeSamples 2008 R2</Name> <!-- from adventure works samples-->
<Report>
<Name>Gallery_ThermometerRS2008R2.rdl</Name>
</Report>
<Report>
<Name>PointersRS2008R2.rdl</Name>
</Report>
<Report>
<Name>ScalesRS2008R2.rdl</Name>
</Report>
</ReportDirectory>
<ReportDirectory> <!-- with no reports -->
<Name>logs</Name>
</ReportDirectory>
<ReportDirectory><!-- with subreports, names changed to protect confidentiality -->
<Name>CustomerReports</Name>
<Report>
<Name>ReportingPackSpeed.rdl</Name>
<Subreport>
<Name>subreportDT1</Name>
<ReportName>SpeedMachineGroupForYear</ReportName>
</Subreport>
<Subreport>
<Name>subreportDT2</Name>
<ReportName>SpeedMachineGroupForYear</ReportName>
</Subreport>
             </Report>
</ReportDirectory>
</ReportHierarchy>

There are a couple of tidy ups to do on the code

  • To get correct Report Directory if there is more than one level of subdirectories.
  • To write and execute the XSLT transformation to get the subreportOf property, including a None output.
  • Find out if subreports are supported in the header and footer, and if so include them in the .xslt file.
  <ReportDirectory>
<Name>CustomerReports</Name>
<Report>SpeedMachineGroupForYear
                                     <SubreportOf>ReportingPackSpeed.rdl</SubReportOf>
</Report>
</ReportDirectory>
</ReportDirectory>
        <Name>GaugeSamples 2008 R2</Name> <!-- from adventure works samples-->                                    <Report>Gallery_ThermometerRS2008R2.rdl                                                                                          <SubreportOf>None</SubReportOf>
        </Report>
</ReportDirectory>

However, as is and without warranty, here is the batch file:
@Echo off

rem Apply %transformation%.xslt to all .rdl file in a directory
set transformation=ReportHierarchy
rem Directory containing the rdl files
set rdlDir=c:\projects\SSRSTools\
rem create a new file with an xml header
echo >%transformation%.xml
WriteXmlHeader.vbs %transformation%.xml
rem root element tag is same as transformation name
AppendOpeningTag %transformation% %transformation%.xml
for /f "delims=" %%d in ('dir /b /ad "%rdldir%\*"') do (
echo "%%d\*.rdl"
rem for everything in the directory, apply the transformation enclosing in <ReportDirectory></ReportDirectory> tags
AppendOpeningTag ReportDirectory %transformation%.xml
AppendName "%%d" %transformation%.xml
rem We use the /f flag to only return filenames. Our variable name will be “%%r”
rem “dir /b *.*” indicates a bare directory listing (no filesize etc. just filenames)
set tempfile=temp.xml
set fileTag=Report
for /f "delims=" %%r IN ('dir /b /a-d  "%rdldir%\%%d\*.rdl"') do (
rem echo "%%d\%%r"
   rem Append the transformation results, with suitable header and footer,
   rem while removing the xmlHeader that the transformation generated
AppendOpeningTag %fileTag% %transformation%.xml
AppendName %%r %transformation%.xml
msxsl.exe "%rdldir%\%%d\%%r" %transformation%.xslt -o %tempfile%
AppendWithoutXmlHeader.vbs %tempfile% %transformation%.xml
AppendClosingTag %fileTag% %transformation%.xml
)
rem tidy up temporary file
if exist %tempfile% Del %tempfile%

rem close directory tag
AppendClosingTag ReportDirectory %transformation%.xml
)
rem close root tag
AppendClosingTag %transformation% %transformation%.xml
echo
Echo %transformation%.xml created
And the extraction xslt file

<?xml version='1.0'?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:a="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition"
exclude-result-prefixes="a">
<xsl:output method="xml" encoding="utf-8" />
<xsl:template match="/">
<xsl:for-each select="a:Report/a:Body/a:ReportItems/a:Subreport">
<xsl:call-template name="subreport"/>
</xsl:for-each>
</xsl:template>
<xsl:template name="subreport">
<Subreport>
<Name>
<xsl:value-of select="@Name"/>
</Name> <ReportName>
<xsl:value-of select="a:ReportName"/>
</ReportName>
</Subreport>
</xsl:template>
</xsl:stylesheet>
Thanks to the person who posted the tip about dealing with the reportdefinition namespace at http://www.sqlservercentral.com/Forums/Topic564926-21-1.aspx


No comments:

Post a Comment

Note: only a member of this blog may post a comment.