Local Formula - Description refer to Excel Formula - possible?

[Former Member]

Hi,

I have a report with the following structure

- Rows are static

- Columns have entity and account.

To repeat the headings local formulas which are attached to the member "ACCOUNT" have been created which work well. 

However, I also need to put sub titles in the middle. As you can see below Area A, B and C. Area C can be hard coded in the report at the top. i cant seem to able to use a excel formula for a description of a local formula which would resolve the problem (searching scn it looks like its not possible?).  Any other ideas.

The three titles would not ever change - so a description referring a cell would work well if it was possible.

Cheers,

[former_member186338 · Accepted solution]

Hi Leo,

Yes, it's possible to use reference in the description, but what cells do you want to reference - fixed?

Vadim

[former_member186338 · Accepted solution]

Sample:

Vadim

P.S.

The resulting formula in the Description will be:

= EPMLocalMember(""&A1&"";"000";"000")

[Former Member · Accepted solution]

Hi,

So for example, I create a local formula and if I set to use the following excel formula I expect it to show what is column J. However, I have tried putting a formula in the description field but it doesnt seem to work?

=INDIRECT("J"&ROW())

[former_member186338 · Accepted solution]

Sorry, but you use incorrect syntax!

You have to use:

"&INDIRECT("J"&ROW())&"

Prefix "& and suffix &" are not optional!

Vadim

[Former Member · Accepted solution]

Hi,

It looks this will work as long as the number of rows do not expand which they will depending on the selection of accounts.

For example, if account 4 appeared under area A this will push everything down. In turn Area B and Area C wont show (highlighted in red) as they are missaligned to the data on the right.

I could try and write a formula i guess looking at the cell entity value above (using offset function i think) and then show the appropriate value. as i can now use excel formulas in the description this does give me more flexibility.

If there are any other ideas please let me know.

[former_member186338 · Accepted solution]

Easy:

Just test for ENTITY and based on Entity select with IF the required area

Vadim

[Former Member · Accepted solution]

Hi - when you say test area for entity? Are you referring to an EPM function?

Or should I use the offset function look for entity below?

Cheers

[former_member186338 · Accepted solution]

In my sample I have Entity dimension in column A

The local member description formula is:

"&IF(INDIRECT("A"&ROW())="BE1000","Area A",IF(INDIRECT("A"&ROW())="BE3000","Area B",IF(INDIRECT("A"&ROW())="BE4000","Area C","")))&"

Vadim

[former_member186338 · Accepted solution]

You can add required offset to ROW()+...

[Former Member · Accepted solution]

Hi,

When i enable the local formula it displays in the report fine. I go back to edit report and it seems to have disabled it self i i dont touch it - i.e. leave it disabled it still shows on the report?

does that happen with you?

Cheers,

[former_member186338 · Accepted solution]

Select the local member and it will be enabled

[Former Member · Accepted solution]

Hi,

Something weird going on.

I now enable it and it appears. Then I refresh the report without editing the report it disappears!

I am assuming it stays on your report?

[former_member186338 · Accepted solution]

Before selecting local member:

After:

Vadim

[former_member186338 · Accepted solution]

No issues like this! It works fine - I can refresh report without issues!

I can change Accounts set - no issues!

Vadim

[Former Member · Accepted solution]

Hi - definitely a problem with my local member. This is the formula - see any issues with it?

"&IF(INDIRECT("A"&ROW())="ENITITY B","Area B","Area C")&"

I replace the formula with something simple it works but i enter the above it appears once i finished enableing the local member. Then refresh again it disappears

[former_member186338 · Accepted solution]

Please, show your real formula - with real member ID!

And a screenshot of local member window.

I have absolutely no issues...

Test formula:

"&IF("C"="C","Area B","Area C")&"

...

Vadim

[Former Member · Accepted solution]

Hi,

Apologies - i cant provide formula with member IDs. I can confirm the formula is as i wrote as below. The entity ID has no spaces.

"&IF(INDIRECT("A"&ROW())="ENTITY_B","Area B","Area C")&"

The screen is below.

I do think though there is a bug. A normal formula like the following works fine

"&"Area A"&" - displays areas Area A

"&INDIRECT("A"&ROW())&" - This shows the value in column A

Any IF statement I add makes it automatically disable after the second refresh i.e. Ok once I press ok after edit report but then i refresh it disappears.

I am on V10 SP 14 Patch 3

Cheers,

[former_member186338 · Accepted solution]

Ups, you even have secret member id's

Check formula:

"&CONCATENATE("AAA","BBB")&"

I want to see that formula with "," inside is working properly! Like IF(...,...,...)

And "I am on V10 SP 14 Patch 3" (you mean EPM SP 14 Patch 3?) - extremely old, I can't test on it...

I am currently working with EPM SP19 .NET4

Vadim

[Former Member · Accepted solution]

Hi - I think you found the problem the same thing happens with the formula:

"&CONCATENATE("AAA","BBB")&"

Is there a way i can get around the problem with ","

Cheers,

[former_member186338 · Accepted solution]

Upgrade EPM

It's definitely EPM bug!

Vadim

P.S. At least upgrade to test! Then you can reinstall the old....

[former_member186338 · Accepted solution]

For sure you can create a column local member to perform the IF(... based on Entity. Then you can reference the result of column local member in the row local member description - without comma. Then hide the column with column local member.

Vadim

[Former Member · Accepted solution]

Hi,

I have added a local formula to the first column as detailed below but the rows override the column. In turn the values for sub heading dont show next to where i want the sub headings to be displayed (location where i want the headings highkighted in red)

I tired to then change the setting on local formulas so that the columns are priority and i get the following. the local formulas for variance and other just carry on calculating.

Any other options - or is EPM upgrade or just use outer option..

[former_member186338 · Accepted solution]

Hi Leo,

I was talking about the local member with the formula like:

=IF(EPMDIM_CURRENT_MEMBER(ENTITY)="ENTITY_A","Area A","Area B")

You will have Area A or Area B... in this column.

Then you can reference this value in the description of header local member.

Vadim

[Former Member]

Perfect - thanks

[former_member186338]

Another option is to use Outer Member option in the local member setup - Entity x will be shown!

Vadim

[Former Member]

Hi - this was going to use this as my backup option

[former_member186338]

You can also use Outer Member option with Member Names option:

Rename ENTITY A to Area A, ENTITY B to Area B, ENTITY C to Area C and use Outer Member

Vadim

[Former Member]

Hi - I tried this but it didnt work? Does it work for you?

[Former Member]

Hi,

to resolve this i renamed the member and then i referred to it via a formula without commas

[former_member186338]

You can rename by Member rename or you can "rename" using Excel IF in column member formula! Both options will result in the column with Area x values.

Vadim