EPMRetrieveData function

[Former Member]

I am trying to use the EPMRetrievedata function in a local member formula hoping it will be dynamic. In column A, I have product #s. All the other page axis, row and column axis members are fixed. I defined all those in the EPMRetrieveData function like EPMMemberID($B$13), EPMMemberID($C$4) etc. except for the product which is in the format EPMMemberID($A12). My hope was that for each subsequent local member, the product cell reference within the EPMRetrieveData will automatically update pointing to the right row like EPMMemberID($A13), EPMMemberID($A14) etc. But it is static. All the subsequent local members all have the same EPMMemberID($A12) reference within the EPMRetriveData. I get the same data for all the products.

Has anyone tried to do this with any success? Is there an alternate way to make it dynamic? All ideas and suggestions will be appreciated. Thank you.

[former_member186338]

Hi Kris,

Please provide the screenshot with clear picture of cells referenced.

Vadim

[Former Member]

Hi Vadim,

Thank you for your quick response. I have attached the screen shot image in a word document.

You will see the local member formula in the top window for the cell F21. It uses the EPMMemberID for the product in B16 as one of the intercepts and calculates the data correctly. However, for the next product in F28, it does not change the pointer. It still calculates using the product data in B16 instead of B23. F28 data value is the same as F21. Since it is a local member, I was expecting it to update the formula each time pointing to the correct product in column B.

Any help to fix the Retrieve local member formula will be appreciated. Is there some other way to make it work? Thank you.

Kris

[Former Member]

Hi Kris,

Try using B$14,C$14 and so on instead of $B$14.

Using $B$14,etc binds the formula to that particular cell only and that is why local members will reference that cell.

Regards,

Varsha

[Former Member]

Hi Kris,

first of all I agree that you should be caution about the $ signs in the referencing. If BPC automatically generates it, then overrule it using Excel referencing.

Among other options, you can use a local member, which you attach to e.g. specific member. In the local member, you can use EPMMEMBER([dimension].[hierarchy].[Member]) . Evidently, in the scenario below, on a node level, you have to calculate it differently ..

Another method is to use the formatting sheet. First, type the formula in the most top left cell, where the formula should first appear. Then copy it to the formatting sheet on the data cell that suits your requirement (either a specific member or a member combination). Add an apostrophe before it and change ";" to ","   (  = if (A=B; "No";"Yes")   should become   '=If(A=B,"No","Yes")  ). Don't forget to select the 'content' of that formatting cell

In the example below the COS is calculated and the result is saved to the database.

I guess you can apply these techniques to your problem.

Kind regards,

Christophe

[Former Member]

Hi Christophe,

Thank you Christophe. In your example, you are using the same column data in the formula like H1*H2 etc. That always works fine. In my case, I have to use the dimension member values that are in different columns in the EPMRetrieveData function and also make it dynamic to call the right member for different products as it goes down the rows. For example, the local member in cell C12 must calculate using the productID in column A10 along with all the other dimension members. The next calculation in cell C15 must use productID in A13 etc. This is not working. All the local members point to the A10 and is not dynamic. I hope this helps.

Kris

[Former Member]

Hi Kris,

you should repeat the Row Headers so each EpmRetrieveData will reference the right Product.Then if you need a similar formatting you can make a new local member with the code/description of the product in this way: IF (B16=B15,"",EPMMemberDesc(B16)) and hide the column B.

Regards

Andrea

[Former Member]

Hi Varsha,

I am not anchoring the rows, only the column. It still does not work.

Krish

[former_member186338]

Hi Kris,

I do not understand why do you use EpmRetrieveData at all? Why it's not possible to create a normal EPM report? The report with huge number of EpmRetrieveData formulas can be very slow...

Vadim