It is common that different dimensions contain different thresholds for alert metrics. For example, sales threshold targets may be different for each region of a company, as shown in the following screenshot:
In our example, we have four regions and different thresholds for each region. So anything below the yellow threshold value will be red, anything that is equal to or greater than the yellow threshold but less than the green threshold will be yellow, and anything equal to or greater than the green threshold value will be green.
This recipe contains a column chart that contains monthly values for a selected region. As the user changes their region selection, the alert threshold will also change. The appropriate alert coloring for each bar will be displayed on the chart.
We will have one worksheet that contains the threshold values as well as a spot that will house the thresholds for the selected region. Please refer to the first screenshot from the introductory section of this recipe. This is how the threshold data layout will look. There will be another worksheet called Chart Data that will contain the chart data.
- Insert a Column Chart and a Combo Box selector into the canvas.
- Open the Combo Box selector properties and bind Title and Labels to the cells shown in the following screenshot:
- Set up the threshold value by linking to the appropriate label. In the Data Insertion section of the Combo Box properties, rename the text to
Threshold. Then, set Insertion Type as Row. Bind Source Data to cells A2:C5 in the Thresholds sheet. Bind Destination to the section highlighted in yellow (E3:G3).
- Next, press the + button in the Data Insertion section to add another row selection. Name the next insertion type as
Chart Data. Set Insertion Type to Row. Go to the Chart Data worksheet, bind Source Data to cells A2:F5, and then bind Destination to cells H3:M3.
- Bind the chart data to the Chart Data worksheet cells that we populated from step 4. Set the Subtitle of the chart to cell H3, which contains the selected region name.
- Go to the Alerts section of the chart properties and check the Enable Alerts checkbox.
- Select the radio button that says By Value.
- In the Alert Thresholds section, check the Use a Range checkbox and bind to cells F3:G3 in the Thresholds worksheet.
- In the Color Order section, select the radio button that says High values are good.
In our example, we bind the alert thresholds in steps 6 through 9 to the cells that dynamically change according to the user-selected dropdown. The first threshold, which is red, is anything that is less than the yellow threshold value in cell F3. The yellow threshold is anything greater or equal to the yellow threshold value in cell F3 but less than the green threshold value in cell G3. The green threshold is anything that is greater than or equal to the green threshold value in cell G3. Using the range bound in the Alert Thresholds section, we can dynamically change our threshold settings.
In this example, we hardcoded all the threshold and chart data values. But in reality, we can populate the values in the yellow destination cells of steps 3 and 4 using any of the available SAP BusinessObjects Dashboards data connectivity options.
To use different data connectivity options, please read the recipes in Chapter 8, Dashboard Data Connectivity.