I was reading Continuous Delivery by Jez Humble and David Farley, when I came across this quote regarding unit testing a UI (Chapter 4, pg. 88):
However, most UI testing tools take a naive approach that couples them tightly to the UI, with the result that when the UI changes even slightly, the tests break. This results in many false positives–tests that break not due to any problem with the application’s behavior, but rather because some checkbox has had its name changed.
There are several ways to solve this problem. One is to add an abstraction layer between your tests and your UI so as to reduce the amount of work required when the UI changes.
For a long time, I have had this nagging thought that, while I have done a good job creating automated tests for the database and the ETL, I have done nothing for SSRS. This has always been a manual process, both for testing the queries and the usability of the report. I put off automated unit tests, hoping that I would stumble across a good tool, one day, that I could throw an SSRS report to and generate repeatable tests.
My epiphany from this quote is that we can easily test the queries for each dataset in an SSRS report. These tests could be part of a database unit test project. If the query is complex, we could test that the logic is working properly. If it a straight select, we could simply test the column names and types to insure that a report does not break if the underlying database changes. What a great method for regression testing and very simple to implement. So my advice is this:
Write unit tests for the datasets
Use only a row of data for each test (to insure they run fast)
Test the metadata (db tests has the “expected schema” test condition, but this does not detect length of strings or differences between unicode or not)
Test the logic where applicable
For MDX, you can use openquery, note that the schema is not possible or worth testing because MDX is not strongly typed. Everything is returned as NTEXT in openquery.
Better that your datasets use procs, rather than inline SQL, so if the proc changes, the tests must change with it.