cancel
Showing results for 
Search instead for 
Did you mean: 

EPMDimensionOverride with Dynamic Cell Range

Former Member

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

Accepted Solutions (1)

Accepted Solutions (1)

Former Member

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.

Former Member
0 Kudos

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

Former Member
0 Kudos

Hi Karsten,

I have used these multiple times in multiple projects, without any difficulty.

Did you face any specific problem?

gunesbt
Active Contributor
0 Kudos

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.

former_member186338
Active Contributor
0 Kudos

Please read my answer here

gunesbt
Active Contributor
0 Kudos

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.

former_member186338
Active Contributor
0 Kudos

Some misunderstanding!

The thread is about concatenating Member ID's and not about member properties for filter!

About dynamic formula - please explain, how you will get the properties (show some screenshot). In some cases it's possible to use Excel formulas...

Vadim

gunesbt
Active Contributor
0 Kudos

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.

former_member186338
Active Contributor
0 Kudos

Sorry, but I can't understand your logic:

"As you see a cost element starting with 730 has more than 1 account type so it can not be a property"...

Please describe all properties and dimensions used...

Vadim

gunesbt
Active Contributor
0 Kudos

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.

former_member186338
Active Contributor
0 Kudos

Please, open a new discussion...

It's possible to achieve.

Vadim

gunesbt
Active Contributor
0 Kudos

Ok.Thanks

Answers (5)

Answers (5)

Former Member

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


Former Member
0 Kudos

Hi Neha,

There is no built in functionality to handle this. You need to address this through some workaround.

Hope this helps.

former_member186338
Active Contributor
0 Kudos

Hi Neha,

I am skeptical about adding functionality to use some range argument for EPMDimensionOverride function... A lot of checks to be done with the contents of this range (single column, ignore empty, check members id's are valid...)

It's possible, but not in the nearest future

B.R. Vadim

former_member186338
Active Contributor

Hi Neha,

I have used some not perfect workaround: just add the column with the same formula. Then if new member is added - simply copy the formula:

Vadim

P.S. This solution is for the cases when you have to avoid VBA!

Former Member
0 Kudos

Thanks Nilanjan and Vadim !!

Regards,

Neha

Former Member
0 Kudos

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&"))

lucas_costa3
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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