Using the sample, PivotTree_Sample_esm.bqy, from Sections, select Sales PT Query. This query is a union of four separate query requests where each query has the same column names. In Sales PT Query 1, Item Type is added, and then the remaining hierarchical columns are added as NULL columns. The Units column, for example, becomes the Sum of Units for Item Type. See Table 23 for the column names and values.
Table 23. Sales PT Query 1 Column Names and Values
Column Name | Column Values |
|---|---|
Item Type | Item Type |
Store Type | NULL |
OS | NULL |
State | NULL |
Sum(Units) | Sum(Units) |
Sum(Amount) | Sum(Amount) |
In Sales PT Query 2, Store Type is added as the next hierarchical level, it contains data rather than being a NULL column. The remaining columns are again added as NULL columns. The Sum of Units column, for example, becomes the Sum of Units for Item Type and Store Type. See Table 24 for the column names and values.
Table 24. Sales PT Query 2 Column Names and Values
Column Name | Column Values |
|---|---|
Item Type | Item Type |
Store Type | Store Type |
OS | NULL |
State | NULL |
Sum(Units) | Sum(Units) |
Sum(Amount) | Sum(Amount) |
To create a union of queries:
In Interactive Reporting Studio, open a dashboard that contains the Pivot Trees component, and select Insert, then New Query.
If prompted, select the database connection file.
For example, select Sample.oce, and click OK at the user name and password login without entering any information.
From catalog, expand Tables, and drag tables onto the content area.
Optional: Create links between the tables, if not automatically linked.
Select topics or columns and drag them onto the Request line.
Select Query, then Append Query to create the union.
A set of query tabs is displayed beneath the Request line.
Optional: Select a query tab, right-click, and select Rename.
Select topics or columns and drag them onto the Request line for the appended queries.
From the Interactive Reporting Studio toolbar, select Process.
The results are displayed.
Select Insert, then New Table and drag all columns to the Content pane.