Testing SSRS Reports

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:

<Settings>
  <ReportServer Path="[Path]" Mode="[Mode]" UserName="@UserName" UserPassword="@UserPassword" HttpClientCredentialType="[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:

  1. The settings for the <ReportServer> attributes are outlined in the Getting Started section.
  2. 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).

  1. 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:
    SSRS example report to test
    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.
  2. The next step is to run the report, and then save the output as XML.SSRS-Testing-2
  3. Then open the XML output SSRS XML Report OutputIf you take a close look you will see a <Tablix2> 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!
  4. Open the Settings.xml file from the SSRS Unit Test project, you should already have the <ReportServer> node attributes configured, add a new <Report> node to the <Folder> section. To test the report I’ve created for this post I added this:
    <Report Name="SummaryReport_ForAutomatedTests"></Report>
  5. Next we add a <TestCases> node, so we now have:
     <Report Name="SummaryReport_ForAutomatedTests"><TestCases></TestCases></Report>

Now we are ready to add a <TestCase>

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 <TestCases> collection:

<TestCase Assert="AreEqual" Path="//Tablix2/RowGroup_Collection/RowGroup[1]/ColumnGroup_Collection/ColumnGroup[1]/Textbox14/@Total" Value="3233" />
  • 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:

<TestCase Assert="AreEqual" Path="//Tablix2/RowGroup_Collection/RowGroup[1]/ColumnGroup_Collection/ColumnGroup[1]/Textbox25/@Answered" Value="1000" />

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:

<TestCase Assert="IsNotNull" Path="//Tablix2/RowGroup_Collection/RowGroup[1]/ColumnGroup_Collection/ColumnGroup[1]/Textbox27/@Failed" />

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.

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

There is also an xls transform that you can apply to the test results xml file, which produces a nice html file.

SSRS-Testing-6
SSRS-Testing-5

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s