Need advice for EPMTUPLE

[Former Member]

Hi BPC experts,

Using BPC 10 SP20, I need to display a local member that shows the difference between 2015.01 and 2016.01. This local member must be after 2016.01 ACTUAL such as in the following picture :

My formula is :

EPMComparison(, EPMDIM_CURRENT_MEMBER(ACCOUNT), EPMTUPLE([CATEGORY].[].[ACTUAL], [TIME].[PARENTH1].[2015.01],), EPMTUPLE([CATEGORY].[].[ACTUAL], [TIME]..[PARENTH1].[2016.01],))

I checked the member combination in the report editor and put 2016.01 and ACTUAL.

Unfortunately, it does not work. Is my formula wrong ? Is EPMTUPLE the right formula to use ?

Thank you in advance.

Dat

[Former Member · Accepted solution]

Ok so...

I solved my issue but another problem arised...

The following formula works for my inquiry :

I added two columns and then refreshed the data. It works being dynamic : (Actually, I did not read the whole topic I mentioned, my bad)

However, If I reverse Category and Time such as the following picture, the local member does not work anymore, it even disappears...

Unfortunately, I actually need to refer to this structure and not the one before.

What could have gone wrong ?

Dat

[Former Member]

Hi,

Put the local Formula. Goto edit report--> local member---> add local member at column position 3(Assuming the first two columns are 2015.01 and 2016.01)

Write the excel formula in formula bar.

Rohit

[Former Member]

Hi Rohit,

I did try that at first and it worked.

However, by using automatic local member recognition, the formula would use EPMposition(). This solution does not work as soon as a user will insert a column or a row and refreshes the data.

Furthermore, it also worked with EPMmember instead of EPMTuple if I did not add the Category dimension in columns. However, my final objective is to compare ACTUALS and BUDGET so it is necessary for me to keep the Category dimension in the column.

[Former Member]

Hi,

If this is the case than use EPMPOSITION.

EPMPOSITION(1) - EPMPOSITION(2)

Also don't forget to click on column radio button

Rohit

[Former Member]

Hi Rohit,

EPMposition does not allow a user to add new columns/rows and to refresh data without messing up the local member so I would like to use another formula.

Dat

[Former Member]

Hi,

Use EPMPOSITION function only in the formula bar of local member.

For adding new column use local member

Rohit

[Former Member]

Hi,

I think I am not clear enough with my explanation or there is something I don't get about EPMposition.

So let me illustrate EPMposition from my point of view :

First, I do this :

That's what you are explaining to me, right ?

The result appears as it should be :

Until now, everything works as it is intended.

Now, I add a column and then I refresh the data :

The local member does not refer to 2016.01 anymore due to EPMposition. That is the issue that needs to be solved.

If you know something about EPMposition being more dynamic, please tell me.

I actually referred to the following topic : dynamic referencing for Local Member Formula in BPC 10 reporting (cannot linked the url, I don't know why, you can google it).

They are using EPMMember which works for one dimension in the columns but not if there are several ones. I do believe EPMTuple is the right solution but there is something wrong in my formula.

[Shrikant_Jadhav]

Hi Dat,

What it your exact requirement? How do you added new column ? Is it number of column are not fixed ?  On what basis column structure is changing ?

Shrikant

[Former Member]

Hi,

If you have inserted a new column, you have to change formula also.

Rohit

[Former Member]

Hi Shrikant,

I just added normally with a right click -> insert entire column.

My requirement is to make sure that the local member always refers to the 2015.01 and 2016.01 even if I insert a column.

Please read the topic "Dynamic Referencing for Local Member Formula in BPC 10 Reporting" (I cannot copy-paste the topic here I don't know why).

They are using a dynamic referencing exactly the way I want. However, they use EPMMember in their formula, which only works for one dimension in a column (in their case, they have time dimension). In my case, I need to put 2 dimensions in the column (Category and Time) which is why EPMMember does not work. I do believe that EPMTuple is the right answer but I am not using it in the right way.

Please check my formula in my first post.

Thank you,

Dat

[Former Member]

Hi,

I am not going to change the local member each time a user will add a column/row. I need a dynamic referencing such as the topic I mentioned to you. Please read the topic.

I already tried their formula and it works as well but only when there is one dimension in columns, not 2.

[Former Member]

Hi,

Can you tell me on what basis your columns are changing.

Rohit