1

I have been interested in map reporting, so I wanted to create a map report with drill down functionality, so basically created two reports as follows: a) Main report based on SQL Server Spatial data for showing US States wide sales b) Drill down map report based on ESRI shapfile for showing US Counties sales for the selected state

I downloaded the US Counties ESRI shapefile.

Once I was done with the reports, I tested the map with drilldown functionality. I previewed the main report, hover the mouse over Texas and I don't see the counties for Texas state. I see only a map of the US with tiny dots.

So, I am not sure what I missed. I chose County Name column from the spatial data and also the county name from the analytical dataset. Is it the ESRI shapefile?

Please advise.

Tae-Sung Shin
  • 20,215
  • 33
  • 138
  • 240
Frida
  • 85
  • 1
  • 2
  • 4

1 Answers1

1

To my knowledge SSRS does not support 'hover over' features only an 'action' feature. The action can go to a hyperlink, go to a report, or go to a section. To do a drill down for a report, even if it is geographic you need to have a reference to something you can pass to something else. I recently did something similar to what you are asking though. You can make a 'subreport' that dynamically gets created from a geographic data type that represents states. What I did:

  1. I downloaded a program that takes Census shape files and puts them into a SQL database: http://www.sharpgis.net/page/sql-server-2008-spatial-tools.aspx

  2. I got a shape file of the counties of the US from the Census.

  3. I created a database called 'Spatial'
  4. I created a table and inserted data into it from step 1's app called 'Counties'
  5. I created a generic US map as my 'Main' report, using the geographic options for the US.

  6. I created a subreport of a map type but chose the spatial data wizard. The options are not that important to get started but HOW you make the image. This report gets it's data from the 'geographic' column of the table I made so the query would be like this(ensure to have a datasource go to the Spatial Database):

    Select StateName, Counties, geog(geographic type)
from UsCounties
where StateName = @State

  7. The report when done looks like a blob, because it is created at run time, not statically. You can 'preview' it and pass in a state like 'OR' or 'WA' from a variable that should be created called 'State'. If it was not auto created you may do so and make it a 'Text' variable. It will display the state shape if it was set up correct.

  8. You may go back to the report in step 5 and then go to the 'Polygon Properties' of the layer you created.
  9. Select the 'Action' pane on the left. Choose 'Go to report', specify the sub report you created, the report will want a variable match for 'State' the generic US state has an 'Abbreviation' that corresponds to the state two digit code.
  10. Now you should be able to run the main report and when you click anywhere in a state it will 'drillthrough' to a report that is created at runtime from the spatial database. This is vector based so it is fairly quick depending on your hardware hosting your machine and how much data you show.

Keep in mind that with SSRS it is hard to make it have some 3d features and hover over for some reason is completely ignored for objects but is relegated to only 'tooltips' which you can load up with functions to give text and numbers but not much more. There is a hack I wrote in another place of how you can use javascript to pop out a window, but under the hood you are really just doing a drill through presented differently.

djangojazz
  • 14,131
  • 10
  • 56
  • 94
  • I'm stuck on #6. – Dave Jul 03 '18 at 21:56
  • I haven't done SSRS in close to three years, good luck. You need to match a variable like '@State' from a parent report that you pass in to the child report if memory serves. Your variable could change from report to report but in essence doing sub reports you pass in none to many variables to them. – djangojazz Jul 03 '18 at 22:11
  • The groom in the query is where I run into issues. Getting the map to use the query is not working. – Dave Jul 05 '18 at 04:59
  • In coding no matter the language I try to go to the simplest thing first. See if you can create a report that can dynamically change the geographic shape from a variable of state like: 'OR' = shows Oregon dynamically, 'CA' = shows California. Before applying to a main I would be testing the subreport directly. – djangojazz Jul 05 '18 at 19:29