Writing SQL Queries Against SAP Business Objects WebIntelligence Visualization Elements

Versions: 8.0+

Versions:6.3+

With QuerySurge BI Tester for SAP Business Objects WebIntelligence, data testers can use familiar SQL syntax to retrieve query data from WebIntelligence reports and validate it against any QuerySurge-supported Source or Target.

WebIntelligence reports have multiple processing levels, and data can be retrieved independently from these different levels. Two key levels for report data verification are the report data providers and the report visualization elements. In this article we discuss how to extract data from report visualization elements. A WebIntelligence report can have one or multiple visualization elements, which display the data supplied by the report data providers.

Note: To extract data from WebIntelligence report data providers, see Writing SQL Queries Against SAP Business Objects WebIntelligence Data Providers

Connection Setup 

WebIntelligence 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 WebIntelligence, see the article Connecting QuerySurge to SAP Business Objects WebIntelligence.

Determining a report's Document ID

To retrieve WebIntelligence element data, a document must be identified using its Document ID number. To find the Document ID for a WebIntelligence document:

1. Log into the SAP Business Objects BI Launch Pad, and click the Documents tab
2. Open the folder that contains the desired WebIntelligence document
3. Right click the document and click Properties
4. Note the numeric ID number (first number in the "ID, CUID" field)

Note: BI Tester for WebIntelligence can only retrieve WebIntelligence documents; other objects (such as Crystal Reports) are not supported.

Determining a report's Report Name(s)

The next piece of information needed is the report which contains the element which we would like to extract. Reports are shown as tabs at the bottom of a document. To find a reports name see the below steps:

1. Open the WebIntelligence document and enter Design mode
2. Note reports name. This can be copied directly out of the UI by right-clicking and selecting rename. 

Determining a report's Element Name(s)

The last piece of information required is the name of the element. To find an elements name see the below steps:

1. Open the WebIntelligence document and enter Design mode
2. Select the chart or table element which the data will be extracted from
3. Right click and select Format Chart, Format Table...
4. Note the chart/tables name

The combination of a document ID, report name and element name can be used to query the report with a QuerySurge BI Tester connection. Each document ID/report name/element name combination will be treated as a separate table in QueryPairs and Staging Queries, as explained below.

Querying Report Data

The query syntax for BI Tester queries is based on H2 SQL and supports most of the H2 SQL grammar and its function calls, so SELECT-type queries against WebIntelligence follow a familiar form. The main element is the webiReportv2 table function, which instructs the BI Tester 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:

webiReportv2(documentId, 'reportName', 'elementName')

• documentId (integer): ID found in document's properties
• reportName (string): Name of report containing the element 
• elementName (string): Name of element to extract data from

The reportName and elementName string argument must be surrounded by single quotes, and single quotes within the argument value (i.e. apostrophes within a report name) must be escaped with an additional single quote. Following is a sample SQL statement against the our Sales Analysis Dashboard report:

SELECT * FROM webiReportv2(5608, 'Sales Analysis', 'Pareto QS');

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."City",
  rpt."Quantity sold",
  rpt."Pareto line",
  rpt."Pareto 80%"
webiReportv2(5608, 'Sales Analysis', 'Pareto QS') AS rpt;

Passing Query Filter Parameters

Many WebIntelligence 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 webiReportv2 function supports passing in a JSON-formatted parameter string:

webiReportv2(documentId, 'reportName', 'elementName', paramsJson)

Where paramsJson is a JSON array of parameter objects:

[
  {
    "queryName": "Order List",
    "paramName": "Start Date",
    "values": [ { "value": "2005-01-01T00:00:00.000Z" } ]
  },
  {
    "queryName": "Order List",
    "paramName": "End Date",
    "values": [ { "value": "2007-09-28T00:00:00.000Z" } ]
  },
  {
    "queryName": "Order List",
    "paramName": "Country",
    "values": [ { "value": "US" } ]
  },
  {
    "queryName": "Order List",
    "paramName": "Region Name",
    "values": [ { "value": "Idaho", "path": "[[0,\ US]]" } ]
  },
  {
    "queryName": "Members By Join Year",
    "paramName": "Join Year",
    "values": [
      { "value": "2005" },
      { "value": "2006" },
    ]
  }
]

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

