Manage Groups and Value Based Break how we used in webi report

This blog is about two BO features in webi (Manage Groups and Value Based Break) and how we used it to achieve one of our real time project requirement. We got a requirement in Profit and Loss (P&L) report to bring new subtotal lines A&CP and RSG&A as below in Expected Output.

In old output we have a cross table with P&L line as row and Month/Actual YTD as columns. All P&L lines shown here are direct values from database (our case from HANA view with Relational connection). New lines A&CP (sum of Advertising and CP) and RSG&A (Sum of Selling, R&E and Total G&A) has to be computed @ report level as these P&L lines were not available @ HANA level.

The easiest approach is to split the table in to 3 to 4 blocks. Block 1 with P&L lines Volume (MSU) to GP. Block 2 with A&CP (hardcoding) in header and its child (Advertising & CP) as Block 2 rows. Similarly we have to design Block 3 & 4 with required P&L lines. But this approach requires more tables and more effort for future maintenance.

Is there a way we can achieve the requirement with single cross table structure?

Yes, we were able to achieve this using combination of BO webi features Manage Groups and Value Based Break. Let’s see in detail how this is done with similar report sample.

Step-1

For explanation we created a report with required P&L Line in rows and Month Number/Actual YTD with cross table structure as below. As first step create a new column before P&L Line column and display P&L line again.

Step-2

Right click on first column (which is copy of P&L line column) and click on Group -> Manage Groups.

Manage Groups panel opens and change the group name as required and start grouping as below.

The group we created will appear under Variables list. It works similar to a variable created with IF-THEN formula grouping.

v_PL_LINE_DESC_NEW_=If [PL_LINE_DESC_NEW] InList (“Volume (MSU)”;”Gross Sales”;”DR&A”;”Trade”;”CP – RiR”;”Net Sales”;”COGS”;”Distribution”;”GP”;”OP”) Then “No Break” ElseIf [PL_LINE_DESC_NEW] InList (“Advertising”;”CP”) Then “A&CP” ElseIf [PL_LINE_DESC_NEW] InList (“Selling”;”R&E”;”Total G&A”) Then “RSG&A”

After this step report output looks as below.

Step-3 

Right click on v_PL_LINE_DESC_NEW  column and click on Break -> Add Break

Initial output appears as below. We can see table is split into multiple blocks with default setting.

Let’s adjust Break properties from Break -> Manage Breaks

Deselect default property “Break footer” and “Apply sort”. Select “Break header” as we need to display subtotal as a top row for A&CP and RSG&A. Also select “Value based break” and click “Values” button. “Select Values” window pops up. Here pass values “A&CP” and “RSG&A” which only required break and click “OK”.

Click on “A&CP” break header cell and update formula “=[v_PL_LINE_DESC_NEW]” and click on Body cell and update formula “=Sum([v_ACT_YTD])”. Finally we can hide first column to look output as below.

Step-4

As last step we have to make some formatting adjustment to make output similar to the requirement. For this we have applied 3 conditional formatting for P&L line column to make few P&L line appear with space and Italic, few bold and few bold with Top Border as below.

Final output (P&L line column) looks as below with required subtotal lines A&CP and RSG&A.

Conclusion

To conclude Value Based Break is one of the powerful feature in webi which is available from 4.2 sp5 versions and this blog will help to implement similar requirements. As mentioned before Manage Groups is one of easiest way of grouping dimension values without writing formula with IT-THEN logic.

SAP Environment Details Used

SAP BO 4.2 SP5

HANA 2.0 SP5

Reference

Value Based Break is enabled in webi from 4.2 sp5 version

https://blogs.sap.com/2017/10/23/sap-bi-4.2-sp5-whats-new-in-web-intelligence-and-semantic-layer/

Group the values of a dimension: SAP BusinessObjects Web Intelligence 4.1 SP2

https://www.youtube.com/watch?v=weF2MmJkeqw