How to automate tests for reports in SSRS

Question

My current project uses SSRS as the reporting engine. I have the report executing a text command with about 10 variables passed to it. I have been looking for a solution on how to unit test (acceptance or integration, whatever, just some automation) to an SSRS report project.

I thought about using this method:

1. Move the SQL text command to a stored procedure
2. Create a Reports.Tests project
3. Create a project_tests database and point the connection string to this database
4. Set up my unit test <Setup> with the data I would return in the test
5. Create a test with the parameters passed and test the result set

I wanted to be sure that I wasn't missing something obvious.

How do other programmers test their reports automatically? Any suggestions on how to improve this method?

EDIT: I should clarify that I would like to only test the result set and not formatting of the report.

Answer 1

Well that would only test the database 'API', it wouldn't take in to account any of the features you might be using in the report (filtering, sorting, aggregation etc).

The only thing I can suggest is to find a tool that can compare XML and just get the report server to send an XML version of the report.

http://reportserver/ReportServer?/directory/Report1&rs:Format=XML&rs:Command=Render

There must be a tool out there that can do this

Not sure if this has the capability to do it: http://www.soapui.org/

Answer 2

Do acceptance/smoke tests with watin or selenium rc with your favorite unit testing framework.

Answer 3

Selenium has been a great tool for me. Selenium should allow you to validate most of the functionality in the report works as expected from the viewpoint of a web browsing user.

Having a test database separate from production will help keep things clean in your environment, but won't affect the quality of the test.

Answer 4

So you are basically wanting to test the SP then?

http://tsqlunit.sourceforge.net/

or VSTS for Database Professionals

http://msdn.microsoft.com/en-us/library/bb381703(VS.80).aspx