Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSRS 2008R2 showing counties (not just states)


SSRS 2008R2 showing counties (not just states)

Author
Message
pietlinden
pietlinden
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2195 Visits: 12563
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
pietlinden
pietlinden
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2195 Visits: 12563
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?
pgt892
pgt892
SSC-Addicted
SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)SSC-Addicted (405 reputation)

Group: General Forum Members
Points: 405 Visits: 303
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.
pietlinden
pietlinden
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2195 Visits: 12563
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search