cancel
Showing results for 
Search instead for 
Did you mean: 

Showing parent in EPM based on base level member selected

former_member544285
Participant
0 Kudos

Hi Experts,

I have a requirement in which I need to filter ACCOUNT dimension using EPMDimensionOverride. On top of that, the dimension has to show it's parent(s) in hierarchy up to the very top. Illustration of ACCOUNT master data:

  • 10000
    • 11000
      • 11100
      • 11200
    • 12000
      • 12100
      • 12200

In case I need to show ACCOUNT 11100, the report must show:

  • 10000
    • 11000
      • 11100

In case I need to show ACCOUNT 11200, 12100, 12200, the report must show:

  • 10000
    • 11000
      • 11200
    • 12000
      • 12100
      • 12200

I'm wondering if there is a standard config in EPM that can be used to enable this behavior.

P.S. I'm using BPC NW 10.1 with EPM add-in version 20 SP1

Thanks,

Hendry

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Hendry,

Are you using property to determine the override?

if that is the case then just make sure you have the same property for those parent nodes as well, then it will display the whole structure.

Andy

former_member544285
Participant
0 Kudos

Hi Andy,

Unfortunately, based on my requirement we can't do that. The property is maintained by comma-separated list of ENTITY. The relationship is many-to-many so it complicates things and I can't have the parent to have the same property. (see below)

Thanks,

Hendry

Former Member
0 Kudos

Hi Hendry,

Why you can't apply property to the parents?

Why don't you try it first before you say it can't?

Give the same property to the parent nodes and it will work.

Andy

former_member544285
Participant
0 Kudos

Hi Andy,

You're right, as long as I can maintain the right value on parent hierarchy, I can show the member correctly. (See below for illustration)

It would require additional effort to maintain on top node, but I think it's the best solution for this requirement.

Thanks a lot!

Hendry

Former Member
0 Kudos

Hi Hendry,

Great, glad to help.

It is the only way with dimension override.

It will be a mission to maintain unfortunately.

Andy

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi Hendry,

I had a similar problem and solved by checking the authorization objects.

Everson

former_member544285
Participant
0 Kudos

Hi Everson,

Can you elaborate on that? Do you use Data Access Profile to limit ACCOUNT visibility?

Thanks,

Hendry

Former Member
0 Kudos

Hi Hendry,

Please... check this post

Everson

former_member544285
Participant
0 Kudos

Thanks Everson,

This method will work when we have a 1:1 user assignment to ENTITY. But as of now, our mapping is not 1:1 between user and ENTITY.

BR

Hendry

former_member186338
Active Contributor
0 Kudos

Hi Hendry,

Can you explain what do you want to show - parent member ID's or also values for parent member ID's?

Show the screenshot of the desired result!

Vadim

former_member544285
Participant
0 Kudos

Hi Vadim,

I want to show the parent member as well as the values for the member IDs. Assuming the ACCOUNT master data goes like this:

The report goes like this:

Please note that the ACCOUNT.COMPANY is comma separated list of members of ENTITY.

Thanks,

Hendry

former_member186338
Active Contributor
0 Kudos

Hi Hendry,

I do not understand what is the relation between ACCOUNT property ENTITY or COMPANY (not clear from your post) with the report requirements... What is your current EPMDimensionOverride formula??

Second - the "desired" result from your post can't be achieved by EPM/Excel functions ... Only VBA.

Vadim

former_member544285
Participant
0 Kudos

Hi Vadim,

On ACCOUNT dimension, I maintain a property which called "COMPANY". This property represents the list of ENTITY that the ACCOUNT is allowed to be posted on. In my previous post, account 12120000 can only be posted on ENTITY 1100 and 1200, while ACCOUNT 12310000 can be posted on ENTITY 1100, 1200, and 1300.

On the report, user will select ENTITY, and then based on that ENTITY, I have to populate the list of ACCOUNT that are available for the ENTITY, based on the property ACCOUNT.COMPANY. I have achieved the list of ACCOUNT by excel formula, but the problem is that the client wanted to see the ACCOUNT bottom-up -- from base level to the top level.

I have been thinking to recursively read the H1 property, but it will show without grouping:

