Monday, 12 January 2015

Dependency inversion

Yesterdays post showed how to get all the reports with all the subreports

Here's the transform to change this to all subreports with their calling reports
as well as all reports that are not subreports at all.

<?xml version='1.0'?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
>
<xsl:output method="xml" encoding="utf-8" />
<xsl:key name="subreports-by-name" match="/ReportHierarchy/ReportDirectory/Report/Subreport" use="Name"/>
<xsl:template match="/">
<ReportDependencies>
<Subreport>
<Name>
<xsl:text>None</xsl:text>
</Name>
<xsl:for-each select="/ReportHierarchy/ReportDirectory/Report[generate-id() != generate-id(key('subreports-by-name', Name)[1])]">
<xsl:sort select="Name" />
<Report>
<Name>
<xsl:value-of select="Name" />
</Name>
<ReportDirectory>
<xsl:value-of select="../Name"/>
</ReportDirectory>
</Report>
</xsl:for-each>
</Subreport>
<xsl:for-each  select="/ReportHierarchy/ReportDirectory/Report/Subreport [count(. | key('subreports-by-name', Name)[1]) = 1]">
<xsl:sort select="Name" />
<Subreport>
<Name>
<xsl:value-of select="Name"/>
</Name>
<xsl:value-of select="surname" />
<xsl:for-each select="key('subreports-by-name', Name)">
<xsl:sort select="../Name" />
<Report>
<Name>
<xsl:value-of select="../Name" />
</Name>
<ReportDirectory>
<xsl:value-of select="../../Name"/>
</ReportDirectory>
</Report>
</xsl:for-each>
</Subreport>
</xsl:for-each>
</ReportDependencies>
</xsl:template>
</xsl:stylesheet>

And the sample output....
<?xml version="1.0" encoding="utf-8"?>
<ReportDependencies>
<Subreport>
<Name>None</Name>
<Report>
<Name>AdventureWorks2008R2_Base.rdl</Name>
<ReportDirectory>AdventureWorks 2008R2</ReportDirectory>
</Report>
        </Subreport>
<Subreport>
<Name>subreportDT1</Name>
<Report>
<Name>FinishingPackNetSpeedForGroup1.rdl</Name>
<ReportDirectory>SsrsReportDocumentation</ReportDirectory>
</Report>
<Report>
<Name>FinishingPackNetSpeedForGroup2.rdl</Name>
<ReportDirectory>SsrsReportDocumentation</ReportDirectory>
</Report>
</Subreport>
</ReportDependencies> 

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


Travelling light with SSRS



The BI team has been reviewing what tools are available for SQL Server Reporting Services. We want to leave a light footprint on our customer sites - and don't assume that anyone maintaining the system has the same choice of tools..

While we're developing, we can use more or less what tools we wish. However, we don't want to deploy any extra dependencies, only SSRS native code and .rdl code. And we don't want a steep learning curve for new people - in the early days they should be focussing on the customer and their business.

Preliminary inspection
Our first task is to see what assets we have already:-

What have we got in the source code? And how up to date is it? Open a command window,
DIR /S *.*>sourceYYYYMMDD.log will do it.

What has already been deployed? Open the report manager in a browser (as administrator)
e.g localhost:80/Reports, switch to detailed view and take a look.

And will it build? The bones of an automated script are something like this...
set today=%DATE:~6,4%%DATE:~3,2%%DATE:~0,2%

"%ProgramFiles(x86)%\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe" /Rebuild Release /out "..\logs\Build%today%.txt" "..\AdventureWorks 2008R2.sln"

Are the source code and deployed code synchronised? If the dates don't look right, or maybe even if they do, there's an article and download at http://www.codeproject.com/Articles/339744/SSRS-Downloading-RDL-Files.

Report Structure

http://ssrsreportdocumentation.codeplex.com/ contains a report which looks at the SSRS database. Basically, the tool says  'tell me as much as you can about reports which have already been deployed'.
I've made one tweak to it, to make a cut down copy which doesn't give the execution log details, and which we can safely run on legacy projects before we have a feel for data volume.

I've also put together an early version of a static analysis tool for the source code. It uses XSLT on the report document language (.rdl) files to identify reports and subreports. Yes, you could achieve the same thing using manual search tools such as those within BIDS or  Notepad++, but it involves a lot of pointing and clicking. To be a separate post on this blog. (Villagers - any ideas for a good name for this tool?)

Pre-release Checks

What can we do to avoid shooting ourselves in the foot?

The simplest thing is to preview from BIDS, and eyeball the output to compare with your change request.

Does is build without errors? The overlapping items warning can indicate problems in one export formats without showing in another export format.

Can you export to CSV or Excel without errors?  DIV/0 is a common problem that doesn't always show on the preview window.

A fairly simple and lightweight approach is to preview from bids, save as xml, and use a diff tool to compare with the current live copy. (That assumes their data sources point at the same database. If not, you can preserve the live copy with a name like .old.rdl till the deployment is approved).

The disadvantage is that you don't get the layout changes. So if you've caught a text box with your mouse and moved it a fraction, that can be hard to spot. You would need to compare the (mime format) mhtml from live and pre-release versions, so needing an extra tool to decode it.  I believe that http://rsunit.codeplex.com/ has tackled the extraction from mhtml to html.

Which brings us to unit test tools. http://rsunit.codeplex.com/ includes integration tests as well as unit tests.  However I haven't used it in production yet. And in the past I have found (with other reporting tools) that it takes a long time if a selection of reports is included in an integration testing run.

One thing I would like to do soon is to 'round up the usual suspects'. The rogues' gallery in Europe includes: forgetting to change the default language 'En-us'; and leaving the default page size of 'letter'. The static analysis tool for .rdl files can pull out the tag (or absence of such a tag) easily into a single xml file. This gives us the possibility of a XSLT transform to present the results in Unit Test format (e.g. Nunit or Microsoft Test Result format).

http://vmdp.blogspot.co.uk/2010/10/testing-steps-for-ssrs.html gives a usual suspect list from the perspective of a web designer.

Deploying SSRS.

The simplest, but most dangerous approach is point and click from BIDS, after setting project deployment properties.

A command line approach allows pre-deployment check, but only if the quality checks have been completed on the whole project, not individual files.

Report Manager allows individual reports to be uploaded. Some of our customers use this approach successfully. You just have to watch that any report is uploaded if its subreports have changed.

The endgame, as it is for all project types, is Continuous Integration. There are a couple of open source tools using the Soap API, to be considered once the pre-release quality checks have been implemented.

Monitoring after deployment
Usefully, SSRS includes various views of the Execution Log, which can be interrogated with SQL.  Care is needed on busy systems to look only at recent events. An example blog post is
http://blog.thoughtstuff.co.uk/2011/01/sql-server-reporting-services-recently-executed-modified-reports/

There is a good-looking tool called SCRUBS which takes an ETL (Extract/Transform/Load) approach to provide a long term view of the SSRS data. However, it's an installed component, and couldn't be left on a customer site without regular support.