EPM report to display columns based on Context change

[Former Member]

All,

My requirement is to display Quarter values based on change in periodic context  for TIME dimension. Report has Glaccount in Row and Time Dimension in column.

Example:

Time Dimension Context = 2015.01

Row             2015.Q1

Account1       1234

If context  changed to 2015.04  , then column should populate values for 2015.Q2. Based on periodic context , columns to display Quarters for those periods fall. If the quarter selected in Context, then column to display normally as Quarter itself.

Please also look my screenshot.

Please can someone help me on this output and logic.

Thanks!

Veer

[Former Member · Accepted solution]

Thank you all . Sorry, I did not provide the exact requirement . Please see the below screenshot.

It will be clear.

My context is periodic . I am looking to find only the highlighted ones.

Thanks BPCuser ,I am unable to use this in my report. It is really a good tip .

Thanks Arnold. Time properties are already set up in the same way as mentioned.I believe I need a excel logic being dynamic in finding the exact quarter based on context.

Thanks Shrikant. you  are really close to my requirement. In your screenshot, you are showing F & G column , seems screenshot is truncated. Can you please resend it again with more details.

From my screenshot , i am already using concatenate year to Q1 in Col6. In Col 7  I am offsetting.

But the problem is , I am unable to make it dynamic.

Please can you provide detail logic if possible for Col6.

thanks!

Veer

[Shrikant_Jadhav · Accepted solution]

PFA

Shrikant

[Former Member · Accepted solution]

Hi Veera,

so, your reports shows the current period (= context), the same period for the prior year, the current quarter, the same quarter prior year and the full year plus the previous year.

Col 1: context

Col 2: context, offset -12 level 2

for columns 3 and 4 get the property for your period that shows which quarter it belongs to

col 3: concatenate that quarter with current year

col 4: concatenate that quarter with prior year

for columns 5 and 6 get the property that shows which year the current period belongs to

col 5: that year (concatenate with .TOTAL or whatever you use)

col 6: that year -1 (concatenate with .TOTAL or whatever you use)

BR,

Arnold

[Former Member · Accepted solution]

Hi Veera,

If your report structure is static in that case have a look at below thread.For calculation you  can simply use local members.

[former_member186338 · Accepted solution]

Hi Veera,

In terms of static report:

you can use simple Excel formulas to generate correct member names:

If you have month in A11 like 2015.04

="Q"&TRUNC((RIGHT(A11;2)-1)/3)+1

will result in Q2, etc...

Vadim

[former_member186338 · Accepted solution]

P.S. To be able to work with quarters selected in A11:

=LEFT(A11,5)&IF(MID(A11,6,1)="Q",RIGHT(A11,2),"Q"&TRUNC((RIGHT(A11,2)-1)/3)+1)

For months - will return quarter, for quarters - same quarter!

Vadim

[Former Member · Accepted solution]

Thank you Vadim, It is exactly my requirement.

Thanks!

Veer

[dinesh_veluru]

Hi Veera,

Please use the below formula in the hidden cell.

fetch the epm contextmember in cell G14.

=IF(RIGHT($G$14,2)="Q1",$G$14,IF(RIGHT($G$14,2)="Q2",$G$14,IF(RIGHT($G$14,2)="Q3",$G$14,IF(RIGHT($G$14,2)="Q4",$G$14,EPMMemberProperty(,G14,"PARENTH1")))))

override the time with the value u have received by using the above formula.

Thanks,

Dinesh.V

[Former Member]

Hi Dinesh,

Thank you ,formula working . but data is not getting populated due to EPMOlapMemberO is not recognized. I activated the member recognition also . Any clue ?

Regards

Veera

[dinesh_veluru]

Hi Veera,

Please refer the OLAP member of time dimension to cell containing the formula.

F2 is the cell which has formula deriving the quarter

= EPMOlapMemberO(F2,"[TIME].[PARENTH1].[2013.04]","APRIL","","000")

else please post your olapmember. i will let you know how to refer to the cell.

Thanks,

Dinesh.V

[Former Member]

Dinesh,

Can you please explain the formula once? Where did F2 reference come from.

My context member is referenced from cell W1 and hidden. so i had referenced that in your formula. as in screenshot.

please suggest.

Thanks!

Veer

[dinesh_veluru]

Hi Veera,

context member  is in cell W1

the formula lets put in cell X1

then Olap member will be

=EPMOlapMemberO(X1,"[TIME].[PARENTH1].[2015.Q1]","Q1 2015","","000")

Thanks,

Dinesh.V

[Shrikant_Jadhav]

Hi Veera,

Use static template concept. Populate the time member outside template. Activate member recognition. Refer that member in template. Turn off Activate member recognition.

Refer below screenshot.

Shrikant

[Former Member]

Hi Veera,

I would add a property to the time dimension which would show the quarter you want to see for any given period. So .01 to .03 and .Q1 would have Q1 in this property, .04 to .06 and .Q2 would have Q2 and so on.

Then call up this property for the time in the context and use excel to concatenate the value with the value of the current year.

Finally use EPMDimensionOverride to override your time dimension in the report with the value you have just created.

Hope this helps,

Arnold

[Former Member]

Hi Veera,

You can simply use member offset relationship in edit report as shown in below screen.

select context member and use memberoff set level=1.

Hope this will solve your problem.