Before I move to VBA (which will make things more complex), I'd like to know if there's an alternative solution to this problem

Regards,

Hendry

former_member186338
Active Contributor
0 Kudos

"in the report, user will select ENTITY, and then based on that ENTITY, I have to populate the list of ACCOUNT that are available for the ENTITY, based on the property ACCOUNT.COMPANY"

Please! What is your current EPMDimensionOverride formula?? How you managed to use property with a comma separated list of ENTITIES?

Vadim

former_member544285
Participant
0 Kudos

Hi Vadim,

I created a separate report which show all ACCOUNT under Revenue (The report is for Revenue), and then add 2 local members:

1. To check if the selected ENTITY is within ACCOUNT.ENTITY. If yes, show ACCOUNT, else show blank.Formula :

=IF(IFERROR(FIND(Revenue!REV_ENTITY,EPMMemberProperty(,INDIRECT(ADDRESS(ROW(),COLUMN()-2)),"COMPANY")),0)>0,INDIRECT(ADDRESS(ROW(),COLUMN()-2)),"")

2. To concatenate the list of ACCOUNT to be shown. Formula:

=IF(INDIRECT(ADDRESS(ROW()-1,COLUMN()))="",INDIRECT(ADDRESS(ROW(),COLUMN()-1)),IF(INDIRECT(ADDRESS(ROW(),COLUMN()-1))<>"",INDIRECT(ADDRESS(ROW()-1,COLUMN()))&","&INDIRECT(ADDRESS(ROW(),COLUMN()-1)),INDIRECT(ADDRESS(ROW()-1,COLUMN()))))

See below:

BR

Hendry

former_member186338
Active Contributor
0 Kudos

Hi Hendry,

Looks extremely complicated!

What about creating some matrix ACCOUNT/ENTITY with all other dimensions fixed to some dummy members? For each account (base, parent) you can save value 1 against the entity where it has to be shown and nothing in the opposite case.

Then you will create the first report with dummy members in the page axis, multimember ENTITY also in the page axis, some dummy member in the column axis and all ACCOUNT members in the row axis. For this report in Edit report use "Remove Empty and Zero Values". As a result - for the selected combination of the ENTITY members only accounts with not empty value will be shown.

Then use Row axis of the first report as a shared axis of your final report.

Vadim

former_member544285
Participant
0 Kudos

Hi Vadim,

It's an interesting concept and it can be useful in the future. However, currently the mapping is being maintained from R3. We pull the information into 0GL_ACCOUNT infoobject in BW and then load it to ACCOUNT dimension in BPC. We could've loaded it as transaction data and do it as your suggestion, though. Your solution would be more feasible if the maintenance is being done on BPC.

Thanks!

Hendry

former_member186338
Active Contributor
0 Kudos

But how you maintain the property COMPANY?

Vadim

former_member544285
Participant
0 Kudos

We were able to get the mapping in the BW system. It was maintained in the infoobject 0GL_ACCOUNT as attribute. When loading master data for ACCOUNT from 0GL_ACCOUNT, I simply pull the information into ACCOUNT.COMPANY property

BR

Hendry

former_member186338
Active Contributor
0 Kudos

"We were able to get the mapping in the BW system. It was maintained in the infoobject 0GL_ACCOUNT as attribute." - how?

Just for test - try my proposal (using Excel template to convert COMPANY property into data to be written):

Page axis - dummy members

Column Axis - Entities

Row Axis - Base Accounts

Local member - COMPANY property of account. Located before column axis.

Formula in the data area - if local member cell contain Entity in current column then 1, else - nothing

Vadim

former_member186338
Active Contributor
0 Kudos

I have done tests myself and it's working fine:

Input form to convert COMPANY property to values saved:

Results with different BE selected (BE - ENTITY)

Vadim

former_member544285
Participant
0 Kudos

Hi Vadim,

In my case there is a mapping table in SAP R3 that they also use to filter ACCOUNT by COMPANY. So the maintenance will be done on R3.

In case of mapping done on BPC, I'd agree that your method is more preferable.

Thanks,

Hendry

former_member186338
Active Contributor
0 Kudos

Can you explain the structure of mapping table?

Vadim