Skip to Content

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

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.



When the Administration tab opens, click Dimensions.




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.



Once you are in the Structure of the Time dimension, click Add and then select New 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.


Save the dimension by clicking Save.




Click Close when the dimension has finished saving.




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



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



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.



Save and Process the dimension.



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



When the Time dimension has finished process, click Close.




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



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



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



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.



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



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.



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.



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.



Now that the report layout is complete, click OK.



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



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.



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.



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.



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



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



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



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



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.



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.



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



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




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:




You must be Logged on to comment or reply to a post.
  • 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?

  • 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?

  • 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,


  • 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,


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