Skip to Content

EPM Add-in report with multiple Categories in SAP BPC, version for Microsoft and NetWeaver 10.0

With the new user interface in BPC 10, one might wonder how to create a report rendering two different Categories: Actual data for closed months and Plan data for the outlying months.  Follow the below instructions and you will impress your client in no time.

First, we need to create a property informing the system which months are closed or what the beginning Plan month is.   One way is to create a property on the Category dimension named OPENING_MTH and the administrator updates this monthly.  One advantage of this is your Categories can have different beginning months.  Another was is to create a property on the Time dimension (CLOSED) with valid values of “Y” or blank.  Either way will work and you should base your decision on the businesses requirements.  For this blog, we focus on the later and create a property on the Time dimension.

 

Create CLOSED Property on the Time Dimension

Login to BPC 10 Portal and click Planning and Consolidation Administration.

 

01_Portal.jpg

When the Administration tab opens, click Dimensions.

 

02_Dimensions.jpg

 

Highlight the Time Dimension and click Edit Structure (at the top).  Be careful to not double-click on the Time dimension as that will take you to the Time dimension members.

03_Time_Dimension

 

Once you are in the Structure of the Time dimension, click Add and then select New Property.

 

04_Time_Add_Property

 

Type in CLOSED for ID and Name will default with CLOSED.  You can change the Name to something else that is more informative like Closed Actual Months or Actual Months.  Enter 1 (one) for the Number of Characters as this will be either a “Y” or blank.  Click OK to when finished.

05_Closed

Save the dimension by clicking Save.

 

06_Time_Save

 

Click Close when the dimension has finished saving.

 

07_Time_Close

 

Either double-click on the Time dimension to open it or single click and choose Edit Members.

08_Time_Open

 

Find the newly created CLOSED property when the Time dimension opens.

09_Time_Closed_Propertyn

 

Enter a “Y” in all months, quarters, and Years that are closed.  In the below, I have enter a Y for month January, February, March, April, May and Quarter 1 for 2010.

10_Time_Closed_Y

 

Save and Process the dimension.

11_Time_Save_Process

 

When the splash screen asking to take the environment offline, click No.

12_Time_Offline

 

When the Time dimension has finished process, click Close.

 

13_Time_Close

 

Now it is time to start configuring the actual report.  To do this, click on the EPM Office Add-in for Excel.

14_Open_Excel

 

Choose the correct model (in BPC 7.x, this was an application) and click OK.

15_Environmentl.jpg

 

At the prompt, supply a valid user ID and password and click Logon.

16_Login

 

Since we are going to be creating formulas above the report, place your cursor in cell G7 and click New Report under the EPM ribbon.

17_New_Report

 

Drag the Account dimension to the Row axis and the Time and Category dimensions to the Column axis.

18_Report_Dimensions

 

Click the Category dimension to open the Member Selector.  If there are any members in the Selector Members box (on the left), highlight them and click the left arrow.  Choose Actual from the Dimension Members pane.  Ensure Member Only is selected for the expansion (at the bottom), and then click the right arrow.  Click OK when done.

19_Category_Dimension

 

Click the Time dimension to open the Member Selector.  If there are any members in the Selector Members box (on the left), highlight them and click the left arrow.  Choose a single month and select Member Only in the Selection Relationship dropdown (bottom).  Next, click the right arrow to bring the selection into the Selected Members pane.  When finished, click OK.

20_Time_Dimension

 

Click on the Account dimension and choose accounts that you have Actual data for.  In this example, I chose Context (Net Income) and to show the Member and Children.

21_Account_Dimension

 

Now that the report layout is complete, click OK.

22_Report_Layout

 

The report should look like below, assuming you used the same dimension members.

23_Report

 

In cell F1, type in the formula =EPMContextMember(,”Time”).  Since there is only one active connection, the first parameter can be left blank.  The function EPMContextMember is the new version of EVCVW from BPC 7.x.  Click OK when finished to save the formula.

24_Current_Context

 

In cell G1, type in the formula =LEFT(F1,4)&”.01”.  In my environment, the months are represented by numbers (01, 02, 03, etc), but if you can just as easily have .JAN, .FEB, .MAR, etc.  This will be taking the current year in cell F1 and adding a suffix of .01.  Basically, our report will always start with the first month of the year, in this case January.

25_Current_Month

 

In cell G2, type in the formula =EPMMemberProperty(,G1,”CLOSED”) where G1 is the first month of the year and CLOSED is the property you defined in the Time dimension.  EPMMemberProperty is the new version of EVPRO.  Again, since we are only using one connection, the first parameter can be blank.

26_Current_Month_Closed

 

In cell G3, type in the following formula: =IF(G2=”Y”,”Actual”,”Plan”).

27_Actual_IF_Statement

 

