Hierarchical Display of Calculated and Restricted Key Figures in BEx Query Designer

[stefan_witteck3]

Calculated (CKF) and restricted (RKF) key figures are often used in BW mainly for re-usability and maintenance reasons. However, the handling in the Query Designer is not optimal in case of huge  number of CKF or RKF. Finding a certain CKF/RKF can be tedious and very time consuming. Using the sorting might help, but typically only if  the naming supports it. The following screenshot shows the sorting in the BEx key figure section:

The highest number of CKF/RKF on a single MultiProvider I have seen was more than 1.400. Here also the sorting is not really helpful anymore.

A way of supporting a high(er) number and structuring of CKF/RKF is available since the following note (available as of BW 7.3 SP8) was introduced:

1648317 - Hierarchical display of RKF/CKF in Query Designer

It delivers the transaction RSZTREE, which is typically used to maintain a hierarchical structure in the backend. The note also explains how to use it. The following example demonstrates this based on the business content MultiProvider 0RMA_MP01. After activating the content and opening the Query Designer the CKF section there will look like this:

Call now the transaction RSZTREE in the back-end and enter the InfoProvider there:

The CKF/RKF are displayed in the same structure from the Query Designer. Individual grouping typically starts with building up the hierarchy and then assigning the CKF/RKF to the corresponding nodes. The finalized example looks like this:

Re-opening the Query Designer for the corresponding InfoProvider does look like this:

There are a few smaller downsides. For instance the missing integration into the Query Designer or the not always intuitive behavior of the RSZTREE transaction. Nevertheless, this functionality is useful to structure CKF/RKF more user friendly in the query designer.

Finally, some additional information/thoughts, which might be beneficial using this functionality:

• The hierarchy maintained in RSZTREE is transported together with the InfoProvider.
• The hierarchy information is stored in the table RSZCOMPTREE and RSZCOMPTREETXT.
• The assignment of a single CKF/RKF is stored in the table RSZCOMPIC, field FOLDER. The information is always transported together with CKF/RKF.
• If a CKF/RKF with hierarchy assignment is transported but the hierarchy information is not available in the target, the information is ignored.
• Top nodes Calculated key figure and Restricted key figure cannot be changed