• queryName: Name of the query the specifies the parameter prompt
• paramName: Parameter name, as visible in Query Filters pane
• value: Textual, numeric, or date/time (see formatting convention below) value to be answered for the parameter prompt
• path (Optional): Used for some hierarchical parameters; see explanation below

The parameter string must be minified (that is, formatted without line breaks, for which there are "JSON minifier" tools available on the internet), and then passed in as a fourth argument to the webiReportv2 function:

SELECT *
FROM webiReportv2(6629, 'Report 1', 'Block 1',
'[{"queryName":"Order List","paramName":"Start Date","values":[{"value":"2005-01-01T00:00:00.000Z"}]},{"queryName":"Order List","paramName":"End Date","values":[{"value":"2007-09-28T00:00:00.000Z"}]},{"queryName":"Order List","paramName":"Country","values":[{"value":"US"}]},{"queryName":"Order List","paramName":"Region Name","values":[{"value":"Idaho","path":"[[0,\ US]]"}]},{"queryName":"Members By Join Year","paramName":"Join Year","values":[{"value":"2005"},{"value":"2006"}]}]');

Date/Time parameter formatting

Date/time values used in parameters must be formatted using the following convention:

yyyy-MM-ddThh:mm:ss(.s+)(zzzzzz|Z)

The following table describes each format code:

Character	Description	Optional
yyyy	A four digit that represents the year
-	Separators between parts of the date portion
MM	A two-digit numeral that represents the month
dd	A two-digit numeral that represents the day
T	A separator indicating that time-of-day follows
hh	A two-digit numeral that represents the hour
:	A separator between parts of the time-of-day portion
mm	A two-digit numeral that represents the minute
ss	A two-integer-digit numeral that represents the whole seconds
.s+	The fractional seconds preceded by a dot separator	Optional
zzzzzz	zzzzzz represents the timezone according to the W3C recommendation. zzzzzz is of the form +|-	Optional
Z	The zero-length duration timezone, which is the UTC canonical representation	Optional

Source: SAP BusinessObjects RESTful Web Service SDK User Guide, section 6.3

Examples:

• No offset (timezone of server utilized): 2017-12-31T22:59:59.999
• Time with explicit UTC timezone offset: 2017-12-31T22:59:59.999+01:00
• Zulu time at UTC: 2017-12-31T23:59:59.999Z

Hierarchical Parameter Values

Hierarchical parameters allows "drilling down" into logical hierarchies of the business data and offer more flexibility in filtering reports. For some hierarchical parameters, WebIntelligence requires the path of hierarchy leading to the final parameter value. This can be accomplished by adding a path attribute to the value item:

{ "value": "value text", "path": "[[type,\ level,\ index],...]" }

With the following path components:

• type: Data type, represented by either a number or name:
  • 0 (or String)
  • 1 (or Date)
  • 2 (or Numeric)
• level: Value of the parameter at the particular level in the hierarchy
• index: Optional; included if the hierarchy has indexed levels

In our example above, the path to the Region Name "Idaho" is through the Country "US":

{ "value": "Idaho", "path": "[[0,\ US]]" }

Querying Report Metadata

When developing data tests for WebIntelligence 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 WebIntelligence servers. To obtain metadata for a report, use the webiMetadatav2 function as follows:

SELECT * FROM webiMetadatav2(5608, 'Sales Analysis');

Note that webiMetadatav2 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 report and element names (as table names) and column names for all elements in the report:

Limitations 

There are two main limitations to the current QuerySurge SAP Business Objects WebIntelligence Element extractor. The first is that Extraction of elements are limited to reports which do not contain Sections. Sections are visible on the navigation map and provide a filtered approach for viewing data in a report. An example on how to identify sections is below. 

The second item not currently supported by QuerySurges BI extractor is drill-able report entities. While QuerySurge can extract the base information, you will not be able to extract the drill down data.