cancel
Showing results for 
Search instead for 
Did you mean: 

Repetitive local members

sap_user62
Active Participant
0 Kudos

Hi Friends,

Env 10.1/7.4

I have a requirement to create a report which has lot of calculations. These calculations are kind of repetitive for every set of profit center nodes.

currently we have 4 top nodes of the profit centers

sample layout

                    Profit center node 1  (we will have 5 sets)                                       

                    actual                         Plan

                    CY     PY  LM1 LM2     CY   PY  LM3  LM4

GL Account

LM - local member (calculation)

These 4 local members would repeat for 4 sets of profit center nodes and the parent node, In total I am having around 20 local members.

This report needs expansion on GL Account, so i have created local members based on position (as columns will not need any expansion)

like LM1= sum(epmposition(1),-epmposition(2)) ... etc and attached it the column axis at position(3) , 4 etc

this requires careful detailed work.

I was checking if there is an easier and elegant way to design a report.

Thanks for your time

Ed

Accepted Solutions (0)

Answers (3)

Answers (3)

former_member186338
Active Contributor
0 Kudos

epmposition is not bad option...

Vadim

P.S. Or you can use absolute column reference

sap_user62
Active Participant
0 Kudos

Thanks All for your replies

I have attached a copy of the report.

JP-

CY and PY are not the members of the dimension. I just used it to show current year and prior year fiscal period. If i attach a local member to this time, it would disappear if we change the context want to view the report for a different time period.

Andy-

could you explain how could I use member sorting and grouping feature in my current scenario.

Is there a limitation on the number of local formula in any report? We know if would affect performance,

but we need calculations in the reports. other alternative to local members is member formulas, not sure if that is a better alternative.

Thanks again for your time

Ed.

former_member186338
Active Contributor
0 Kudos

"Is there a limitation on the number of local formula in any report?" - no limitation...

You have to use some complex formula like:

=INDIRECT(ADDRESS(ROW(),COLUMN()-2))/INDIRECT(ADDRESS(ROW(),COLUMN()-1))

This formula in the local member (attached to time dimension - after!)

Vadim

former_member186338
Active Contributor
0 Kudos

Sample:

former_member186338
Active Contributor
0 Kudos

The second local member has to be attached after the first local member like:

sap_user62
Active Participant
0 Kudos

Thanks Vadim,

I trying to fit in your formula to the report needs. working on several hit and trial runs.

(just replacing "/" by "-")

If you take your calculation at G8, for my report it should be

G8 = C8-B8

if I attach as per your formula mentioned in your screen shot its coming as

G8 = F8-E8

Ed.

former_member186338
Active Contributor
0 Kudos

My formula is very simple:

INDIRECT(ADDRESS(ROW(),COLUMN()-OFFSET))

Will reference the cell OFFSET cells left to the cell with local member!

It's just basic Excel

For example: If the local member is in G8

Then: INDIRECT(ADDRESS(ROW(),COLUMN()-1)) will reference F8

INDIRECT(ADDRESS(ROW(),COLUMN()-2)) will reference E8

Sorry - corrected "," instead of ";"

former_member186338
Active Contributor
0 Kudos

"G8 = C8-B8"

=INDIRECT(ADDRESS(ROW(),COLUMN()-4))-INDIRECT(ADDRESS(ROW(),COLUMN()-5))

former_member186338
Active Contributor
0 Kudos

Another option is to simply use: =C8-B6 in the local member

When the local member is applied the references will be recalculated!

sap_user62
Active Participant
0 Kudos

Thanks Vadim, I am using the Indirect formula that you have mentioned. only problem with it, the first local member is attached to time dimension, it will repeat for every set of data.. I just need to selectively hide the unwanted local members in my report, ones in purple

I have noticed, if you use pure excel formulas in the reports, when we expand it does not calculate the expansion, [local member recog not checked on]

and if we create a local member with excel calculation, it attaches itself with the dimension combination, and any change in the context will make the local member disappear.

[local member recog checked on]

Ed

former_member186338
Active Contributor
0 Kudos

"I just need to selectively hide the unwanted local members in my report, ones in purple" - first of all you have to CLEARLY explain your requirements!

I can help, but I have to know what do you want

"and if we create a local member with excel calculation, it attaches itself with the dimension combination, and any change in the context will make the local member disappear." - wrong!

Please start again and provide the logic of calculations!

sap_user62
Active Participant
0 Kudos

sure Vadim. please find the actual requirement.

former_member186338
Active Contributor
0 Kudos

Do you really think that this screenshot is readable without Excel column and row labels?????????????

former_member186338
Active Contributor
0 Kudos

And may be you can spend some time to explain in words the logic of time and category selection?

P.S. On the last screenshots it's not the same as in the original post!

sap_user62
Active Participant
0 Kudos

Apologies, the screen shot was not complete.

Rows - GL account

Columns

Profit center

Category

Time

Actual and CTA are categories

I use the context to get the time, and use offset to calculate the prior year.

Thanks for your time

Ed

Former Member
0 Kudos

Hi,

Have you tried using the option " Use Excel cell references" in the local  member??? Tick this option in the local member and see whenever the local member gets repeated in the report the Excel formulas are adjusted as per the references, typically like Excel functionality!

Attached the screenshot of the option!

And to hide the unwanted local members you can select the column and right click-->Hide, Hide the columns using normal excel functionality....I will not unhide upon Refresh.

Hope this helps.....

Regards,

JP

former_member186338
Active Contributor
0 Kudos

Absolutely strange calculation logic... Can you explain the business value of each calculation?

sap_user62
Active Participant
0 Kudos

I could understand Vadim, the column are not arranged well. But this is how the client has been publishing his data, from legacy and they want to use the same format in bpc reports too

I could not change the ordering of the columns, but simplified the "if" statements.

The values or the % they are looking for is, in comparison to the prior year.

I was able to use the indirect formula that you have mentioned, attached it to the column axis, and did some work around hiding extra calculations.

Its not an elegant solution, but cant reorder the columns.

Thanks again Vadim and Jp for your inputs. Always valuable.

Ed.

Former Member
0 Kudos

Hi Ed,

Is CY,PY members of any dimension? If Yes..then you can add the local member after the PY member instead of position.

What are the calculations done in the local member? 20 local members are too much too handle in a report....I don't think 20 members are required!

Please share the screenshot of the report structure to understand carefully.......

Regards,

JP

Former Member
0 Kudos

Hi Ed,

You can try using the member sorting and grouping function in the member selector column axis.

Andy

former_member186338
Active Contributor
0 Kudos

sorting and grouping will not help - only single local member inserted...