Testing SSRS Reports

Ken Ross included in business-intelligence testing

2013-07-22 932 words 5 minutes

Contents

In this post I’ll outline how I used a project from Codeplex http://unittestssrs.codeplex.com to write tests against SSRS reports.

From the description from the project page: Allows testing reports deployed to reporting server 2008R2 or 2012 in Native or SharePoint mode. The tests cases are created in xml file in declarative form.

Getting Started

1. Download and extract the latest release from the Codeplex site http://unittestssrs.codeplex.com

2. Open the Settings.xml file, in here you will need to set a few different options:

Path this is the path to the report server, it will probably look something like this: http://my-reportserver/ReportServer
Mode this can be Native or Sharepoint.
UserName and UserPassword these only need to be set if the report server requires authentication, otherwise you can keep the defaults
HttpClientCredentialType this will be either Windows or Ntlm

Configuring Tests

Test cases are written in the Settings.xml file. The basic structure is this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


<Settings>
  <ReportServer Path="[Path]" Mode="[Mode]" UserName="@UserName" UserPassword="@UserPassword" tpClientCredentialType="[CredentialType]">
    <Folder Name="[NameOfFolderOnReportServer]">
      <Report Name="[NameOfReportInReportFolder]">
        <Params>
          <Param Name="[ParamFromReport]" Value="[Value]" />
          <!-- More parameters can be specified -->
        </Params>
        <TestCases>
          <!-- Test cases go here -->
        </TestCases>
      </Report>
    </Folder>
  </ReportServer>
</Settings>

A few notes:

The settings for the attributes are outlined in the Getting Started section.
I had problems initially with my reports because there were a few in the same folder that had similar names, this caused the project to throw an exception. This I didn’t figure out until I downloaded the source code and stepped through line by line.

Setting up a sample report

At present the only supported test cases are to assert IsNotNull and AreEqual, however I noticed that the AreEqual assertion only works on numeric data. So forget trying to test if columns contain text, you can also only test data that appears in the body of the report. Which for most people probably isn’t going to pose any problems.

To write test cases you need to know or understand XPath, and how it relates to your SSRS reports.

All the reports that I’m testing use a Matrix (or Tablix as they are also known).

As an example I created a new report which connects to an SSAS cube and pulls out four measures, I then added a matrix to the report and dragged in the measures from my data set, here is what my report now looks like: I have highlighted the following: data set, the matrix/tablix in the design window and Row Groups and Column Groups. These are important to note when it comes to writing the XPath for the test cases.
The next step is to run the report, and then save the output as XML.
Then open the XML output If you take a close look you will see a node, this refers to the name that SSDT gave the Matrix when I added it to the report, you will also notice the <RowGroup_Collection> and <Column_Group> nodes which correspond to the highlighted sections in the first step. Using these XML output we can now write some tests!
Open the Settings.xml file from the SSRS Unit Test project, you should already have the node attributes configured, add a new node to the section. To test the report I’ve created for this post I added this:
Next we add a node, so we now have:

Now we are ready to add a

NB: You will also need to deploy the report to the report server and folder you specified in the Settings.xml file.

Writing Test Cases

This is where knowing a bit of XPath comes in handy, however we are helped a lot by having the report XML output.

Example: Checking that the Total matches the expected value (AreEqual):

Looking at the XML output I can see that Textbox14 corresponds to the field representing my Total. If I wanted to I could go back to SSDT and give this field a better name, but for now I’m happy keeping it as Textbox14. I also know from looking at the XML output that the value I expect to have returned is 3233.

So, I will add the following to the collection:

Assert=“AreEqual” tells SSRS Unit Test that we want to check the value of a field.
Path="//Tablix2/RowGroup_Collection/RowGroup[1]/ColumnGroup_Collection/ColumnGroup[1]/Textbox14/@Total" tells SSRS Unit Test how to traverse the XML to get the value of the field
Value=“3233” is the value that we are expecting.

Example: Checking that the Answered matches the expected value (AreEqual):

Next up we can do the same thing for the Answered field in the report, once again looking at the XML output we can take a guess that Textbox25 corresponds to the Answered field, so can add the following:

Example: Checking that Failed has a value (IsNotNull):

Lastly, we can test that the failed field has a value (IsNotNull), by once more looking at the XML output we can see that Textbox27 corresponds to the failed field. Our test case will look like this:

Test Output

We can now run SSRS Unit Test and if it is configured correctly, then it should produce a file called something like “TestSuite yyyy-mm-dd hh-mm-ss.xml”. This file looks a lot like the Settings.xml file, but with an extra field.

You will see highlighted in the picture above that a Passed attribute has been added to each which we defined in the Settings.xml file, this will either say True or False. Depending on if the test case passes.