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 3 of 3

  • 27 March 2011
  • Author: SMcDonald
  • Number of views: 11257

This is the final article of my Rock Your Reports series that I have been doing.  In the first article, I showed you how to create a column chart with a multi series axis.  In the second Article, we created a report using the map which is an R2 feature of Reporting Services as well as a data bar.  What I want to do is show you how to create a drill through action from the Map of the country into a detailed report about the state and also include a map of the state.


Create a new report called State Details

Create another shared data sources: AW2008R2

Under Report Data Tab:  Create a data source pointing to the shared data source AW2008R2

Create an embedded data set called Called SpatialData, use the below query:

SELECT        TOP (500) Address_1.AddressLine1, 1 AS OrderQty, Address_1.City, Address_1.PostalCode, Address_1.SpatialLocation, Person.StateProvince.Name,


FROM            Person.Address AS Address_1 INNER JOIN

                         Person.StateProvince ON Address_1.StateProvinceID = Person.StateProvince.StateProvinceID

WHERE        (Person.StateProvince.CountryRegionCode = 'US') AND (Person.StateProvince.Name = @State)

ORDER BY Address_1.AddressLine1


5.  We now need to create another datasource called AWDW2008R2

6.  Create dataset called CustomersByState using the below query


SELECT        FactResellerSales.OrderQuantity, DimReseller.ResellerName, DimGeography.City, DimGeography.StateProvinceName

FROM            FactResellerSales INNER JOIN

                         DimReseller ON FactResellerSales.ResellerKey = DimReseller.ResellerKey INNER JOIN

                         DimGeography ON DimReseller.GeographyKey = DimGeography.GeographyKey

Where DimGeography.StateProvinceName = @State


 Now that we have our data that we want to use let's start developing a nice report:

 7.  Go to toolbox items and drag out a map, select SQL Server Spatial Query / Next


8.  SpatialData dataset / Next (you will get an error:  Unable to determine the spatial data type due to connection error, it's ok to click ok)

9.  Layer Type should be set to Point

10.  Check the box to add a Bing Map, for our example leave to on Road / next


11. Choose Bubble Map / Next


12.  Select Spatial Data / Next


13.  Check the box for Use bubble sizes to visualize data / data field needs to be Sum(OrderQty) / finish



Let's go back to our Map Report and create an action to go to our StateDetails Report.


14.  Double Click on the map while in Design, Right Click on PolygonLayer1 in the Map Layers Box

15.  Select Polygon Properties


16.  Go to Action and Select Go to Report, Specify the Report  of StateDetails

17.  Add a parameter name is State, Value is StateProvinceName / click ok




Now we need to go back to our StateDetails report and define it more.


18. Click on the bubbles of the Map and then select the drop down option for PointLayer1, under map layers

19.  Select Point Properties, select fill and change the color to the color of your choice, I chose Aqua / OK



20.  Go back into our PointLayer drop down and select Point Size Rule, for this example Start Size is 11 and End Size is 36.



21.  Right Click on the Legend and set the visibility to Hide

22.  Drag out a table below the map and use the fields from your data set called Customers By State

23.  Drag City and Reseller Name down to Row Groups (city being the first grouping) delete details (group only)


24.  Drag over OrderQuantity & Sales Amt to the details of the table & delete extra columns, Format the Quantity & Sales Amt.

25.  You could do some additional formatting like alternating row colors.  I used the below expression:

=IIF(RunningValue(Fields!City.Value,COUNTDISTINCT,NOTHING) MOD 2 = 0,IIF(ROWNUMBER(NOTHING) MOD 2=0,"LightGrey","White"),IIF(ROWNUMBER(NOTHING) MOD 2=1,"White","LightGrey"))





I hope that everybody has enjoyed this series.  It was a lot of fun putting it together.  Be prepared for my next blog / article series (I haven’t decided what type it would be yet J ) over the Report Builder.  Have a great week!!!




Categories: Reporting Services
Rate this article:


Other posts by SMcDonald

Please login or register to post comments.