Sunday, 11 January 2015

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

Report Structure 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 has tackled the extraction from mhtml to html.

Which brings us to unit test tools. 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). 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

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.

No comments:

Post a Comment

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