Skip to Content
avatar image
Former Member

BPC NW 10: hide columns dynamically

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

1.JPG (71.2 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Jul 16, 2013 at 01:07 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Potturi:

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

      thanks anyway

      mah

  • avatar image
    Former Member
    Jul 16, 2013 at 07:52 PM

    Hi,

    Use VBA to achieve this.

    Regards,

    Kalyan.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member 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.

  • avatar image
    Former Member
    Jul 17, 2013 at 05:12 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Aug 08, 2013 at 02:26 PM

    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

    Add comment
    10|10000 characters needed characters exceeded