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,
Person.StateProvince.CountryRegionCode
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!!!