How to set columns order including Local Members

[Former Member]

Hi!

I have a report with three columns, lets call them: "Real", "Budget" and "%"(local member). The last one is basically "Real/Budget". Thing is, I want to set this last column as the second column in my report so I will have "Real", "%" and "Budget", but in the Sorting tab of the Report Editor you can't actually select a Local Member in the Sorting Criteria, only the other two. Thus, the order will be "Real", "Budget" and "%".

Any ideas/workarounds? Thanks in advance!

[Former Member · Accepted solution]

Hi Lago,

I am guessing Real and Budget are the categories. If yes, are these going to be constant? If yes, then you don't need any expansion on the columns. In that case, you can just hardcode them as per your desired sequence. Even if they are not static, you can use multiple expansion in the column using the "|" operator. Keep one column empty in between, and put the formula for % in that column.

Hope this helps.

[Former Member · Accepted solution]

Hi Nilanjan,

Yes, Real(Actual in the picture) and Budget are CATEGORY members, although Budget(Presupuesto v1 in the picture) won't be static, that's why I want it to be on the right side. Thing is, I insert an empty column between both categories, and put the % (% Variación in the picture) formula.. but after refreshing, it keeps moving the Budget (Presupuesto v1) next to the Actual one.

[former_member186498 · Accepted solution]

Hi Iago,

I'm not sure I've understand your problem but if you want mantain the expansion on the "% Variacion" column you may just add 2 empty columns right side of the % in the ColKeyRange changing the EVRNG, f.e. from "=EVRNG(G14:H14)" to "=EVRNG(G14:H14; J14:K14)" leaving the J14:K14 cells empty, so even the % will be expanded.

Kind regards

     Roberto

[Former Member · Accepted solution]

Hi Iago,

Are you using BPC 10? If yes, edit the report, go to the local member tab and have a play around with the items highlighted in green.

Hope this helps

[Former Member · Accepted solution]

Hi Roberto,

Thanks for your answer, but no, the "% Variacion" is not going to be expanded. "Presupuesto v1" will be expanded, thats why I want it on the right column. And the column "% Variacion" (the formula is "Actual/Presupuesto v1 %", basically) in the middle.

Looking at the picture, what I want to do, is basically, swap "Presupuesto v1" for "% Variacion" column. But when I do it, and then refresh the workbook, it keeps going back to the position shown in the picture.

Kind regards,

Iago.

[Former Member · Accepted solution]

Hi Lago,

What you need to do is to modify the colkeyrange.

For example, if your colkeyrange is "=EVRNG(G14:H14)". Please change it to "=EVRNG(G14:H14; J14:K14)". And you expansion should be something like ACTUAL|PRESUPIESTOV1.

With this technique, you are breaking the column expansion in 2 parts. First expansion will take place from G to H with ACTUAL, and the second expansion will take place from J to K for PRESUPIESTOV1. Now, you will get 1 blank column in the middle - column I. Place your formula in this column.

Hope this helps.

[former_member186498 · Accepted solution]

Hi Iago,

can you please post the key range, expand range and PageKeyRange of your evdre?

Kind regards

     Roberto

[Former Member · Accepted solution]

Hi Arnold,

I tried to use this feature before, but the "Insert at Position" radio button is disabled, so I can only select Before or After... but not to put the local member column in the middle.

[Former Member · Accepted solution]

Hi Roberto,

I use the BPC 10 version, so I don't have any EVDRE code. I don't know if this would help but my column  for Actual is defined as following:

=EPMOlapMember("[CATEGORY].[H1].[ACTUAL]";"";"Actual";"";"000")

[Former Member · Accepted solution]

Hi Lago,

Have you tried inserting a blank column, manually? Try doing this.

Alternatively, you can also consider the option of inserting a "Blank" member.

Hope this helps.

[Former Member · Accepted solution]

Hi Iago,

it becomes available when you attach the local member to either the axis or the dimension but not the member or member combination.

[Former Member · Accepted solution]

It actually becomes available only when attached to a dimension, but not axis.

[Former Member · Accepted solution]

I already tried that.

In the moment, I write down the formula: "=G10/I10" in the 10th row of this column (H)... the formula is applied to the whole column(great), but swaps position with the next column.

[Former Member · Accepted solution]

dimension

axis

Maybe it depends on the formula you are using

[Former Member · Accepted solution]

For some reason it doesnt work at all when you try just a simple formula as:

= I15/G15.

I give up, and I'll just put the column in the first column, so even if you  expand(Member and children) the last column, you'll keep the "Variation %" column just next to the other two column.

[JohnL · Accepted solution]

Hi Lago,

What version of the EPM Add-In are you using?

I just tried this with the latest (SP8 Patch 4) and I was able to get this working using the "Use Excel Cell References" option of the Local Member tab.

See below:

Thanks,

John

[Former Member · Accepted solution]

Hi John,

I upgraded the EPM to the latest SP08 patch 4 released on the 26th of April, and now I have this checkbox available. Thanks a lot!

[JohnL · Accepted solution]

Hi Iago,

You are on a version SP05 + patch 3 = 10.0.0.7143   SAP NOTE 1653310

See Note 1366676 (at the bottom) to determine what version you are on.

I would upgrade to the latest version which is SP08 Patch 4  SAP Note 1707187

Thanks,

John