Tutorial 14-2. Mapping with Pie Charts

For the second tutorial, let’s get a little fancy and create a map type that plots multiple values: the pie chart. We learned earlier that the pie chart and bar chart types are only available when the Data tab has different fields for Geographic Area and On Change Of. By using a third data field, we can plot values against a category. Let’s see how this works by mapping the total sales by city against multiple product types. We’ll use a pie chart to see what percentage of sales each product has within a state. Since it is a pie chart, the percentages must add up to 100% for each state. In the second part of this tutorial, we will change the map type to a bar chart to see how it maps total sales dollars for each product rather than percent of total sales.

1. Create a blank report using the Xtreme.mdb database.
2. We want to show sales based on product type and city. This information is spread out across many tables and requires them all to be linked together. Add the following tables to the report: Customer, Orders, Orders Detail, Product, and Product Type.
3. Check the Links to make sure all tables have been linked together by their primary IDs (if you selected all the correct tables in Step 2, then this should be done automatically). Click the OK button to save the table info.
4. Before creating the map, we first need to create a formula that tracks sales by product. The table Orders Detail tracks how many products were ordered and what each one cost, but not the total amount. Let’s create the formula to do that now.
4. Create a new formula and call it Product Sales.
5. In the Formula Workshop dialog box, use Crystal Syntax to enter the following formula:

6. Save and close the Formula Workshop.
7. Now let’s create the map and we’ll use the Product Sales formula in it. Select the menu items Insert > Map. This opens the Map Expert dialog box.
8. On the Data tab, we want to map product sales by state. We also want to show how the sales compare to each other based on product type.
5. To map by state, select the field Customer.Region and assign it to the Geographic Area section.
6. To compare sales by product type, select the field Product_Type.Product Type Name and assign it to the On Change Of section.
7. To map the data on total sales, select the formula Product Sales that you created in step 4 and add it to the Map Values list. At this point, the Data tab of the Map Expert dialog box should look like the following:

8. Click on the Type tab to choose what type of map to use.
9. Notice that the only map types available are the Pie Chart and Bar Chart. This is because we chose different fields for the Geographic Field and On Change Of fields.
9. The default type is Pie Chart and we’ll leave that as it is. Click the Ok button to save your changes and preview the map.
10. 10. The default view is of the whole world and we only want to view the United States. Right-click on the report and select the menu option Resolve Mismatch. Choose the map USA.
11. Click the Ok button to save the current map and preview the report. It should look similar to the following figure.

A couple of options you could play with are making the pie charts larger or turning off proportional sizing so that they are all the same size.

12. After you had a chance to review the pie chart map, and possibly modify it to see what happens, let’s change the map type to bar chart. This lets you see how it differs from the pie chart. Right-click on the map and choose Map Expert.
13. 13. Click on the type tab and choose Bar Chart. Preview the report and the pie charts have been replaced with bar charts. The length of each bar is indicative of the total sales for each product type.