How to activate "insert EPMMemberID in EPM formulas" by API

[former_member440277]

Hi, experts!

I'm trying to activate "insert EPMMembersID in EPM formulas" function in advanced menu option by API (using VBA). Is anybody knows how it works?

and one more question:

i found "UseEPMMemberIDInEVFormulas" function, it looks like the following syntax:

Set api = Application.COMAddIns("FPMXLClient.Connect").Object

api.SetUserOption UseEPMMemberIDInEVFomulas, True

and it doesn't work.

maby in the User Guide was made misprint, anyway both variants ( UseEPMMemberIDInEVFomulas and EseEPMMemberIDInEVFormulas) don`t works.

What's wrong with this script?

[former_member186338 · Accepted solution]

Hi Ilya,

Please explain you requirements! What do you want to achieve in general??? Why do you need the mentioned functions?

Vadim

[former_member186338 · Accepted solution]

"i found "UseEPMMemberIDInEVFormulas" function," - incorrect, it's not a function it's a string parameter of the method SetUserOption.

The correct syntax is:

First in VBA project: Tools -> References: check FPMXLClient

Then:

Dim epm As New FPMXLClient.EPMAddInAutomation

Sub SetEPMMemberID()

epm.SetUserOption "UseEPMMemberIDInEVFomulas", True      'String!!!

End Sub

Vadim

[former_member186338 · Accepted solution]

P.S. Just tested the code from my previous message - working as expected!

Vadim

[former_member186338 · Accepted solution]

If it's working - it's a good practice to close the discussion...

[former_member440277 · Accepted solution]

Hi, Vadim!

Thank you for help. Now scipt seems work correctly.

The original purpose was to create column with property of members in column "B". For this purpose i created column with formula =EPMMbemberProperty(<connection>;B10;<property>) using EPMCopyRange. Where B10 is the member inthe row axis, wich looks like description only (without ID). In this case, some formulas shows #AmbiugousMemberDescription - ... (because some of descriptions are using few times). To fix it i'm using EPM\Tools\More\insert EPMMember in EPM formulas.I need to use this option after expand (refresh) report. Because if i'm taking EPMMemberID in =EPMMemberProperty(<connection>;EPMMbemberID(B10);<property>) using EPMCopyRange, the EPMCopyRange inserts formulas in each cells, however without EPMMemberID inserting works only if cell in column "B" is not empty.

[former_member186338 · Accepted solution]

I can' see you report, but instead of EPMCopyRange it's better to use local member. You will put yourself EPMMemberID in the local member formula. In this case you don't need to play with VBA!

Vadim

P.S. If it's possible to avoid VBA - better to avoid it...

[former_member440277 · Accepted solution]

thank you for idia, i'll try to use your advice to use local member. I'm also trying to avoid using VBA.

[former_member186338 · Accepted solution]

Look in the help:

"16.4.3 Local Members - Examples"

Formula like:

=EPMMemberProperty(,EPMMbemberID(EPMDIM_CURRENT_MEMBER(DIMNAME)), "PROPERTY")

Where DIMNAME - dimension name in the row axis.

In the local member formula use English format independent of your Excel settings ("," - delimiter)!

Vadim

But Absolutely strange idea to mark this discussion as Assumed Answered! Assumed Answered - means that you didn't received a correct answer...

[former_member440277 · Accepted solution]

Sorry. i misundersstood the meaning of the status. is possible to change it?

[former_member186338 · Accepted solution]

Yes, simply select a correct answer and mark it Correct!

[former_member440277 · Accepted solution]

in my case it looks like this:

=IF(EPMDIM_CURRENT_MEMBER(<dimension name>)<>"",EPMmemberProrerty(,EPMMemberID(EPMDIM_CURRENT_MEMBER(dimension name)),<property>),"").

but one more question: is it possible optimise this formula without IF?

[former_member186338 · Accepted solution]

Why you don't like if?

Test for empty is because of blank lines or???

It's better to provide a report screenshot...