When validating computer systems and applications an array of screenshots and other such supporting documentation is frequently used.

All too often, supporting information is simply stored in a database and is easily accessible, which means that the information can be extracted by use of an SQL query and presented in a user-friendly format with a reporting tool.

Documented Query

Obviously, from a quality perspective a documented assurance that the SQL is extracting the correct information consistently must be in place or in other words the SQL must be qualified. This is not a daunting process, this means that a test script must be assembled that utilises a documented query and that the results are verified; the supporting evidence attached to the test script, the testing can be carried out on a set of data that is not exhaustive and returning 1000s of rows of data, just something simple whose results can be confirmed to be correct and complete.

Reporting Tool

Once the SQL query has been produced to extract the required data, the SQL can be used as a data source in a reporting tool such as Crystal Reports and then the fields of data organised in a user-friendly (easy-on-the-eye). The SQL is executed as part of the report and voila, a nice easy-to-read set of well presented data.

When writing the SQL query, ensure that the query is structured as specifically as possible – writing a query to extract too much detail (e.g SELECT * FROM tblSales) when only a specific amount is required can impose what is sometimes as substantial strain on the system being queried, if this is a production system then the outcome can be disastrous.

Filters

In addition, reporting tools offer allsorts of various forms of filtering and sorting data – be careful not to leave filters in place in the reporting tool – this can cause the correct results to look truncated however this is something that would be usually be detected by whoever generates the report.

If these reports are to be used on an on-going basis, it makes sense to have a controlling SOP for the process of generating the reports – this is also highly recommended when dealing with cGMP systems.

Summary

In summary, SQL is an ideal data source for report tools to create many time-saving, valuable reports. Care should be taken not to place an unnecessary load on a production system. A controlling SOP should be in place to instruct the user on the initialisation and execution of the report in order to eliminate any user applied filters that are left over from the previous run of the report.