Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSRS 2008R2 showing counties (not just states) Expand / Collapse
Author
Message
Posted Friday, March 8, 2013 9:53 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 11:03 PM
Points: 703, Visits: 4,488
I have a hopefully simple question. SSRS 2008R2 can do maps by state just fine... is there an easy way to get City>County>State rollup data somewhere or get shapefiles for Counties in the US? I'm working on a database for someone that would be infinitely more useful if I could show like a heat map for sales by county across the country... there's a really cool map here that shows the county lines in it... and that's the part I want - the Counties. The granularity of the data I have is not too good, so county-level or so is about right.

Is there an easy way to create a map like that? (like a color-coded map from election night, but a county-by-county instead of state-by-state?) If so, how? And where would I get the shapefiles for the counties?

Thanks!

Pieter
Post #1428846
Posted Monday, March 18, 2013 9:16 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 11:03 PM
Points: 703, Visits: 4,488
FWIW, Yuri's code, ProcessAddress() that used Yahoo's geocoding services used to return the County, but that appears to not work anymore.

If I can't get that to work, is there a table somewhere of ZipCode-->City--->County available somewhere?
Post #1432456
Posted Wednesday, March 20, 2013 9:09 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 12:54 PM
Points: 270, Visits: 292
Using SSRS 2008 I was able to build a county "heat map" of sales for one of our suppliers by following the instructions on https://www.simple-talk.com/sql/reporting-services/report-building-3.0-adding-maps-to-your-reports/ In the map layers property box you can choose "States by County" for the United States, after that it is setting up your analytical dataset query elements to correspond to the fields on the map.
Post #1433293
Posted Wednesday, March 20, 2013 9:57 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 11:03 PM
Points: 703, Visits: 4,488
I had seen that article before, I'm now I'm walking through it. I noticed that the locations were already geocoded, so that's how the solution works. I did find a pretty interesting demonstration on YouTube about doing Geocoding in SSIS, but haven't implemented it yet. Took me a while to walk through it, but pretty good tutorial. (Just make sure you don't skip a step!) Super cool, because I'm trying to convince someone that Access is just not the tool for doing exactly what he does in the tutorial - mapping sales data. (Never mind the fact that Access has nothing like SSIS to do data imports and transformations).

Thanks for making me look at the tutorial more closely!

Now to figure out the Geocoding stuff a little better. Found an example on YouTube, and a website that will do small datasets, but the company I'm working with has several thousand addresses to geocode. (I used to use the YAddress geocoder, but that apparently no longer works... It had a ProcessAddress TVF that did the geocoding and returned the County each site was in.)

Pieter
Post #1433573
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse