Auditing for Accurate Cognos Reports

All Cognos reports need to be checked to ensure the report produces accurate data. No report developer wants to discover months after the report is in production that business decisions were made based on an incorrect report. Don’t let this happen to you. Follow the these tips to eliminate any potential errors in your report.

Test Cases

Test cases are specifications of report inputs, such as database data and prompts, and expected report results for these inputs. Each report requirement should have at least one test case to verify the requirement functions as intended. Test cases are usually defined by the end user with the help of the report developer. The report developer will send the test cases to the end user for them to review and verify the report results fit the requirements.

Good Test Cases

The following situations make good test cases:

  • Baseline Inputs – The most common sets of report inputs. These are the inputs that will run repeatedly over the life of the report.
  • Special Report Functionality – Report Inputs that make use of special report functionality, such as render variables, conditional formatting, and grouping.
  • Business Logic – Any business logic developed for the report. Examples: Custom calculations for tax and shipping
  • Date Logic – Any special date logic used in the report. Examples: End of Fiscal Year, past historical years
  • One to Many Relationships – Any inputs that produce one to many relationships. Examples: Employees with multiple positions, students with multiple guardians, etc.
  • Many to Many Relationships – Any Input that produces many to many relationships.
  • Any Other Fringe Requirements – Any other inputs that the end user expects to cause issues due to previous experience. Sometimes these test cases require great effort to implement compared to the number of times the test case occurs. But, they should still be tested to observe the output.

Quality Assurance Checklist

These are the items the report developer needs to check for:

    • Report Outputs – Make sure the report is correct in the expected output.
    • Formatting – Any extreme examples of reporting, such as long names, multiple addresses, special characters, etc.
    • Prompts – Cascading prompts work properly. Verify all needed values are available. Test report with and without optional prompts in various combinations.
    • Drill Down Functionality – Test to make sure the drill down works as expected.
    • Schedules/Triggers – Make sure the report is correct when it is run on a specific schedule/trigger.
    • Browser Compatibility – Verify the report works properly in any browser that the end user may use.
    • Duplicated Results – Summary calculations may be counted multiple times.
  • Null Values – What happens with certain fields are null?

Audit Tools in Cognos

Cognos Analytics has various tools to assist in auditing your report results:

Tabular Data SQL

To pull the SQL run by Cognos’ Tabular Data functionality, go to the query’s properties. Click the More button (…)  for Generated SQL to access the SQL.

A warning will pop up. It is a disclaimer stating the limitations of a Tabular Data report. Click OK.

There are two views for the SQL. One is the Native SQL. This SQL will run on the database without any changes to the syntax.

Then there is the IBM Cognos SQL. This SQL is easier to read but may throw errors if run on the database.

Tabular Data

To access the Tabular Data of a query, right click on the query name in the Queries tool drawer. In the context menu select View Tabular Data.

A warning will pop up. It is a disclaimer stating the limitations of a Tabular Data report.

The Tabular Report displays all the queries items in a single list.

It will not include any grouping or sorting applied in the report. However, the data is grouped by the data items in the query if the Auto Group and Summarize property is set to Yes. You can turn off the Auto Group and Summarize property to audit for doubled data results.

To export the Tabular Data out to Excel or PDF, click the down arrow next to the Run Button and choose the desired format.

Generated SQL/MDX

For more complex reports, you will need to access the Generated SQL/MDX. For simpler reports, this query will be the same as the Tabular Query.

In the Queries drawer, right click on the Report icon. In the context menu, select Show generated SQL/MDX.

It will list all the queries run by Cognos to produce the Report Results.

Test Reference Queries in Cognos

You can reference other queries and run audits on the results.

This example counts the distinct number of requisition dates in Query 1.

Viewing the Tabular data for the Audit query will give you the result of it.

Other Audit Tips

  • If your report is pulling from an application’s transaction data, you can compare results to original application’s data.
  • Write SQL statements recreating the Business Logic used in the report. Compare those results to the Cognos report.
  • For calculations, you export the tabular data to Excel. There you can recreate the calculation and verify the report is correct.

For more information contact