Adding an ESRI Map to an SSRS Report

  • 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

  • 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

  • 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.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply