= EPMOlapMemberO Function with Reference Cell

[Former Member]

Hi Experts,

     I am generating a BPC Revenue variation report by comparing two(Current Year,Current year - 1)  years revenue . I have used (EPMMemberOffset,EPMcontextmemebr and EPMOLAPmemberO) functions to achieve the requirement below.

                     Revenue

                     2014.01    2013.01      Variance 2014.02   2013.02  Variance .................................2014.12  2013.12   Variance

Material

By using EPmcontextmember I got Time (2014.TOTAL) and EPMcontextoffset member got 2013.

I have used EPMOlapMemberO function by giving reference cell value.

Ex - EPMOlapMemberO(G23,"[TIME].[PARENTH1].[2014.01]","2014.01","","000")

       Cell G23 is having 2014.01 and G24 is having 2013.01 and I have used the same for 12 months. With respect to cell values have given references in EPMOlapMemberO() function...

Now, if I select 2014.Q2 my report has to show only 2014.04  2013.04 2014.05  2013.05 2014.06  2013.06 2014.Q2  2013.Q2 and for remaining it should show blank values. (I have written excel formula to generate blank values, so that EPMOlapmemberO will refer blank cell and finally will show blank values for Q1 Q3 and Q4).

But values are still showing in the report for the remaining as well where it has to show blank because reference cells are having blank values .

Please suggest me do I need to follow any setting in order to not show values for the blank reference cell while using EPMOlapMemberO() funtion with cell reference..

Thanks..

[former_member186338]

Hi Priya,

You are designing static report (for column axis). Static report consist of FIXED number of columns.

And each column will have EPMOlapmemberO function with the reference to some cell. And this cell can't be blank - have to contain some valid TIME member ID.

Use Excel formulas to present some valid TIME member where you don't have data - 2005.01 for example...

Vadim

[Former Member]

Hi Vadim ,

Great thanks for your prompt response...

I have used excel formula to calculate the year which is not having any values in my system. however after report refresh also still showing earlier values.

Can you suggest any other way to generate variation report ( ex - Comparing years)  with out using EPMOlapmemberO function with the reference cell value.

I can see member offset function in TIME dimension.I have selected Time with members and decendends and

member with offset (-1) . Its not reflecting to all the months and only for year like below

2014.01   2014.02  2014.03.......2014.12    2014  2013

[former_member186338]

Sorry, you have done something incorrect!

"I have used excel formula to calculate the year which is not having any values in my system. however after report refresh also still showing earlier values." Incorrect formula ...

And if you want to have some other proposals - please show meaningful report screenshot with detailed description!

Vadim

[Former Member]

Hi Vadim,

Please find the report format.Having revenue variance for the current year by comparing (current year -1) previous revenue values.

The report has to change dynamically with respect to time selection.

Ex - Time - 2014.Q2 then

       2014.04  2013.04  2014.05 2013.05 2014.06 2013.06 2014.Q2 2013 Q2

Calculated year and month values as shown above and copied to 12 months .

I have used EPMOlapMemberO() by referencing cell values( For the above calculated 12 months).

I tried with the option in the TIME dimension (Conetxt 2014) Member & Descendants

                                                                 (Context 2014) Member Offset

Report output -

                        2014 01 2014 02 2014 03.........2014.12 2014 2013

        Material

Kindly suggest any other way to achieve this requirement.

[former_member186338]

Sorry, but unclear:

in C14 you have =EPMContextMember(,"TIME")

But I see 2014 in this cell????

The logic - how many columns to show is also not clear!

Please provide number of possible time selections and columns required for each...

Vadim

[Former Member]

Hi Vadim

"The logic - how many columns to show is also not clear!"

If user select TIME as 2014 then the output sequence should be,

201401 201301 201402 201302 201403 201303 2014Q1 2013Q1 201404 201304 201405 201405

201406 201306 2014Q2 2013Q2 201407 201307 201408 201308 201409 201309 2014Q3 2013Q3 201410 201310 201411 201311 201412 201312 2014Q4 2013Q3 2014 2013 .

If 2014Q1 then, the report should show only

   201401 201301 201402 201302 201403 201303 2014Q1 2013Q1 .

   2014Q2 then,

  201404 201304 201405 201405 201406 201306 2014Q2 2013Q2,

   2014Q3 then,

  201407 201307 201408 201308 201409 201309 2014Q3 2013Q3

and for 2014Q4

  201410 201310 201411 201311 201412 201312 2014Q4 .

"Please provide number of possible time selections and columns required for each..."

Only one time selection based on year or quarter ex- 2014 or 2014Q1 2014Q2 2014Q3

[former_member186338]

Please, show the screenshot of your TIME dimension in Admin...

[former_member186338]

And PLEASE, use Excel screenshot for your columns conditions... Your previous post is unreadable

[gajendra_moond]

Hi Priya

Could you try local member formula?

[former_member186338]

Hi Gajendra,

Local member formula will not help to get the member of previous year (only using EPMRetrieveData - but it's a slow option)

Vadim

[Former Member]

Hi Vadim,

As suggested in earlier post  I have mapped columns with the reference cells which are not having data in the system and its functioning well showing blanks rather disappearing (EPM Function) after report refresh.Thanks for the same !!!

Can you suggest any other way out\ EPM Functions  for the below requirement apart from EPMOLAPmemberO with the cell  reference.

*************************************************

If user select TIME as 2014 then the output sequence should be,

201401 201301 201402 201302 201403 201303 2014Q1 2013Q1 201404 201304 201405 201405

201406 201306 2014Q2 2013Q2 201407 201307 201408 201308 201409 201309 2014Q3 2013Q3 201410 201310 201411 201311 201412 201312 2014Q4 2013Q3 2014 2013 .

If 2014Q1 then, the report should show only

   201401 201301 201402 201302 201403 201303 2014Q1 2013Q1 .

   2014Q2 then,

  201404 201304 201405 201405 201406 201306 2014Q2 2013Q2,

   2014Q3 then,

  201407 201307 201408 201308 201409 201309 2014Q3 2013Q3

and for 2014Q4

  201410 201310 201411 201311 201412 201312 2014Q4 .

**************************************************

[former_member186338]

Hi Priya,

The original question is solved, better to mark the discussion closed.

Another way is to have a local member after each member of Time dimension with EPMRetrieveData. For Time member in EPMRetrieveData you have to calculate proper offset using EPMMemberOffset from the previous column and depending on the level (month, quarter, year).

B.R. Vadim

[Former Member]

Thanks Vadim

[former_member186338]

Solution with local member:

First in the TIME dimension create a property like TIMEID, but for all members (base and parent) with some sort order values:

2013.01 - 201301

2013.02 - 201302

2013.03 - 201303

2013.Q1 - 201310

2013.04 - 201311

...

2013.Q4 - 201340

2013 - 201350

Then in member selection for TIME column axis use Member Sorting & Grouping (in my sample I used TIMED with slightly incorrect sort order...):

With creating Local member After Group with the following formulas for:

Display:

"&IF(EPMMemberProperty(,INDIRECT(ADDRESS(7,COLUMN()-1)),"LEVEL")="YEAR",EPMMemberOffset(,INDIRECT(ADDRESS(7,COLUMN()-1)),-1),IF(EPMMemberProperty(,INDIRECT(ADDRESS(7,COLUMN()-1)),"LEVEL")="QUARTER",EPMMemberOffset(,INDIRECT(ADDRESS(7,COLUMN()-1)),-4),EPMMemberOffset(,INDIRECT(ADDRESS(7,COLUMN()-1)),-12)))&"

Formula:

=EPMRetrieveData(,EPMMemberID($A$1),EPMMemberID($A$2),EPMMemberID($A$3),EPMMemberID($A$4),EPMMemberID($A$5),EPMMemberID(EPMDIM_CURRENT_MEMBER(INACCT)),IF(EPMMemberProperty(,INDIRECT(ADDRESS(7,COLUMN()-1)),"LEVEL")="YEAR",EPMMemberOffset(,INDIRECT(ADDRESS(7,COLUMN()-1)),-1),IF(EPMMemberProperty(,INDIRECT(ADDRESS(7,COLUMN()-1)),"LEVEL")="QUARTER",EPMMemberOffset(,INDIRECT(ADDRESS(7,COLUMN()-1)),-4),EPMMemberOffset(,INDIRECT(ADDRESS(7,COLUMN()-1)),-12))))

Result:

Vadim

[former_member186338]

By the way, the formulas can be simplified using Excel SEARCH function:

Display:

"&EPMMemberOffset(,INDIRECT(ADDRESS(7,COLUMN()-1)),-SEARCH(LEFT(EPMMemberProperty(,INDIRECT(ADDRESS(7,COLUMN()-1)),"LEVEL"),3),"YEAQUAxxxxxMON"))&"

Formula:

=EPMRetrieveData(,EPMMemberID($A$1),EPMMemberID($A$2),EPMMemberID($A$3),EPMMemberID($A$4),EPMMemberID($A$5),EPMMemberID(EPMDIM_CURRENT_MEMBER(INACCT)),EPMMemberOffset(,INDIRECT(ADDRESS(7,COLUMN()-1)),-SEARCH(LEFT(EPMMemberProperty(,INDIRECT(ADDRESS(7,COLUMN()-1)),"LEVEL"),3),"YEAQUAxxxxxMON")))

P.S. And the performance is not terribly bad