Page Navigation:

Purpose

The purpose of this WIKI page is to demonstrate how to create a cascading list of values (LOV) that has a hierarchical display (tree view) using Information Design Tool (IDT).

IDT has the capability to create cascading LOVs using nested LOVs similar to Universe Design Tool (UDT / Designer) for UNV type Universes, however, the display is flattened rather than using the hierarchical display.

Therefore, if a hierarchical display is desired, then the feature "Create List of Values based on custom hierarchy" needs to be used instead of nesting the LOV.

Overview

Below will cover step by step instructions with screenshots on how to setup a custom hierarchy for a List of Values (LOV) that can be used to display a cascading LOV in a tree.

The example used is based on Oracle's sample HR database "Scott" to build the UNX type Universe in IDT.

Creating a Custom List of Values

1. Login to Information Design Tool (IDT)
2. Open HR UNX Business Layer (HR.blx)
3. Expand the “Parameters and List of Values” tab

   

4. Click the icon for a new LOV
5. Select “List of values based on business layer objects”

   

6. Name the hierarchy (Employee Hierarchy)
7. Select “List of values based on a custom hierarchy”
8. Click “Add Dimension” to build the hierarchy

   

9. Select the objects in the order you want the hierarchy to appear

   

10. In this example, it will be Department, Location then Ename (this is the Employee)

    

11. Click Preview to ensure the display is correct

    

Assigning the new LOV to an Object

1. Expand “Business Layer” tab for the .blx
2. Since “Ename” is the bottom of the hierarchy, select “Ename” object to set the LOV
3. Click “Advanced” tab and then the … box to set the LOV

   

4. Select the hierarchy LOV that we created above (Employee Hierarchy in this example) and click OK

   

5. Right click “Ename” in object list & select “Preview List of Values” to ensure display is correct

   

6. Save & export the UNX

Using the LOV in a Web Intelligence Report

1. Create a new WebI report and add “Ename” to the query filters to view the cascading LOV

   

2. Run the query and note the prompt is in the desired hierarchical display
   

   

3. Click OK and the report is rendered, displaying only the value selected
   

   

Avoiding Concatenation when using Cascading LOVs by adding Index Awareness

When using LOVs based on customer hierarchy in queries, the SQL query generation concatenates the hierarchy level fields in the SQL script in order to guarantee the unicity of filter values.

This can be seen in the SQL generation from our example above (the concatenation is denoted by the pipes "|"):

This concatenation can cause additional load and less efficient SQL to be run against the data source.

To avoid this concatenation, the solution is to associate a PRIMARY KEY (or Index Awareness) on the level dimensions that the hierarchy LOV consists.

For example, let's add a Primary Key to the object “Ename” as follows in Information Design Tool:

1. Login to IDT and open the Business Layer of the UNX
2. Select the object that is in the lower level of the LOV (in this example, Ename)
3. On right hand display, select the "Keys" tab and select "Add Key"

   

4. This will add a primary key
5. Next select the "Select" column and add in the select statement to add a key
6. In our example using the HR sample database, add a select to Ename for the Eno which is the Employee Number in the database
   

   

   
   

   

7. Save and Export the UNX

The SQL Query generation in WebI will now use the index aware key and avoid concatenation:

And here is how it will look in Web Intelligence:

In The WebI Member Selector, click the Key icon to see the Primary Key that is set on the Ename object:

Preventing the MAX() function from being added to the SQL of the dimensions with a Primary Key set

When using Index Aware objects in queries with measures, the SQL query generation will use the associated Primary Keys in the GROUP BY clause in order to optimize the query.

This causes the MAX() function to be added on the object bindings.

To disable this default behavior, add and set the following Universe parameter in the Information Design Tool (IDT):
See below for step by step instructions
GROUPBY_PRIMARY_KEY = No

Instructions on Removing the MAX() function

1. Login to Information Design Tool (IDT) and open UNX Business Layer (.blx)
2. Highlight the UNX name (in this example, "HR") and in the Properties window on the right click the "Parameters" button
   

   

3. From the parameters drop down list, select GROUPBY_PRIMARY_KEY and click "Add"
   

   

4. Note that the parameter is added with the default value of "No" (if the parameter is not added here, the default behavior is if this parameter were set to Yes). Leave it set to "No" to disable the option
   

   

5. Click "OK" then save and publish the UNX (right click on the .blx and click "Publish to a repository"
6. When creating a WebI report on this UNX, the SQL will now generate without MAX() on the object the Primary Key is set
   

   

Related Content

Related Documents

KBA 1666504 - BI 4.x How to create a hierarchical cascading list of values in UNX universe from Information Design Tool

BI 4 SP04 Information Design Tool User Guide

The SAP Help Portal contains the latest BI 4 documentation

Vishal Dhir's Blog on Creating a Prompt with Hierarchical Display with HANA using IDT