With the Scorecard component, we can easily create scorecard KPIs with the ability to insert a user-desired alert / trending icon in any column of the scorecard.
In this recipe, we will go through an example of using the Scorecard component to show a table of values, a threshold indicator that will determine whether the current month's sales meet the threshold or not, and a trend indicator that shows whether the current month's sales have risen/fallen compared to the previous month's sales.
Set up your data as follows. We have a list of regions, current month sales, previous month sales, and sales threshold. Note that the Month to Month Change column is highlighted because it can either come from the external data source or it could be calculated in the Excel spreadsheet.
- Select the Scorecard selector from the Selectors category of the Components window and drag it onto the canvas.
- Bind the Scorecard component to the spreadsheet data prepared in the Getting ready section.
- Now set up the alert components. First, set up a calculation in column F of the spreadsheet, which will determine whether the threshold indicator for the current month will be green or red. Use the incremental formula,
=IF(B2 >= E2, 1, -1), in each row in column F.
- Go to the Alerts section of the scorecard properties and check the Current Month Sales checkbox.
- Bind Alert Values to cells F2:F5.
- Select By Value, right below the Alert Values section.
- In the Alert Thresholds section, delete the yellow color by clicking on the X button in the second last column.
- In the Alert Thresholds section, go to the To value of the first row and change it to
0. - In the Color Order section, select the High values are good radio button.
- In the Alerts section of the scorecard properties, check the Month to Month Change checkbox.
- Bind Alert Values to cells D2:D5.
- Select By Value, right below the Alert Values section.
- Set Alert Threshold values to what is shown in the following screenshot. The numbers will be explained in the How it works... section.
- Click on the highlighted icon in the Enable Auto Colors section and select the arrow icons circled in red.
- In the Color Order section, select the High values are good radio button.
- Go to the Appearance section of the Scorecard component. Select the Text tab. Uncheck the Month to Month Change checkbox because we only want to see the trending icon here and not the text.
In the Scorecard component, we can combine different types of alerts together with our table data, as shown in the following screenshot. Now, let's discuss some important points from the steps in the How to do it… section.
In step 2, you will notice that we do not bind to the Sales Threshold column because that column is used to calculate whether the Current Month Sales are above or below the Sales Threshold. As you can see in step 3, we have column F that houses the calculation. The calculated values in column F are then bound as the alert values in step 5. From there, we are able to determine whether the threshold indicator on the Current Month Sales column should be green or red.
The trend icons shown in the Month to Month Change column are determined from the values in the Month to Month Change column. In step 13, you will notice the funny 0.00001 values. This is to take into account Month to Month Change values that are 0. Unfortunately, we cannot set (if value = 0, then show no change arrow), so 0.00001 is the next closest alternative. In the Month to Month Change column, we want to show the arrows, so in step 16, we hide the text values.
You can customize the scorecard look by modifying the appearance settings. Read the recipes in Chapter 7, Dashboard Look and Feel, to learn how to change the appearance settings of a component.