In the BEx query designer you can create Exceptions, which will highlight specific values based on the thresholds you configured. In SAP BUSINESS OBJECTS ANALYSIS, EDITION FOR OLAP those Exceptions are now called Conditional Formatting. Using Conditional Formatting you can set up thresholds and configure the different formatting options directly in SAP BUSINESS OBJECTS Analysis, edition for OLAP, which also means that you don’t have to create Exceptions in a BEX query anymore as your business user is able to create Conditional Formatting in the BI client directly, which can also help reducing the overall number of needed BEx queries in that way.
SAP BusinessObjects Analysis, edition for OLAP is currently (SAP BusinessObjects BI4 Service Pack 05) not leveraging existing Exceptions from a BEx query, but that is planned to be fully supported with the upcoming release 4.1 (RampUp Q2 2013).
For this section, we will use the BEx query we created in section BEX QUERIES FOR ACTIVITIES and we will start with a workspace with characteristic Country and characteristic Region in the Rows and the key figure Net Value shown in the Columns (see Figure 5.52).
Figure 5.52 Analysis Workspace
1.Open the BI Launchpad via Start > All Programs > SAP BusinessObjects BI Platform 4.0 > SAP BusinessObjects BI Platform > SAP BusinessObjects BI Platform Java BI Launchpad.
2.Logon with your SAP credentials and the SAP authentication for your SAP NetWeaver BW system.
3.Select the menu Applications.
4.Select Analysis, Edition for OLAP. In the next step, you are presented with the list of available OLAP Connections.
5.Select the connection we created previously pointing to your SAP NETWEAVER BWsystem.
6.Click Next. You are now receiving the list of available BEx queries.
7.Select the BEx Query from section BEX QUERIES FOR ACTIVITIES.
8.Remove all items from the Rows.
9.Add characteristic Country to the Rows.
10.Add characteristic Region to the Rows, so that your cross tab first displays Country and then Region.
11.Use a right-click on the item Key Figures in the columns.
12.Select the menu Filter > by Member.
13.Ensure that only the key figure Net Value is selected.
15.Click on the column header for the key figure Net Value so that the complete column is selected (see Figure 5.53).
Figure 5.53 Selected Column
Conditional Formatting and Selection
When creating a new conditional formatting, you need to ensure that your selection of columns or cells includes all those that should become part of the conditional formatting.
16. Navigate to the tab Analyze.
17. Select the menu Conditional Formatting (see Figure 5.54).
Figure 5.54 Conditional Formatting Menu
18. Select New (see Figure 5.55).
Figure 5.55 Conditional Formatting
19. Enter a Name for your new Conditional Formatting definition.
20. Using the option Based on you can select the key figure that you would like to use as a base for the conditional formatting. You can use any key figure from the BEx query, not just those that are visible in the cross tab.
Figure 5.56 Conditional Formatting Details
Subscribe to our youtube channel to get new updates..!
21. The Format option (see Figure 5.56) allows you to select between the Cell Background, the Value, or Symbol as the option for the highlighting.
- Cell Background will highlight the cell background of each cell that meets the defined condition.
- Value will highlight the actual numeric value based on the defined condition.
- Symbol allows you to add a symbol to the cell that meets the defined condition.
Figure 5.57 Color and Symbol Details
22. In the Definition part (see Figure 5.57), you can select from a long list of operators and you can define multiple conditions for the key figure.
Entering Threshold Values
When entering the values for your rules as part of the Filter by Measure definition, it is generally good practice not to enter any thousand separators.
Figure 5.58 Definition Operator
23. For our example, set the following options:
|Based On||Net Value|
|Definition||Select a green color (value 6)|
|Definition Operator||Greater Than|
Click Add (see Figure 5.59).
Figure 5.59 Conditional Formatting Rule
25. Activate the option Preview.
Figure 5.60 Workspace with Preview
The Preview option (bottom left corner) allows you to see how your defined condition impacts your cross tab without having to confirm the condition already.
26. Now add a second definition
|Based On||Net Value|
|Definition||Select a red color (value 1)|
|Definition Operator||Greater Than|
27.Click Add (see Figure 5.61).
Figure 5.61 Conditional Formatting Rule
Now let’s take a look at the Preview (see Figure 5.62).
Figure 5.62 Preview
You will notice that in our example all values larger than 50.000 are shown with a red background now. SAP BusinessObjects Analysis, edition for OLAP is using the numeric values from the colors as a priority. In our given example, the rule for the green color has the value 6 and the rule for the red color has the value 1, which means that red has a higher priority compared to green, so even though we have values larger than 100.000 in the cross tab, those values will become red based on the priority value 1 of the rule for the red color.
28. Click on the color symbol in the rule definition and open the list of colors.
29. Use the menu option Invert.
Figure 5.63 Color Definitions
Now you can see that we have inverted the list of colors, but we did not invert the priority. Our rule for values larger than 50.000 still has the priority value 1 and therefore overwrites the rule for values larger than 100.000. So in case, you are defining overlapping rules, like in our given example, you need to ensure that the priorities are correct as well. By using the menu Edit, you could select a color line where the green colors have a higher priority compared to the red and in that case the rule for values larger than 100.000 becomes the highest priority.
Before we confirm our defined rules, let us take a look on how we can customize the color options.
30. Open the list of available colors.
31. Click Edit (see Figure 5.64).
Figure 5.64 Edit Color Scheme
Here you can change single colors or select one of the predefined set of colors by simply clicking on one of the arrows on top of the defined color lines.
32. Click Done.
33. Now click OK to confirm your defined rules.
Figure 5.65 Workspace with Conditional Formatting
In our given example, the numbers are highlighted in blue as the rule for values larger than 50.000 with a blue color assigned has the priority value 1 is defined.
34. Navigate to the tab Analyze.
35. Open the menu Conditional Formatting (see Figure 5.66).
Figure 5.66 Conditional Formatting
In the menu Conditional Formatting, you will now find the defined set of rules by the Name we defined in the previous steps – Net Value > 10.000. You can use the menu option Edit to open the defined rules and change them, and you can use the menu option Delete to remove your defined rules. In addition, you can also use the toggle in front of the name of your defined Conditional Formatting to disable your rules without removing them from the workspace, which is especially helpful in situations where you defined several conditional formatting rules and want to use them as part of your workspace, but decide not to activate them all at the same time.
In this section, we learned how to create conditional formatting rules in SAP BusinessObjects Analysis, edition for OLAP and how those rules can help us to quickly identify specific thresholds. In the next section, will learn more about a powerful capability, called Focused Navigation.