In this example, we will utilize many techniques that we have learned in the previous recipes to create a Sales/Profit dashboard.
The Sales/Profit dashboard displays the sales or profit of each state on the map. From the map, a user can select a state and then view year-to-date (YTD) sales/profit information for products that are sold from the bar chart in the top right. The user can then drill down further by clicking on a product bar.
A detailed scorecard and trend chart at the bottom right will then be shown for the selected state and product.
Techniques from the following chapters and recipes were used for this example:
Chapter 1, Staying in Control
The Adding a line chart to your dashboard recipe from Chapter 2, Data Visualization
The Drilling down from a chart recipe from Chapter 3, From a Static to an Interactive Dashboard
The Using Filtered Rows recipe from Chapter 3, From a Static to an Interactive Dashboard
The Selecting your data from a list recipe from Chapter 3, From a Static to an Interactive Dashboard
The Using maps to select data of an area or country recipe of Chapter 3, From a Static to an Interactive Dashboard
The Using alerts in a Scorecard recipe from Chapter 5, Using Alerts
The Displaying alerts on a map recipe from Chapter 5, Using Alerts
It is important that you have the Sales_Profit.xlf file as a reference. Please open it before proceeding to the next section as the spreadsheet layout has already been completed for your convenience.
Drag the Map – USA component onto the canvas.
Bind the Region Keys to the State Keys on the Control Sheet tab.
In the Data Insertion section, select Row as the Insertion Type. The Source Data will be the keys that we selected in step 2. The Destination will be cell E1.
Bind Display Data to the key-value pair items in the Map Display worksheet.
Go to the Alerts properties, check the Enable Alerts checkbox, select By Value, check the Use a Range checkbox, and bind to the range section in the Map Display worksheet. It is important that you bind starting at 2500, otherwise it will add another range starting from minimum to 0.
The next step is to complete the YTD chart in the top right-hand corner. Drag a Bar Chart component and place it in the top right-hand corner of the canvas.
Bind the Titles to the appropriate cells in column T of the State and Drilldown Display worksheet. Then bind the Data to the cells V3:W7. The data in V4:W7 is populated depending on whether a user selects Sales or Profit. Note that the cells are pre-populated with test data.
Go to the Insertion properties of the YTD Chart. Check the Enable Data Insertion checkbox and select Row as the Insertion Type. Bind the Source Data to cells A4:Q7 of the State and Drilldown Display worksheet. Bind the Destination to cells A14:Q14. Note that the cells are pre-populated with test data.
The next step is to complete the scorecard in the middle right-hand side of the dashboard. Drag a Scorecard component onto the canvas.
Bind Display Data to cells M13:Q14 of the Drilldown Scorecard section in the State and Drilldown Display worksheet. These cells are the drilldown values populated from the YTD Chart in step 8.
Go to the Appearance properties of the Scorecard and click on the Text tab. Unselect the Target Profit and YTD vs Last YTD checkboxes. The reason is that we only want to see alert shapes on these cells and not the text value.
Go to the Alerts properties of the Scorecard. Check the Target Profit and YTD vs Last YTD checkboxes. In both cases, the Alert Values will be bound to cell O14 of the State and Drilldown Display worksheet. In both cases, make sure to have As Percent of Target selected. Bind Target Profit to cell P14 and YTD vs Last YTD to cell Q14. In the Alert Thresholds section, we want
Min/70%/85%/Maxfor Target Profit. Set the alert threshold for the YTD vs Last YTD toMin/99.999%/100%/Max.Note
The reason why we have 99.999% is so that the yellow arrow symbolizes anything that has YTD equal to Last YTD.
Next, we will complete the trend line chart in the bottom right-hand corner of the dashboard. Drag a Line Chart component and place it in the bottom right-hand corner of the canvas.
Bind Titles and Data Range to the appropriate section in the State and Drilldown Display worksheet.
Now that the display elements are in place, we'll move on to the Sales/Profit Radio selector. Drag a Radio Button selector and place it in the top left section of the canvas.
There are two sets of data bindings here. First, we will select Label as Insertion Type and then bind the data to the selected label (Sales or Profit) cell B1. The data is found in columns A and B of the Control Sheet worksheet.
For the next data binding, Map data column selection, select Column as the Insertion Type. Bind Source Data to columns B and C.
The final interactive component is the hidden State filter, which will select the appropriate data for the State and Product details. Insert a Combo Box selector into the canvas and make sure it is underneath all of the backgrounds. To make sure it is underneath, right-click on the Combo Box selector on the Object Browser and select Send To Back.
Now we will bind the data from the Product Data worksheet. The labels will be bound to column B, since we are collecting all rows that belong to a state when clicking on a state from the map. Select Filtered Rows as Insertion Type and bind the Source Data to cells B2:R205. The Destination cells will be the peach area in the State and Drilldown Display worksheet.
Go to the Behavior section of the hidden filter and bind Selected Item to the selected Map item on cell E1 from the Control Sheet worksheet.
Now that the dashboard is complete, we want to improve the look a bit. As you can see, there are several layers of background objects that add depth to the dashboard components. Drag a variety of Background and Rectangle components onto the canvas and play around with the look until it becomes something that you desire.
In steps 1–3, you utilize what you learned in the Using maps to select data of an area or country recipe of Chapter 3, From a Static to an Interactive Dashboard, to set up your map display and data on the left-hand side of the dashboard. From your map selection, you then drive the right-hand side of the dashboard.
Steps 4–5 used the Displaying alerts on a map recipe from Chapter 5, Using Alerts, to display the different colored states on the map representing the amount of sales/profit each state produced.
In steps 6-8, you use what you learned in the recipes Adding a line chart to your dashboard from Chapter 2, Data Visualization, and Drilling down from a chart from Chapter 3, From a Static to an Interactive Dashboard, to create a YTD Sales/Profit chart that allows a user to drill down from the data values to a particular product.
In steps 9-12, you will use the Using alerts in a Scorecard recipe from Chapter 5, Using Alerts, to show a product's details and threshold for a selected state.
In steps 13-14, we simply built a Line Chart that takes the trend data from a selected product and state.
In steps 15-17, we used a Combo Selector component to select from two sets of data. The first set of data consists of the label Sales/Profit, which is important because other components in the dashboard drive off the destination of the Sales/Profit label. The second set of data contains the sales/profit data for the map object.
In steps 18-20, you utilize what you learned in the Using Filtered Rows recipe from Chapter 3, From a Static to an Interactive Dashboard, to select the appropriate data from the Product Data worksheet. As you can see in the Product Data worksheet, you need to somehow group the states together into a selection. To accomplish this, a Filtered Rows selection is necessary.
The final steps consisted of adding backgrounds and providing a uniform aligned look and feel, recipes of which can be found in Chapter 1, Staying in Control.