Writing SQL Queries Against IBM Cognos Analytics Reports

Versions: 8.0+

With QuerySurge BI Tester for IBM Cognos Analytics, data testers can use familiar SQL syntax to retrieve query data from IBM Cognos Analytics reports and validate it against any QuerySurge-supported Source or Target. Cognos connections can be set up in the QuerySurge Admin view, and be utilized in the same manner as any other Connection types (i.e. in QueryPairs and Staging Queries, and Test Suite Connection overrides).

For details on setting up a Connection to Cognos, see the article Connecting QuerySurge to IBM Cognos Analytics.

Determining a Report's Search Path

To retrieve Cognos report data, the report must be identified using a search path that corresponds to the report's location in the Cognos content store. We will illustrate the form and syntax of search paths using the "Employee satisfaction" report:

The folder path (not the search path) of this report is: Team Content/Samples/Reports/Standard Reports/Employee Satisfaction

Using the known types of the path's components, we can transform this folder path into a search path for the Cognos content store:

/content/folder[@name='Samples']/folder[@name='Reports']/folder[@name='Standard reports']/report[@name='Employee Satisfaction']

Additional guidance on forming search paths can be found in the Cognos documentation on search path syntax.

Note: BI Tester for Cognos can only retrieve content store objects of type report; other objects (such as interactiveReport) are not supported.

Next, we need to find the names of queries contained within this report:

1. Click the Edit icon in the top toolbar
2. Click Queries in the left toolbar
3. Click on the query
4. Click the Properties icon in the top toolbar
5. Scroll down to Miscellaneous properties and copy the query's Name

To preview the base data returned by a query, right click it and choose View Tabular Data:

The combination of a search path and query name can be used to query the report with a QuerySurge BI Extract connection. Each search path/query name combination will be treated as separate tables in QueryPairs and Staging Queries, as explained below.

Querying Report Data

The query syntax for BI Extract queries is based on H2 SQL and supports most of the H2 SQL grammar and its function calls, so SELECT-type queries against Cognos follow a familiar form. The main element is the cognosReport table function, which instructs the BI Extract driver to execute the report and pull back the query data as a table for you to query. The function's syntax is as follows:

cognosReport('/searchpath','Query Name')

The function's arguments must be surrounded by single quotes, and single quotes within the argument values (i.e. quoted names in the search path) must be escaped with an additional single quote. Following is a sample SQL statement against the first query in our Employee Satisfaction report:

SELECT *
FROM cognosReport(
  '/content/folder[@name=''Samples'']/folder[@name=''Reports'']/folder[@name=''Standard reports'']/report[@name=''Employee Satisfaction'']',
  'Query - employee survey scores, targets, and benchmarks');

When executed in QuerySurge, the following results are returned:

Queries can include standard syntax like joins, group by, single-valued functions, aggregate functions, aliases, and more. Note that references to column names with spaces or special characters must be surrounded by double quotes:

SELECT
  rpt."Survey topic",
  rpt."Topic target score",
  rpt."Actual score",
  rpt."Survey score vs. Target score"
FROM cognosReport(
  '/content/folder[@name=''Samples'']/folder[@name=''Reports'']/folder[@name=''Standard reports'']/report[@name=''Employee Satisfaction'']',
  'Query - employee survey scores, targets, and benchmarks') AS rpt;

Passing Parameters to report

Many Cognos reports take advantage of parameter functionality, which allows filtering of report data for exploration and analysis, building one report for many users, and other use cases. To automate testing of these reports, the cognosReport function supports passing in a JSON-formatted parameter string:

[
  {
    "name": "Region_para",
    "values": [
      {
        "type": "Simple",
        "inclusive": true,
        "display": "Americas",
        "use": "[Employee expense].[Employee by region].[Employee by region].[Branch region]->[Employee by region].[710]"
      }
    ]
  }
]

The outer array can contain any number of parameter items, and individual each parameter item's values array may further contain any number of value items. Fields are defined as follows:

• name: Parameter name, as referenced in prompt pages or filter expressions
• type: Only supported value is "Simple", corresponding to a SimpleParmValueItem in the Cognos SDK (other types may be supported in the future)
• inclusive: Indicates whether, if true, to include data that contains this parameter value (akin to "WHERE col = 'myval'" in SQL), or if false, to exclude data (akin to "WHERE col <> 'myval'"). See the Cognos documentation on parameter value inclusivity.
• display: Label for the parameter value, as shown visually in the Cognos user interface
• use: Parameter value passed to queries

The parameter string must be minified (that is, formatted without line breaks, which can be accomplished using "JSON minifier" tools available on the internet, and in JSON libraries for most programming languages), and then passed in as a third parameter to the cognosReport function:

SELECT * FROM cognosReport(
'/content/folder[@name=''Samples'']/folder[@name=''Reports'']/folder[@name=''Standard reports'']/report[@name=''Employee expenses'']',
'Query_List_Expense',
'[{"name":"Region_para","values":
[{"type":"Simple","inclusive":true,"display":"Americas","use":"[Employee expense].[Employee by region].[Employee by region].[Branch region]->[Employee by region].[710]"}]}]');

Depending on the report and query structure, possible values for parameters may be found by browsing the dimensions of a Cognos dimensionally modeled relational (DMR) package, as show below. Parameters may also refer to numbers, dates, string or other data types; refer to Cognos documentation or a knowledgeable resource for details.

Querying Report Metadata

When developing data tests for Cognos reports, it can be useful to see information about the datasets returned by the report for learning about the columns available. Metadata can also be used to compare a report between two Cognos servers. To obtain metadata for a report, use the cognosMetadata function as follows:

SELECT *
FROM cognosMetadata(
  '/content/folder[@name=''Samples'']/folder[@name=''Reports'']/folder[@name=''Standard reports'']/report[@name=''Employee Satisfaction'']');

Note that cognosMetadata can only retrieve one report per metadata query, and it cannot be combined with a report data query (i.e. joins are not supported). This query returns the query names (as table names) and column names for all queries in the report (the example here is truncated for brevity):