SAP BPC 10.0 NW STANDARD : Reporting based on Multiple hierrachy in 1 dimension

[former_member618262]

Hello Experts

We have a requirement to show Industry and Country segment details as part of Profit center hierarchy .

We have created a Profit center dimension and 2 hierarchy viz Industry and Country :

However, users wants to have multiple selection within the hierarchy

for eg.

Industry Country

Profit Center : AC001 Trading Asia

Profit Center : AC002 Invt China

Profit Center AC003 Invt Asia

Users will select "Asia" and then another selection for industry "Trading" then Profit Center- AC001 will apear

User will select " Asia: and then another selection for Industry "All" then AC001 and AC003 should appear

I would like to know how this requirement can be achieved when we have only 1 dimension and selection of multiple hierarchy within the dimension is required

[former_member186338]

What do you mean by "select"? In some particular report?

[former_member186338 · Accepted solution]

In general EPMDimensionOverride do not support "AND" between BAS(...)

Like here:

=EPMDimensionOverride("000", "PROFIT_CENTER", "BAS(PARENT1HIR1) AND BAS(PARENT2HIR2)")

result is empty!

You can try to use VBA to get lists of base members under each parent and compare the lists to find common id's like in my blog:

https://blogs.sap.com/2019/06/24/bpc-nw-10-vba-function-to-get-bassomeparent-dimension-members-list/

When you have final list then you can use EPMDimensionOverride

[former_member186338 · Accepted solution]

P.S. For example there are 2 cells where user select industry and country and a button with vba code to refresh report

VBA code will get lists for both parents, compare lists and generate final comma separated list in some cell.

This cell is referenced in EPMDimensionOverride for the report.

The report is refreshed showing required lines.

[former_member618262]

PS: E1_1 is my profit center base level and rest of the IDS are all nodes

[former_member186338]

Sorry, but I am unable to understand your logic...

"if the user will select "P_TRD " from H1 and "REST_OF_ASIA" from H2, then the data which is at that level should be reflected" ???

at what level??

Please read again my previous answer - I have explained the case with 2 parents and common base members under both parents.

Do you want something different?

[former_member618262]

Hi Vadim

Thank you for the prompt reply as always.

I was also checking if we can use EPMDimensionOverride function if we want Parent only data for Profit center dimension.Please see the below screenshot with hierarchy H1 and H2 eg. if the user will select "P_TRD " from H1 and "REST_OF_ASIA" from H2, then the data which is at that level should be reflected

pc.png(15.0 kB)

pc.png

[former_member618262]

Hi Vadim

Based on the combination for both the hierarchy that the user selects, the profit center data should be all base level under the respective parent. The result should be in the report lines without giving breakup of respective profit centers.

[former_member618262]

@VADIM KALININ

Hi Vadim

Let me re -phrase my question so that it will be more clear to understand :

Can we make multiple selections based on 2 different hierarchy within 1 single dimension ?
for eg. I have Profit center as my dimension under which I have 2 different hierarchy :
Hierarchy 1 is for industry
Hierarchy 2 is for country

User wants to have a selection option for the Profit center dimension based on 2 different hierarchy created.User will select for eg. "Service" from industry hierarchy (H1) and he also needs to select Country for eg. Australia from another hierarchy (H2) so that he can get data for all the profit centers which are under "Service " from hierachy 1 and "Australia" from hierarchy 2

Regards

@VADIM KALININ

[former_member186338]

What do you mean by "under"?

All descendants under some parent?

or

All base members under some parent?

And result has to be report lines?

[former_member618262]

Hi Vadim

The user wants a drop down option in the report , where they can select the Country as well as second selection is Industry. As we have only 1 dimension and multiple hierarchy , I am wondering how we can achieve the requirement

Below is an example

SELECTION 1: Dimension : Profit Center hierarachy H1 : COUNTRY

SELECTION 2 : Dimension Profit Center hierahcy H2: INDUSTRY

Entity 1 Entity 2 Total

Sales - External

Sales- Internal

Regards

[former_member186338]

Sorry, but the idea is still not clear!

You have to provide screenshot of your hierarchy structure to understand the case... Hierarchy assumes multiple levels and I do not understand the required selection result

P.S. Sorry for late reply, but I am not notified about your "answer" to you own question. Use @-mention to notify me.