Registration

Dear SAP Community Member,
In order to fully benefit from what the SAP Community has to offer, please register at:
http://scn.sap.com
Thank you,
The SAP Community team.
Skip to end of metadata
Go to start of metadata

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:

 

 

 

 

 

Important Notes:

  • If Index Awareness is setup in the data source (or in HANA), do not set the Index Awareness in the UNX - this is redundant.
  • If the hierarchy is uneven (example - some objects will expand to 2 levels while other expand 3 or more levels), the key may need to be added to the objects at higher levels.
  • Adding a Primary Key will cause a GROUP BY statement to be added to optimize the query. See below to disable this if it causes problems.

 

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.

 

Screenshot of generated WebI SQL


 

 

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

11 Comments

  1. Voz is a fan too! Awesome stuff Carly!

  2. Former Member

    This is one heck of a write up.  Excellent work...!

  3. Hello, I have BO 4.1 SP 2 with Oracle 11gR2, I work with universes .unv I have the same problem, I do these steps but the problem was not resolve, will you remember if you were having another configuration?

    Thank you in advance!

  4.  

    Hi Sara,

    I apologize for the delay in responding, I have been out on maternity leave for several months.  This write up is for UNX and not UNV.  UNV does not have a true "cascading" LOV that UNX does.  Instead it uses nested LOVs and I am not sure that this is still supported as Development is focused on UNX and not UNV for BI 4.x.  If nested LOVs are still supported for UNV in BI 4.1, you would add a prompt for the parent object in the properties of the LOV object in the Universe.  There is also an option to create a cascading LOV in one of the menu options, which automatically generates the prompts in the child objects for you.

    I hope this helps!

    Carly

  5. Hello Carly,

     

    I've hope everything been ok and thanks for answering. In my comment, what I wanted to say is that I put the parameter "GROUPBY_PRIMARY_KEY" in the universe unv, and it doesnt work... I dont know if it is for the SP that I was, maybe with SP4 work better, I'll tell you soon, when we do the upgrade.

     

    Thanks,

     

     

  6. Former Member

     

    This is one amazing write up. thank you for posting.

    I have a question though

    when we create a cascading prompt like this, it doesnt allow us to enter the value manually though does it? it is missing a bar saying "Type a value:................................... box right around where the refresh button is.

  7. I agree, this is an excellent post. But I'm having a similar issue to what Sara is experiencing. I'm using BI 4.2 SP2 with SQL Server 2014 and while the cascading prompt from the IDT works perfectly, if I include a measure object (SUM(myObject), for example) in the WebI query, there is no GROUP BY clause generated. 

  8. Former Member

    Fantastic post!

    What is the trick for getting this to work in an opendoc link within Webi? I would like to use this cascading prompt in a source report, but also to be able to pass that to a target report via opendoc.

    It seems that, within Webi the hierarchical representation of the object is not accessible to be able to pass that to the target report (e.g. <level 1> \ <level 2>). Accessing the prompt value via PromptSummary() only gives the lowest level.

    Thanks in advance for any suggestions...

  9. Very detailed explanation. Thank You.  I understand about the INDEX AWARENESS. However I am not able to understand why system add MAX(EMP NAME). Is there any reason behind such behavior?