SCN member MotaEngil Developers asked in the SAP BusinessObjects Planning and Consolidation, version for SAP NetWeaver SAP Planning and Consolidation, version for SAP NetWeaver how to achieve BPC reports - zebra formatting for Excel reports. I will demonstrate two ways to achieve such an outcome using the advanced formatting capabilities of EvDRE formulas in BPC.
Let us start with a simple EvDRE report containing four rows with three of them being base members (Accounts CE0004010 to CE0004030) and one calculated total (Account CE0004000).
The intention is to have every other row in the report, be formatted with a color different from the one immediately above or below it. Hence the name "zebra" formatting. Presumably such a requirement is aimed to improve ease in readability of a long report with many rows.
The following is a picture of the report we seek to create, while keeping in mind that the rows in the report are not static and will vary depending on the user navigation and the data which exists in the BPC system. However the EvDRE formula which produces this report handles this situation perfectly. Afterall, EvDRE means (Everest) Dynamic Range Expansion. Everest being an old code name for the product, if you were not aware.
Having explained the requirement and shown the result, now let us turn to the details behind the solution.
The report is based on using the EvDRE formula which contains a FormatRange. In this example it is defined as cell block Sheet1!$A$45:$F$61.
The FormatRange was automatically created as a default by the EvDRE wizard as follows:
We can relate these instructions easily to the report which was shown above. For example it show that DATA elements in the report get a number format expressed with a comma representing the thousands place and a period indicating a decimal place. The DATA cells also have a single black border. Totals rows are defined using the CALC property and have a similar formatting design, but also have a yellow pattern. The dimension member key values get a light green color pattern.
To achieve the zebra stripe effect, we have to add a new row within the FormatRange (adjust cell B9 as applicable). I have copied row 46 to row 56 and then changed the CRITERIA from DEFAULT to INPUT.
Next apply the Excel technique for Conditional Formatting to cell C56. Conditional formatting is available from the Home ribbon within the Styles group in Microsoft Excel 2007.
Create a New Rule and select the Rule Type called "Use a formula to determine which cells to format".
In the input box type in either of the following formulas:
=MOD(ROW(),2)=0 or =MOD(ROW(),2)=1
Before we continue, let us look at the meaning of these formulas. The first formula checks to see if the row number is even and if true applies the conditional formatting. The the second formula is similar but affects only odd numbered rows. You can see this result is accomplished with the combination of using the Excel functions =ROW() and =MOD(). The latter being a formula that returns the remainder after a number is divided by a divisor.
Next select the Format button and goto the Fill tab and select a color which you want to create the zebra stripe effect.
After clicking OK two times to close the conditional formatting dialogue, the spreadsheet will look similar to the following. (It may not actually change color if the first formula was used in an odd numbered row, or visa-versa.)
At this point all that is necessary is to Refresh the BPC report and the conditional formatting will be applied to all the rows dynamically returned by the EvDRE formula.
****************************************************************************************
A simpler solution to the zebra stripe requirement is actually offered by the BPC EvDRE function itself. There is an available parameter called ODDROWS which can be used in the APPLY TO section of the FormatRange. For example we might have defined row 56 as follows:
In the example above, cell C56 is simply formatted with a green fill color, rather than using a conditional formatting.
After refreshing the BPC report again, the resulting output is as expected.
A few conclusions can be made about these two possible solutions.
- Using the ODDROWS option, BPC will format the first row of the report and every other row, no matter what row number the cells happen to lie. So if the first row of the report happens to be in row 12, for example, it will be formatted, even though 12 is not an odd number.
- In contrast, the conditional formatting approach does use the relative position of the report and formats even or odd rows in the workbook as determined by the formula chosen above.
- Also to note is the fact the conditional formatting did not alter the calculated rows, while the ODDROWS method did. Although it would be possible to create a second conditional formatting rule which does apply against calculated members.
Either method can be used to achieve the zebra stripe requirement, but the technique shown here for conditional formatting might be valuable to BPC report writers in numerous other ways beyond color formatting.
1 Comment