In cell H1, type in the formula =EPMMemberOffset(,G1,1).  EPMMemberOffset() is the new version of EVTIM().

28_EVTIM

 

Next, copy the contents in cells G2 and G3 to H2 and H3, respectively.

29_Copy

 

Copy the contents in cells H1, H2, and H3 out 10 (ten) columns to column R.

30_Copy_2

 

Next, we need to manufacture the time dimension.  In order to do this, click in cell G5 and enter EPMMemberDesc(G1) for the first parameter.  Click OK when done.

31_Create_Time

 

We are going to do the same thing for the Category dimension.  In cell G6, type in EPMMemberDesc(G3) to display the correct Category Member.

32_Create_Category

 

Now, copy cells G5 and G6 out to column R.

33_Copy_Dmension_Formula

 

Click the Refresh Report and two different Category dimensional data will be rendered in the same report.

34_Final

 

Bonus:

Now that you have created this, it takes one formula to create a Rolling 12 report.  In cell G1, enter the following formula, change the Time Current Context to 2010.Q2, and click Refresh:

=IF(EPMMemberProperty(,F1,”Level”)=”Year”,LEFT(F1,4)&”.01″,IF(RIGHT(F1,2)=”Q1″,LEFT(F1,4)&”.01″,IF(RIGHT(F1,2)=”Q2″,LEFT(F1,4)&”.04″,IF(RIGHT(F1,2)=”Q3″,LEFT(F1,4)&”.07″,IF(RIGHT(F1,2)=”Q4″,LEFT(F1,4)&”.10″,F1)))))

 

35_Rolling

To report this post you need to login first.

13 Comments

You must be Logged on to comment or reply to a post.

      1. Former Member

        Thank you.

        EPMMemberProperty = EVPRO
        EPMMemberOffset = EVTIM
        EPMMemberDesc = EVDES
        EPMContextMember = EVCVW

        EPMRetrieveData = EVGET
        EPMSaveData = EVSND

        (0) 
  1. George Serfiotis

    Very interesting document. It describes exactly the specifications set for a report I have to create. Nonetheless,

    I get the following message both after step =LEFT(F1,4)&”.01” and step =IF(G2=”Y”,”Actual”,”Plan”): “Unable to

    Recognize the report Default Report: The same dimension (TIME) has been found in several axes. The initial

    definition will be applied.”.

    In both cases the cells (G1, G3) end up having as value the value itself (2009.01 / ACTUAL) and not the specified

    function that displays the value.

    Any idea why this is the case?

    (0) 
      1. George Serfiotis

        Dear Robert,

        thank you very much for your prompt reply. I have checked the sample video on the link you provided and tried to implement the steps illustrated. Unfortunately, once again I have got the same “error” mesage. Could you please take a look at the following short recording where the steps I follow and the error are shown? http://www.youtube.com/watch?v=Dgxaa7zQ9w8

        (0) 
        1. Former Member Post author

          George,

          The recording stopped before the error presented itself.  Please send a Word doc showing the steps and the error to my email address (r.marshall@sap.com)

          Also, on the EPM Add-in Academy, there is a sample workbook showing the final product of the Rolling 12.  Did you download this and if so, do you have the same issue?

          Thanks,

          Rob

          (0) 
  2. SAP User

    Robert,

    Very good post.I checked the sample video for Butterfly report in the wiki page.

    Can you suggest any links on how to create multiple reports in a same sheet and in multiple sheets with different connections?

    (0) 
  3. Former Member

    Hi,

    Thanks for this. I wonder why it keeps on expanding as symmetrical even after applying the steps above? specially when i try to remove the zeros in row key range.

    any idea? because we need to apply the suppression of zeros in rows, and every time we apply the suppression, the col becomes symmetric.

    Thanks as always,

    yajepe

    (0) 
  4. Former Member

    Hi,

    Will the above steps (multiple categories in single report) are applicable even when the Suppress empty in rows are selected?

    Because when I tried applying suppress empty rows the col expanded and became symmetrical.

    I wonder what went wrong, please advise.

    Thanks as always,

    yajepe

    (0) 
  5. Former Member

    It’s a great article and I want to thank you very much for your post! It might have been a long time since you wrote it, but still extremely helpful and I’m glad I found. Just wondering if I can bug you with a follow up question. It would be great if you can give me some suggestion!

    So I followed your step and at the very end, I have a rolling forecast report. But I have an extra requirement from my client: they want an additional column to total All the Actual and Plan months together to see how much they have deviated from their original budget number. I added a Local formula but I’m not sure how I can save it back, I can always use EPM save, but then from a designing point of view, should I add another Version to save that total there? Just so it can be retrieve later. Or if I don’t add another version to save it back, then every time when they open the report then the calculation will just happen again with the Refresh? Just wondering what would be the best practice for this requirement.

    (0) 

Leave a Reply