on 02-08-2012 9:27 AM
Hi,
I am creating a dynamic template where the Employees and Cost centers are two dimensions. Under employee dimension, cost centers have been added as properties as shown below:
ID Costcenter
ALLEMP
EMP001 2358
EMP002 2358
EMP003 2358
EMP004 2358
EMP005 2354
EMP006 2354
EMP007 2354
EMP008 2354
EMP009 2358
EMP010 2354
i have designed the Employees dimension members in row n time in column. While creating the template i can see the only 2354 employees. My requirement is if i change the context member from 2354 to 2358 then only those employees must appear which are shown under their respective properties. i have already checked filter because of which i can see only 2354 members but i want it to be dynamic and also that if i select 2354 then only 2354 members must appear and vice versa. It should not be static.
Please guide.
Thank you.
Rgds,
Poonam
There are two new functions in BPC 10 that will help:
EPMAxisOverride and EPMContextMember
Assuming you are using the first report on the sheet (default report), use the following:
Build the report, then on the same sheet as the report, enter the following formula (in any cell outside of the report):
=EPMAxisOverride("000",FALSE,"EMPLOYEE","CC='"&EPMContextMember("COSTCENTER")&"'")
Assuming your dimensions are named "EMPLOYEE" and "COSTCENTER". Also, assumes the cost center property on EMPLOYEE is named "CC".
Let me know if you have any trouble.
Steve
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Steve,
Thank you so much for the reply but unfortunately if i enter that formula to any cell, only "Column Definition 001" appears in the cell. I believe that 001 refers to the report which i want to be flexible. Nothing is changing in the layout if i change Cost Centers. Secondly, i would like to inform that i have three reports in one sheet showing different layouts.
Please guide.
Rgds,
Poonam
Hi,
You should refresh the report to see expected results. If you want to apply the same functionality for all 3 reports then you can pass Report ID (first parameter) as "000;001;002".
Here 000,001 and 002 are report Ids.
You should pass TRUE as second parameter if Employee is under Row axis.
Hope it helps...
regards,
Raju
Hi Raju,
Thanks for your help. i refreshed the sheet but still getting the same thing. One more thing, i can now see all the members of employee dimension even if i select 2354 or 2358. They dont consider cost center proerty.
If i change the parameter to TRUE, it shows me only the member selected in the context member of Employee dimension.
Please guide!
Rgds,
Poonam
Hi Poonam,
Can you try as follows :
=EPMAxisOverride("001","TRUE","EMPLOYEE","Costcenter="&EPMContextMember(,"COSTCENTERS"))
Here Costcenter should be your attribute under EMPLOYEE dimension
Note : In EPMContextMember, first parameter is optinal (i.e Connection name ) and second parameter should be Dimension
Hope it works...
regards,
Raju
User | Count |
---|---|
5 | |
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.