on 03-20-2015 3:53 AM
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:
In case I need to show ACCOUNT 11100, the report must show:
In case I need to show ACCOUNT 11200, 12100, 12200, the report must show:
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Hendry,
I had a similar problem and solved by checking the authorization objects.
Everson
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
"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
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
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
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
"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
User | Count |
---|---|
6 | |
5 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.