In our last report, we had to provide some information about what our gross margin was for investors that we are trying to solicit for extra money to keep the business running. In that report we did a comparison of our Total Product Sales and Sales Amount as well as figure out what our gross margin was.
For this report, the investors wanted to also know about our resellers and what state they're in. Though after the last report we put together for them, they might not be that interested in it anymore.
Below are the step by step Map Report instructions ( the map is an R2 feature only)
1. Under Solution: Create a new shared data source of AWDW2008R2
2. Under Report Data Tab: Create a data source pointing to the shared data source
3. Create an embedded data set called ResellerGeography using the below query:
SELECT DISTINCT DimGeography.StateProvinceName, SUM(FactResellerSales.OrderQuantity) AS OrderQuantity, DimGeography.City, DimReseller.ResellerName
FROM DimReseller INNER JOIN
FactResellerSales ON DimReseller.ResellerKey = FactResellerSales.ResellerKey INNER JOIN
DimGeography ON DimReseller.GeographyKey = DimGeography.GeographyKey
WHERE (DimGeography.EnglishCountryRegionName = 'United States')
GROUP BY DimGeography.StateProvinceName, DimGeography.City, DimReseller.ResellerName
4. Go to tool box items and drag out a Map
5. Select Map Gallery - under map gallery choose USA by state exploded / Next
6. For this example we are not adding a bing layer or cropping (you can choose to do so if you'd like)the map so click Next
7. Choose Color Analytical Map / Next
8. Use the ResellerGeography data set / Next
9. Under Match Fields column, check the box STATENAME (then to the right)-Under the Analytical Dataset Field = StateProvinceName (we are linking the map state name to our Dataset StateProvinceName) / Next
10. Field to Visualize is Sum(OrderQuantity) / Check display labels = #StateFP / finish - just a side note, if you wanted to change the color schema of the maps you could it here.
11. Drag map to Far Left Top Corner
Now what we want to do is bring in a little more detail for the map.
12. Drag a table from the toolbox over to the Right of the Map
13. Drag State Province Name to the Row Groups / Delete the details (group only)
14. The second column will be Order Quantity (should sum automatically since we deleted the details from row groups). Don't forget to Format the Number
15. Drag another Order Quantity (make sure it's summed) to the right of the first Order Quantity. Then go to the toolbox and drag over a data bar on the details of the 2nd Order Quantity (select 1st one called Bar).
16. You also have the option of not bringing in two Order Quantity columns and simply place the data bar in first Order Quantity that you brought out. If you did, you could also Right Click on the bar and select the option to Show Data Label.
Now, I am sure you know the proper formatting goes without saying. You could alternate row colors if you want or add a company logo to your report too. Spice it up a bit!
In my final article series for Rock Your Reports that I will write this week, I will show you how to create and action on this Map Report and go to a detailed report that uses the spatial data within AdventureWorks2008R2 (only, not in AdventureWorksDW2008R2).
I hope you are enjoying this series as much I have been. Have a great week!