<img src="https://certify.alexametrics.com/atrk.gif?account=3HHNq1DlQy20Y8" style="display:none" height="1" width="1" alt="">
Man-Computer-Laptop-Coffee-Cafe-Concept-000079551979_Large-1.jpg

SAP BPC & EPM
Thought Leadership

Waltzing with Columns in the EPM Add-in for SAP BPC

Posted by
Deb Silverman
Deb Silverman
on Wed, Jan 17, 2018 @ 08:01 AM
Featured Blog Photo-12.png

We all encounter game-changing challenges, so here’s my story of how a simple task we do every day has taught me that things are not always as easy as they seem. 

When using overrides with rows, the column keys sometimes shift around.  We all have our strategies, however I discovered an issue with an EPM tab containing multiple reports (000, 001, 002 +...) not sharing a column axis. Typically, local members are positioned or anchored and methodically built with the expectation they’ll stick.  I hit a wall and was lucky a colleague was sitting nearby to share ideas on how to fix this issue. 

She told me to try using R1C1 in the OLAP keys.  Below, see
 a report’s column keys. The upper section of the tab contains all the drivers to derive time (category, etc.)  These values are linked into the OLAP key (row 54):

1.png

Waltzing with columns to get them to ‘stick’ was not on my agenda for the day – these things drag productivity and kill deadlines.  Everyone has these days, regardless of experience level, and the timing is never good.  

Here are the results -   Notice TIME 2016.OCT OLAP has an INDIRECT plus the R1C1 method from row 14:

2-4.png

And the WBS picking up from row 18:

3-3.png

Tip:  Using the R1C1 referencing method can produce more reliable and consistent results.  You can use the R1C1 method on a spreadsheet set up to use A1 style – just be sure you understand how the logic works and then type it in manually. 

What is R1C1 reference?

Most of us reference rows and columns in Excel using the A1 reference style– by column letter and a row number. R1C1 reference style uses a number for both the column and row. In the snapshot you can see R1C1 where R is row and C is column. 

4-4.png

R1C1 rules of the road are displayed below – the top left is negative and bottom right is positive.

5-2.png

In this example we show a simple formula displaying R2C4 (D2 in A1 style.)  The formula is read 0Rows-2Column plus 0Rows-1Column:

6-1.png

This example shows R5C5:

7-1.png

Another example uses R1C1 for a local member sum(), another method for your toolkit. The RC-Style Sum() for the year 2017 local member is defined below:

8.png

Now, you can see the column below sums -1 to the left, -2, -3, etc. When we close the local member editor and refresh the report, we will see the typical A1 style reference - learn how to sneakily control position with this tip:

9.png

Excel formatting can be controlled using the Options menu. There is no need to set the referencing style for R1C1 each time you want to use it – you can turn it off and on to see the position of your cell, for programming purposes.  You can use both to build a report, leaving A1 style as the default. Understanding how R1C1 works allows you to choose the correct RC-value.

  
🌎 Join us for the largest gathering of SAP BPC users in the world at the upcoming EPM Leadership Summit in Las Vegas March 12-15!

view agenda

VBA tip-1 

In the example below, we sum an entire column without deriving the last row, saving several lines of code! If we were to do this in excel it the formula would be =SUM(R2C:R[-1]C).

10-1.png

The resulting VBA formula shown below in B1 looks like this:

11.png

VBA logic:

 12.png

My days of waltzing with columns are over!

Related Articles: 


deb_silverman.pngAuthor Bio:

Deborah Silverman has been with Column5 Consulting for almost 5 years as a Senior Consultant. She is one of our most popular blog writers with her decades of software experience. She has assisted in hundreds of implementations and specializes in SAP BPC. 

Deborah Silverman, Senior Consultant, US Team

 

Topics: EPM Add-in, Product Focused

Subscribe

Recent Posts