Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Rock Your Reports Series 2 of 3

  • 13 March 2011
  • Author: SMcDonald
  • Number of views: 10964

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!

Categories: Reporting Services
Rate this article:


Other posts by SMcDonald

Please login or register to post comments.