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:
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.
Min/70%/85%/Max
for Target Profit. Set the alert threshold for the YTD vs Last YTD to Min/99.999%/100%/Max
.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.