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.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:
And the WBS picking up from row 18:
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.
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.
R1C1 rules of the road are displayed below – the top left is negative and bottom right is positive.
In this example we show a simple formula displaying R2C4 (D2 in A1 style.) The formula is read 0Rows-2Column plus 0Rows-1Column:
This example shows R5C5:
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:
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:
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!
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).
The resulting VBA formula shown below in B1 looks like this:
My days of waltzing with columns are over!
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