I am looking for ideas for automating export tasks you would usually see in reports (SSRS SQL Server Reporting Services example - Export to PDF/Excel/CSV). What I have observed is when you export data, some portion of data is lost as it is not word wrapped. In actual printouts you would see data missing for some columns.

  • Are these usually manually verified tasks?
  • Has anyone tried automating - verify actual data in column to exported data in CSV/Excel/print preview word?
  • I have good number of reports to be verified (40 plus), automating them would save good amount time I believe.

Thanks in advance


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:

  1. the data is fetched from somewhere,
  2. values are converted into a form appropriate for the output format,
  3. the values are merged into an empty document of the appropriate format,
  4. 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.

  • It is SSRS (SQL Server Reporting Services), I am looking for it. – Siva Sep 12 '11 at 1:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.