I do not know which SSRS you are referring to (e.g. SQL Server Reporting Services, Social Science Research Solutions). Nonetheless, I like this question because it describes a common problem, it lends itself to more than one kind of automation, and it illustrates testability and the answer requires some analysis.
My approach would depend upon whether I knew how to automate the export action via either the UI or an API and how the software was organized.
Several processing steps take place during an export:
- the data is fetched from somewhere,
- values are converted into a form appropriate for the output format,
- the values are merged into an empty document of the appropriate format,
- a stream of bytes is emitted.
Of course one could decompose the action into even more steps, but the steps above are enough to illustrate my point. Similarly, the steps may be performed in parallel or serially, but for my purposes it does not matter.
Here are issues to consider before deciding on an automation strategy.
You do not need to use the same tool/technology for everything.
There are at least two actions you could automate: initiating the
export and analyzing the results. Some possible ways to initiate the
export include the UI, a command line interface, or an API. A UI
automation tool is unlikely to provide a way to analyze Excel or PDF
files. That is not an insurmountable problem. You can use a
different mechanism for file analysis. If you automate initiating the
export but not the analysis, or vice versa, you still will have saved
some time.
You need to choose whether to compare against canonical results or computed results.
One approach is to generate some exports and compare them to canonical
results (exports that you generated at some time in the past using the
same inputs.) Another approach is to calculate (on the fly) the
expected results for a given set of inputs, perform the export, and
then compare the export to your calculated, expected results. This is
more flexible but can be considerably more work. (This issue is not
specific to exports testing.)
It is possible but not easy to analyze the contents of an Excel or PDF.
Because of its simple syntax, a CSV is easy to parse and analyze. The
same cannot be said for Excel or PDF files. With the latter, you must
consider both content and appearance. A Google search should reveal
tools for converting an Excel file into a CSV. I do not know whether
there are similar tools for PDF files. In any case, practically
speaking, you need to eyeball an Excel file or PDF to determine whether
there are problems with its appearance.
You may be able to separate the content testing from appearance testing.
I once tested a complicated reporting system for a bank. There were
many types of reports, each with a variety of configurable options.
The data resided in a relational database, and as in your example the
export formats were CSV, Excel, and PDF. The developer wisely
separated querying the database from producing the output file. To
improve testability, he provided a test mode in which the raw queried
output could be dumped to a file. He also provided a way to generate
a CSV, Excel or PDF from a raw set of data.
Given these "testability" features, I could break the reporting system
testing into two independent tests: a test of whether a set of inputs
produced the right raw data., and a test of whether a set of raw data
could be converted into an CSV, Excel, and PDF correctly. I was able
to automate the first test. I also automated the CSV tests. The
Excel and PDF tests were still manual, but at least it was not
necessary for a human to eyeball an Excel and PDF of every report with
every report option.
