cancel
Showing results for 
Search instead for 
Did you mean: 

Local member

Former Member
0 Kudos

Hello everybody,

I am currently working on SAP BPC 10 MS and I have a trouble with a local member.

I have 2 reports in 1 sheet.

As you can see on the screenshot, the columns "P_INTERCO" and "CONTROLE" are part of the first report.

This is a static one. Then, you have the second one, which is dynamic. The green cells correspond to the entity code.

The red ones are the headers and correspond to the description of the entity (using epmmemberdesc()).

In the second screenshot, you can see my local member ("EN_TETE_COL") formula.

Everything works fine except that the header of the ENTITY 1 keep the same header as P_INTERCO (not represented here).

The issue is that the local member begins from scratch in the 2nd report (column ENTITY 1).

P_INTERCO = I45

CONTROLE = J45

P_S0008 = K45

So the test of the cell K47 is IF(I45="P_INTERCO","Total à ventiler",epmmemberdesc(K45)). I have an offset.

Does anybody has something to provide?

Thanks you in advance for your help!

Kind Regards,

Bastien

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hello Bastien......in Future Please provide the screenshots in English!

Without providing the row and column headers how should we understand the formula??? Please provide them in the screenshot! And where have you added the local member? In 1st report or 2nd report? Where do you want the local member to be? before column axis?

Your current local member has not been applied, from the screenshot I see! Tick the "Apply" option.

Regards,

JP

Former Member
0 Kudos

Hello JP!

I hope the screenshot below will help you more to understand my issue.

I have added my local member in the first report (but I have also tried to put it in the second one).

Concerning the "Apply" it is a mistake in my screenshot. Of course, it was ticked.

The local member is "EN_TETE_COL" which is at the good place in the first report. I would like it to apply the formula dynamically for the 2 reports.

As you can see, all the entities have the good description (i.e P_S0100 gives the right description (of course it is not "ENTITY 2" but for confidentiality I cannot give the right name)).

The issue is only for the description of the column "K" as you can see in the screenshot below

I hope my explanations are clearer.

Ps: Sorry for the language but I currently work on a french version and, unfortunately, I cannot change it.

Thank you for your time

Regards,

Bastien

Former Member
0 Kudos

Bastien..Its a bit confusing....But are you trying to say that the local member is not working on the second report??? Should the cell reference I45 fixed??

Try using the option "Use Excel References" from the Local member settings....and let me know.

Regards,

JP

Former Member
0 Kudos

P.S for the second report..you can override the local member formula using the Formatting sheet!.

Under the Dimension member/property section, include the local member and override the content with Formula "=IF(K45="P_INTERCO","Total à ventiler",epmmemberdesc(K45)). Apply.

This will override the formula from the Edit Report definition.

Hope this solves your error..

Regards,

JP

Former Member
0 Kudos

Hi Bastien,

The problem you are facing is the standard behavior of system. Since Local member is part of first report so when report change to default to report 001 same definition will be apply.

For you problem you can use two formatting sheets.

use second formatting sheet to second report.

use same function just change the cell reference and use content option.

Hope this willsolve your problem. Still if you are confused the please let me know.

Thanks

Former Member
0 Kudos

Hi BPC user!

I have tried your solution, but, unfortunately it still does not work

I still have an offset when the test of the cell is being done.

Thank for your help!

Kind regards,

Bastien

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi guys!

Sorry for late reply, I was trying to make it work.

@JP: I have 2 formatting sheets. I am quite sure I must include the members to color rows (to respect the specs)...

Now I don't have issue with the first report. Only with the 2nd one.

As shown below :

The formula in OI58 still test with P$53 (cell in the first report). It is the same issue that we have from the beginning. So I have put the formula in the 2nd formatting sheet :

With OH53 as we said. But it does not work..

Do not know why the formatting sheet does not override in this case..

Is there something different from the previous case?

@BPC user : It is not acceptable and unfortunately the memberdesc will not override the local member of the 1st report??

Thank you for your time guys!

Regards,

Bastien

Former Member
0 Kudos

Hi Bastien,


If it is not acceptable fine. But you can use if condition in your formula also something like given below.



Hope I am clear with this.




Former Member
0 Kudos

Bastien....In the second formatting sheet, the local members name seem to be EN_TETE_COL etc. But in the report screenshot I see ECART_1 and so on.....Is this the reason it is not overriding?

Plus look at the priority of the formats applied priority to row or priority to column in formatting sheet! The local members in 2nd report are in column, have you added the loacl member in column section?? Sorry since the screenshot is not in English...I am asking you this..

Look at these option...apart from this I don't know any possible reason for the formula to not get applied.

Regards,

JP

Former Member
0 Kudos

Hi guys!

Thank you so much for your help!

JP your solution works perfectly it is the good formula to insert !

Kind Regards!

Bastien

Former Member
0 Kudos

Hi Bastien,

Glad to see that your problem is resolved.

