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

Adding an ESRI Map to an SSRS Report Expand / Collapse
Author
Message
Posted Monday, September 24, 2012 2:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 4:15 AM
Points: 18, Visits: 65
Hi

I'm using Windows XP Pro with Office 2007 & have installed SQL Server Mgmt Studio 2008 R2 with Business Intelligence Development Studio.

Using BIDS I have created a report & used the toolbox to add a map.

I browse to an ESRI map to use a Map from Australias 'Australian Bureau of Statistics' website & downloaded this to my PC.

The trouble I'm having is that I will browse to the ESRI file, & it works OK.

If I try to create a new map using the same ESRI map, I get an error "Unable to open this file".

This pretty weird considering it worked OK, minutes before.

Has anyone experienced this or know why this happens?

I've tried closing & reopening the App, Re downloadingthe ESRI maps & restarting the PC. I can't find any pattern as to why this happens.

Any suggestions??

Thanks
Post #1363359
Posted Tuesday, September 25, 2012 1:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 4:15 AM
Points: 18, Visits: 65
I've found the reason why I haven't been able to use these .shp files.

Their size was the problem, they were too big to use in the SSRS (tool of BIDS
Post #1363827
Posted Tuesday, September 25, 2012 2:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 4:15 AM
Points: 18, Visits: 65
Reason for the error continued...

I found that ESRI .shp (Shapefile) maps for Australia that held very granular data such as Postcodes were naturally bigger in Filesize than maps that had just Australian States info.

Therefore I could browse to the Region maps & they would work OK, as soon as I wanted something with Postcodes, it was too big & prompted the "Unable to open this C://....... .shp file" error.

So there's nothing wrong with the ESRI files themselves – by the way, ensure you download all the other file types besides the .shp file such as .dbf, .shx.

Next I downloaded a tool to upload the .shp (Shapefile) in a normal table format (Rows & Columns).

The tool was shp2sql;

http://www.dl4all.com/ree/tag/free+shp2sql.html

Then when you make the selection in SSRS to add a map using a Spatial Query, the query you write will be something like the below - note I'm using the Postcode type data but you would match on any suitable common field;

select *

from Your_Ref_Table_with_Postcode A -- your file including the Postcode field

JOIN Spatial_Table_Data B -- Uploaded .shp file

ON A.POSTCODE = B.SPATIAL_POSTCODE

The Spatial or geometric data aligns the map with the Postcode info common to both tables & you can continue to create your map in SSRS.

Hope this helps anyone with the same problem.
Post #1363846
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse