BPC NW 10: hide columns dynamically

[Former Member]

hi bpc experts:

I have a situation where I need to show in column axis: all base level members of entity,category (REAL and the one from the context) and base level members of year (time dimension), it works fine but I need to hide the columns based in the category, for example: for category = M03 (which means that january,february and march are already closed), in version REAL I need to hide from 2014.04 until 2014.12 and for M03 I need to hide from 2014.01 until 2014.03.

As you can see in the image Report 1 is the default layout and Report 2 is how it is supposed to be. 

I ve seen I can use the "excluded members" option from the report editor, but I need this to be dynamically, may be using the funcion EPMSupress using something like EPMSuppress("000",FALSE,"AR,REAL,2014.04") but I m shown this error : Members of Dimension CATEGORY can't be used to suppress on this axis as there are not from the outer dimension of the axis.

Regards

mah

[Former Member]

Hi,

What I think you need is a mix of EPM and Excel Functions..

Please refer the following links:

http://scn.sap.com/docs/DOC-30341

http://wiki.sdn.sap.com/wiki/display/CPM/EPM+Add-in+Academyhttp://wiki.sdn.sap.com/wiki/display/CPM/EPM+Add-in+Academy   (planning using properties).

Best Regards,

Lucas

[Former Member]

Hi,

You might use 'EPMDimensionOverride' function if you have one or two such conditions. Otherwise VBA should be a good option. You can try something like this:

=EPMDimensionOverride("000","TIME",IF(CATEGORY="REAL",BAS(2014.Q1),BAS(2014.TOTAL)))

Regards,

Neha

[Former Member]

Hi,

Use VBA to achieve this.

Regards,

Kalyan.

[Former Member]

Hi Kalyan:

Have you tried using epmsupress() ?

Wondering if you can share some VBA code to achieve this.

regards

mah

[Former Member]

Hi,

You have to first find out the columns you want to hide from column B to column Y. In the code you check what is the category selection and accordingly you hide the required columns.

For e.g.,

When user select REAL and M03 use this code:

IF Range("B4").value="REAL" AND Range("N4").value="M03" then

     Range("E4:M4,Q4:Y4").EntireColumn.Hidden = True.

End If

Similarly you have to write code for other comination.

Hope this helps.

Regards,

Kalyan.

[former_member190501]

Hi,

You can try as follows :

1. Maintain a new property (eg.Category) in Time dimension and maintain values as follows:

      Time           Category

      2014.01       REAL

      2014.02       REAL

      2014.03       REAL

      2014.04       MO3

      ....

      2014.12       MO3

2.   Create a report with Entity, Time and Category in columns and select following from Member selector.

      Entity - Select All Base members

      Time  - Base members of context

      Category - Linked to property 'CATEGORY' of dimension 'TIME'  ( You can choose this option from drop down list of selection relationship called 'Dimension Property')

Only disadvantage of this method is every month you have to maintain property of Time with proper values.

Hope it helps..

regards,

Raju

[Former Member]

Hi Potturi:

Thanks for answering, but it looks like my user is not buying this solution since requeries maintaince.

thanks anyway

mah