Dimension Override based on dynamic selection with multiple properties
As EPMDimensionOverride is mostly used function and requirement is to pass multiple property, I decided to write different scenario of this function with dynamic selection of multiple properties and values of properties.
Function and it’s parameters –
EPMDimensionOverride(“reportID”,”Dimension”,”Member”)
The logical operator supported by this functions are as follows
| AND | OR |
|---|---|
| ; | , |
Background
PRODUCT Dimension contains three properties and values as shown in below table.
| AREA | GROUP | FIELD |
|---|---|---|
| 51090 | 11 | A |
In my given case these are the Dimensions as well.
User is selecting “AREA”, “GROUP” and “FIELD” and these value are used to override PRODUCT dimension.
As third parameter of function is “Member” , Concatenate all the desired properties and values of properties in separate cell and give reference of that cell as third parameter in function.
As shown in below first example, Properties and property values are populated in cell “C7:E8” along with logical operator. Desired combination of properties and operator is Concatenated in cell “I8”. The formula of cell “I8” is displayed in cell “I7” (Formula 2) . Alternate formula also provide in above cell “I6” (Formula 1). The EPMDimensionOverride function is written in cell “I4”. Formula of “I4” is displayed in above cell “I3” . The cell “I8” is used as third parameter in function.
Tested on EPM Add-In SP 20, .NET 3.5 , BPC 10.0 NW.
Comments and suggestions are highly appreciated.
Best Regards,
Shrikant
Good Shrikant..
Its really Helpful.
Regards
Rohit Agrawal
Very Good Shrikant.
It help me lot in my project thanks for sharing good stuff.
Hi Shrikant,
Sorry but can you explain me what is the point? ";" - AND "," - OR is a well known syntax...
Vadim
Hi Vadim.
Though it is well know syntax of AND OR, there are many people who are creating threads for dimension override with multiple properties with combination of logical operator. Also I saw many unanswered old threads for the same requirement. 🙂
Shrikant
Hi Shrikant ,
Very nice document for beginner.
Himanshu
Hi Shrikant,
Its helpful for beginners.
Shilpa
Hi Shrikant,
Can we use "not equal to" ?
Shrikant
Hi Shrikant,
I am trying to use EPMDimensionOverride to populate a list of members entered by the user.
The Member to override is "COST_CENTER", and by concatenating a list of cost centers they update automatically.
Is it possible for me to specify two properties within EPMDimensionOverride for every COST_CENTER?
Currently I am not able to pass through the cost center as well as the two additional properties.....
Here is my function:
=EPMDimensionOverride("000","COST_CENTER","40003;COMPANY_CODE:1000;PC_OPEX:FIELD")
Where 40003 is the COST_CENTER, and the following two values are properties of COST_CENTER.
Thanks,
Peter
It's better to open a discussion 🙂
In general I do not understand your logic:
In EPMDimensionOverride you pass the COST_CENTER ID and properties... What do you want to achieve????
Sorry, I'm new to the site.
I am overriding for cost center, and want to override by a cost center and two properties related to that cost_center.
I would end up with a very large concatenation in the end to populate a long list of cost_centers and am pulling their properties from EPMMemberProperty function. In the end the user will paste a list of cost_centers, and EPMDimensionOverride will populate those cost_centers and show values based on those properties also.
Is that possible?
Sorry, still not clear without some sample with screenshot! And document comments is not a right place for this question. Please open a new discussion and provide screenshot with explanation.
Can you do this with other functions as well? and Also on Microsoft Version?
Ex: EPMContextMember
Thanks
EPMContextMember, EPMSelectMember - No!
Thank you Shrikant, your post really helped in our project.
Tried it in Add-In 10.0 sp28 Patch 3 .NET4.5, BPC 10.1 NW, works excellent!!!