With my approach you will required 2 formatting sheet. 1 st formatting sheet for the  first report and second for the second report. in second formatting sheet you have use that content option. you need to use normal function if condition is not required to use.

In second formatting sheet with multiple combination you have use like local member name and entity members than use that content option


If you see my screen shot i have used add to multiple selection option.




In above screen shot Test is my Local member and with property selection I have selected base level member from my entity dimension. Once again check my screen shot for more clarity.

Hope this time I have given your answer correctly. still if you are not clear  please let me know.

Former Member
0 Kudos

Actually, I have ever tried this kind of solution but bad.

However, in this situation, the first report is static and the second one dynamic.

But in another case I have, this is the opposite. The first one is dynamic and the second one static.

I have the same issue with the formula because in this case I cannot know the length of the first report and so on I cannot put a static reference in the formatting sheet..

Do you have an idea or should I start by the static one and after that the dynamic (but in this case this will not respect the specs)

Kind regards,

Bastien

Former Member
0 Kudos

Hey Bastein,

If I understood correctly your this requirement, then no in other case you have 1st report as dynamic then 2nd report static......

Do you have the same formula in the local member in this report too? If yes.....then it will be a complex one to build.

I don't know whether it is suitable for you or not, but in my mind there is one idea......Idea is to use the "Shift" option from the Edit Report. For the first dynamic report using shift you can shift the second report at distance of fixed columns(don't know how many members you have) and start the 2nd report at a fixed column. Then use the same approach for the local member formula as we discussed above.

I haven't tested this....Don't have a system to test.....But may be you can try and let me know....

Hope you understood my solution...

Regards,

JP

Former Member
0 Kudos

Hi JP!

I think it is a complex one to build.

You will find in the screenshot below the specifications I have to respect

It is the model I have to follow.

The rows are statics (consolidated account plan).

Both reports are seperated by an empty column.

For the columns :

     - 1st block is dynamic : expansion on the ORGANIZATION or LP dimension according to the axis of selected analysis (I have a dropdown list with which one the user selects either Organization or LP) with filter according to the selected level of detail (ALL, DEP, BAS; with right parent to its children)(here is also another dropdown list with 3 choices).

For this situation I have created a dimensionOverride which works.

     - 2nd block is static.

The coloring convention is:

     - red : headers

     - green : olapmembers.

     - grey : local members

Here is my local member formula

which I have put in the 2nd formatting sheet as we have discussed before.

But now the issues are :

     - Do not know if it will always be PS54 for the first column of the 2nd report.

     - The formula in the 2nd formatting sheet overrides the local members (in grey)

I do not know if I am really clear but it is a bit complicated to explain..

I have a shift to seperate the 2 reports.

Regards,

Bastien

Former Member
0 Kudos

Yea....It is complex...but can be managed with work arounds.

For the second report only I said that use Shift option. Currently you have P$54, But I say that as per the maximum space required for the expansion of 1st report dimension shift the columns to right and fixed the first cell of the second report!. Hide other unnecessary columns!

- The formula in the 2nd formatting sheet overrides the local members (in grey) - I dint get this point.



Regards,

JP

Former Member
0 Kudos

My 2nd report is "static"

Normally if I copy and paste the tabulars of the specs, it should work.

But indeed, due to formula in the formatting sheet which overrides the local member of the first report, I have the feeling that the 2nd report becomes "dynamic".

In the 2nd report, I need 4 local members for calculations (grey columns).

Below the formula of one of them

The issue is, as you can see, the local member formula does not apply.

Moreover, I am confused by the way.

Does the 2nd report is still static or dynamic?

Because, if it is dynamic, I should use the 2nd formatting sheet and, as you can see in the specs, I have multiple rows colored and so, it is almost impossible to reproduce exactly the same (I think).

Kind regards,

Bastien

Former Member
0 Kudos

Anyway, I have found a "bad" solution.

Put each member in the formatting sheet and apply the formatting..

I am currently doing it, and it looks like working.

Regards,

Bastien

Former Member
0 Kudos

Hi,

Sorry for late reply.

If it is acceptable to your business then you can create first report as static and second one is dynamic or first report dynamic and second report static.

If first report is dynamic and second one is static then it;s hard to find out from where second report is getting started.

For your case there is another way don't know it is acceptable to you. You can achieve it without using formatting sheet also.

Use EPMMemberDesc(cell no) function outside your report drag the formula as much as your report can expand.It will not be dynamic but it will work.it will not hamper performance also.

Just keep below setting active for your report.

Hope this will help you.

Former Member
0 Kudos

Hey Bastien,

Are you using only single formatting sheet to both the reports?? I would recommend to use 2 formatting sheets, so that the formula convergence doesn't happen!.

Moreover the second report to me appears to be partly dynamic and partly static. Static because of members in report and dynamic local members.

I guess if you use 2 formatting sheets then there is no need to include all members in formatting sheet and then format them!

For the local member formula....try to work with the formula on excel first! Since you have given a screenshot I am not able to test it....But might be the conditions are not completed.....Check that. Do you get any error for that formula?

Regards,

JP