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:

Log into the SAP Business Objects BI Launch Pad, and click the Documents tab
Open the folder that contains the desired WebIntelligence document
Right click the document and click Properties
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:

Open the WebIntelligence document and enter Design mode
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:

Open the WebIntelligence document and enter Design mode
Select the chart or table element which the data will be extracted from
Right click and select Format Chart, Format Table...
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');

If the report accepts parameters, one can be provided as follows:

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

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:

Reports with sections (Version 14.2+)

Sections allow for grouping of data for all visualizations within a report, the below example shows how a report with sections would appear in SAP.

Report with Sections

To query a visualization contained within a section, you will need to specify the section in your query. This section information is contained as JSON and follows the following format:

{"sections": {"section": "2014", "subSection": {"section": "Q3"}}}

Sample Section JSON for retrieving data from a visualization in Section 2014, Subsection Q3.

Note: Section details should not be inputted with any additional formatting such as new lines.

Details on the section fields are defined as below:

sections: marks the start of the json with section info.

section: the section header a visualization is part of.

subSection: (optional) a nested section. SAP WebIntelligence Reports allow for nesting sections within a section.

Once the section information has been extracted, this JSON object is passed as the last option of the webiReportv2 function.

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

The query below retrieves data for the Bar visualization, grouped under the outer section 2018 and the nested section Q2.

SELECT * FROM webiReportv2(6826,'Quarter Analysis', 'Bar', '', '{"sections":{"section":"2018","subsection":{"section":"Q2"}}}')

Section Metadata

To facilitate query development on reports containing sections, the QuerySurge BI Driver provides the ability to retrieve all sections associated with a given visualization. This is accomplished through the webiSectionMetadata function:

select * from webiSectionMetadata(6826, 'Quarter Analysis', 'Bar')

The above query produces the following result:

Columns 'report' and 'element' correspond to the report and visualization names provided in the query respectively. The 'section' column lists all the possible sections this visualization is grouped under.

Section Filtering

The QuerySurge BI Driver enables users to limit which section values are scanned during query execution. This helps exclude unnecessary sections from analysis and can improve query performance.

This behavior is controlled by providing limit and offset parameters, where:

limit (integer): specifies the maximum number of sections the can be fetched. Default is 50 and max allowed is 50.

offset (integer): specifies an offset into the list of sections (0-based). The default value is 0, which corresponds to the beginning of the grouped data. There is no upper limit.

Section filters are provided as an array, since a Web Intelligence Report may contain multiple nested sections.

'[[limit₁, offset₁], [limit₂, offset₂], ...]'

Where:

limit₁ and offset₁ define how many sections are scanned in the outermost level.
limit₂ and offset₂ apply to the next nested level, and so on for deeper sections.

You can provide one or more values in the array. If a nested section does not have its own value specified, it will automatically inherit the values from its parent section.

Note: Section filters are optional. If they are not provided the default values apply when querying data.

The array of filter values can be supplied to both webiReportv2 as well as webiSectionMetadata functions.

Applying section filtering to webiReportv2 :

SELECT * FROM webiReportv2(6826  /* document Id */
  ,'Quarter Analysis'            /* report name */
  ,'Bar'                         /* visualization name */
  , ''                           /* parameter json */
  ,'{"sections":{"section":"2018","subsection":{"section":"Q2"}}}' /* section json */
  ,'[[10,2], [2,0]]'            /* section filter */
);

The query above will scan 10 outer sections, beginning at the 3rd section (0-based index).
For each of those outer sections, it will scan the first 2 inner sections, starting from the beginning.

Applying section filtering to webiSectionMetadata :

select * from webiSectionMetadata(6826 /* document Id */
  ,'Quarter Analysis'                  /* report name */
  ,'Bar'                               /* visualization name */
  ,''                                  /* parameter json */
  ,'[[50,4]]'                          /* section filter */
);

The above query will scan 10 sections starting at the 5th index.

Note: For webiSectionMetadata function if Section filters are provided then parameter json must also be provided. If report does not accept parameter then pass an empty string ('') as shown in the example above

Limitation

QuerySurges BI extractor does not support drill-able report entities. While QuerySurge can extract the base information, you will not be able to extract the drill down data.

Articles in this section

Writing SQL Queries Against SAP Business Objects WebIntelligence Visualization Elements (Versions: 6.3+)

Connection Setup

Determining a report's Document ID

Determining a report's Report Name(s)

Determining a report's Element Name(s)

Querying Report Data

Passing Query Filter Parameters

Date/Time parameter formatting

Hierarchical Parameter Values

Querying Report Metadata

Reports with sections (Version 14.2+)

Section Metadata

Limitation

Articles in this section

Connection Setup

Determining a report's Document ID

Determining a report's Report Name(s)

Determining a report's Element Name(s)

Querying Report Data

Passing Query Filter Parameters

Date/Time parameter formatting

Hierarchical Parameter Values

Querying Report Metadata

Reports with sections (Version 14.2+)

Section Metadata

Limitation

Related articles