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.

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)))))