cancel
Showing results for 
Search instead for 
Did you mean: 

BEx query: CO-PA data and hierarchy

Former Member
0 Kudos

Hi BEx fans

We need to design a report in BEx.

There is a hierarchy in play, with let us say 2 levels. 2 Key figures are determined.

Hierarchy Level 1Hierarchy Level 2Key figure 1Key figure 2
Commercial Business150150
Mix010050
Mix150100
Non Commercial Business100100
Mix1100100

Regarding the key figures let us explain it more detailled, they are designed depending on hierachy level values:

Key figure 1 should be be restricted by Mix0 and Mix1 (hierarchy level 2). On the other way it should also be restricted on Mix1 (level 2).

I have defined a query which doubles the keyfigures, instead of 2 there are at all 4 keyfigures. Executing the query you will see the 4 keyfigures correctly in a line (row area).

If you add the charateristic with collapsed hierarchy you will also see the these key figures correctly, but if you extract the hierarchy level from 1 to 2, you will recognize that the doubled keyfigures will be shown (with no values -> correct).

Question: Do you know a way how to eliminate these "duplicate" key figure rows when using the hierarchy? Is there another way to solve this problem?

Thanks

T.

Accepted Solutions (1)

Accepted Solutions (1)

former_member223480
Active Contributor
0 Kudos

Hi Thomas

Is it possible to send a grid with the result and what you want to achieve?

Thank you

Yiannis

Former Member
0 Kudos

Hi Yiannis

Attached find an example.

Level 1 is "Business". Level 2 are "Commercial" and "Non Commercial".

Level 3 are the values as described above. These values are different for the keyfigures.

In the grid you can see that for "Commercial" KFG3 and KFG4 which belong to "Non-commercial" automatically are shown, correctly with 0.

The same for KFG1 and KFG2, shown under "Commercial" with values and under "Non-Commercials" without values.

Do you understand where the problem is?

former_member223480
Active Contributor
0 Kudos

Yes Thomas this is normal,I assume that you have restricted with only level 3, you need to restrict also level 2 in your key figures.

You do not want that?

Thank you

Former Member
0 Kudos

Thanks Yiannis

Yes, you are right, the key figures are restricted by level 3.

Level 1 and Level 2 are text nodes and level 3 is "real" value of the characteristic:

  Level 1 -> text

    Level 2 -> text

      Level 3 -> characteristic value

How can you restrict the keyfigure on level 2 if this is a text node?

Thanks

T.

former_member223480
Active Contributor
0 Kudos

If i am not wrong you can this is not a problem, hierarchy is based on a icharacteristic then restrict based on the hierarchy nodes.

The problem is that you can't use it twice in the report. If you do not want it for display then this is not a problem. If you want to keep it in the report then you have to create a new characteristic.

Thank you

Former Member
0 Kudos

How do you mean this " If you want to keep it in the report then you have to create a new characteristic"?

Do you want me to make a new characteristic for Level 2?

Level 1 -> text 

    Level 2 -> characeristic value of characteristic no. 2

      Level 3 -> characteristic value of origin charateristic

Former Member
0 Kudos

Hi Yiannis

I have created a second charateristic which determines the logic of level 2 from the hierarchy characteristic (characteristic no. 1).

Then I additionally restricted the key figures with the relevant values of character no 2.

Unfortunately it doesn't work, still the same effect.

This is strange, as it is not really logic.

In the screen you can see KFG1 and KFG2, next column the new characteristic.

KFG1 is restricted additonally on value 1 of this new characteristic.

As you can see, the values 2, 3 and 4 are still displayed.

Do you know the reason for this?

Thanks

T.

Former Member
0 Kudos

There is a workaround:

Zero suppression (active values =0), apply to rows.

In workbook you can navigate to local query properties, and additionally are able to

-> apply also to members of structure key figures

-> apply also to members of structure struct.

This is only locally. Is there a chance to get these properties globally?

Former Member
0 Kudos

Problem solved:

The query consists of two structures.

The structure containing the key figures have properties for suppressing zeros. Deactivate this flag and you will get the right result.

Now it works.

Anyway, thanks a lot for your replies...full points.

former_member223480
Active Contributor
0 Kudos

Hi Thomas

If you have something like the image below on the rows then select the key figures structure and uncheck the zero suppresion.

My update came slower than yours, so you found it! Great

Answers (0)