on 04-04-2014 12:14 AM
Hi All,
I am working on an EPM reports where I need to use EPMDimensionOverride formula. Requirement is, EPMDimensionOverride should take members from a dynamic cell range in excel sheet and use that to override current
members of dimension.
I know that we can concatenate values in all these cells separated by comma and pass to "Members" parameter of formula but we don't want to do that as the list of members we want to use to override can go very long. User will change this list frequently (add or remove members). We can not ask users to modify EPMDimensionOverride formula whenever there is a change in the list of members.
So far We have tried following but none of these works,
=EPMDimensionOverride("000",<Dimension Name>,EPMCellRanges(E1:E12))
=EPMDimensionOverride("000",<Dimension Name>,E1:E12)
=EPMDimensionOverride("000",<Dimension Name>,Test) where "Test" is a pure EXCEL name range for cells E1:E12
Please advise how I can use EPMDimensionOverride formula with a dynamic cell range.
Thanks,
Neha
Hi Neha,
This can be done using a simple macro to create a comma separated string of members.
Function concatenateRange(myRange, mySeparator)
firstCell = True
myRangeValues = myRange.Value
For Each theCell In myRangeValues
If theCell <> "" Then
If firstCell Then
concatenateRange = theCell
Else
concatenateRange = concatenateRange & mySeparator & theCell
End If
End If
firstCell = False
Next
End Function
Use this function in your excel. If the users are going to type the members in the Row E, then you can call this function in lets say cell A1:
=concatenateRange(E1:E1000,",")
This function will ignore all the blank cells. Now, you can use cell A1 in your EPMDimensionOverride function.
Hope this helps.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Nilanjan,
I did this several times, but in my case, the dimension override could not handle this. All my elements were concatenated correctly with seperator and the EPMDimensionOverride formula afterwards was correct, but I got an error message after refresh anytime I tried custom functions.
I hope, it will work for the thread owner,
Best regards,
Karsten
Hi Nilanjan,
I write =EPMDimensionOverride("000","COST_CENTER","BUS_AREA="&C6) and C6 contains 21,22,25.But the result in rows is the cost centers having 21 as bus_area not 21 or 22 or 25. The formula considers only the first business area not all 3 bus areas seperated by comma in C6.I also tried ";" instead of "," .
What is wrong with the formula?
As far as I understand from your method, we can read more than 1 value dynamically with this conctenation method but my formula did not work.
Hi Vadim,
I already read it. In this thread( If I did not misunderstand) someone tells the opposite(i.e it is possible to use 3 cell reference in dimensionoverride).That's why I asked them before answering your response.
Your solution is not dynamic because it is not cell referanced.I need to read the members from 3 cells dynamically.
Hi Vadim,
Thank you for correcting.
The original problem was this:
COST_ELEMENT has account types like this
As you see a cost element starting with 730 has more than 1 account type so it can not be a property:
COst center has account type too but this time as property.What I want is when the user selects cost center from context, I want to list the corresponding cost elements.i.e when he selects Cost center 1 and if cost center 1 has account type 2 as property then the cost elements of 730 and 740 must appear.(note that 730 , 740 etc is another property of cost element)
In my design, I created a table like above in excel (and hide it of course) and read the account type of cost center via epmcontexmember lets say again 2 .Then go to the table with this value 2 and via vlookup I write 730 and 740 to two cells.Then the last part was to filter costelements via dimensionoverride but I did not achieve this as you know.
I have COST_CENTER in the context bar and COST_ELEMENT in the rows(both are dimensions) And ACCOUNT_TYPE is a property of COST_CENTER and 1st 3 digits of COST_ELEMENT is the property of COST_ELEMENT.
In addition, COST_ELEMENTS has account types,too but 1 cost element may have more then 1 account type. For example 730xxxxxx has 3 account types( 1 , 2 ,3 ). So ACCOUNT_TYPE can not be a property of COST_ELEMENT.
The account types of cost elements are like below and I created the table in input form and hide it:
Now, when the user selects cost center from context, I read the account type of that cost center via epmcontextmember. Lets say he selects Cost center CH002021102 and the account type of CH002021102 is 2. I go to the table above with this value 2 via vlookup and find the cost elements of 730 and 740 and write the 730 to cell A1 and 740 to cell A2.
Then I tried epmdimensionoverride to filter cost elements with the property of 1st 3 digits of COST_ELEMENT but failed. Since dimensionoverride with concatenated value is not working as I told.
Thanks everyone for your response.
Nilanjan - VB function that you provided worked fine for me...Thanks..I noticed that this function works fine unless the limit of maximum number of charters allowed in a cell (32,767) is reached, though it is unlikely that this will happen.
Vadim - Workaround that you provided also works fine and we are already using it in a couple of reports. In this case we could not use it since we don't want users to modify anything except dimension members that they was to add/remove.
I was wondering if any newer version of EPM Add-In provides built-in function for this requirement or may be EPMDimensionOverride function itself has improved.
We are using EPM Add-In version 10.0 SP13 patch 4.
Thanks again !!
Regards,
Neha
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks Nilanjan and Vadim !!
Regards,
Neha
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
hi neha ,
if you want use the fixed member take lucas suggestion.if user need to select for particular hierarchy .
diplay under base level in rows use below formula
create c1:epmselectmeber("",member,filter,dimensionname)
epmdimensionoverride(reportis,dimensionname,bas("&c1&"))
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Neha,
The Members parameter should be a single cell, a single value.
What you need to do is create a new cell E13 i.e. a formula to concatenate values from E1 to E12.
Something like E13, "=E1 & "," & E2 & "," & E3....E12"
Cheers!
Lucas
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Neha,
this is not working by standard.
The dimension override just works with single or concatenated values, as described by Lucas.
The problem at this stage is that standard Excel does not support the concatenation of cell ranges. The bad message is, even if you write an own function for concatenating those ranges, it will not work with the dimension override. This is a known issue.
A possible solution for your problem would be the use of copyranges. This enables you to create complete dynamic reports based on changing members.
Hope this helps,
Karsten
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.