As part of the BI project I'm currently working on, I needed to render my data on a map of several gold countries on visual studio, we found that the map of the United States, so how to do for other countries?
Here is a solution to create our own personalized map.
Get the shapefile
To start, you have to download the shapefile (world) which contains all the information related to the geometry of the countries (Points, lines, Polygons).
Shapefile conversion to SQL Spatial Data
In the next step we will use a tool called Shape2SQL (sqlspatialtools_build3759) to retrieve the spatial data from the shapefile in a SQL Server table.
Now, I have all the countries in a table called World, as shown below, with a geom column having the Geometry SQL data type, knowing that we can delete the countries we do not need this table.
Creation of the map
Now that I have my spatial data in a table, I am ready to create the map. In Business Intelligence Development Studio, I added the map to my report that launched the Map Wizard and then I used the following steps on the page:
- Choose a source for spatial data
- Select SQL Server spatial query
- Click Next.
On the page :
- Choose a dataset with SQL Server spatial data
- Select Add New Dataset with SQL Server Spatial Data.
On the page :
- Choose a connection to a SQL Server spatial data source
- Select New.
In the Data Source Properties dialog box, on the General page:
- Add a connection string like this
(by changing the name of your server and database):
Data Source = (local); Initial Catalog = SpatialData
Click OK, and then click Next.
On the query design page, add a query for the "dbo.World" table, like this:
SELECT [NAME], [geom] FROM [dbo]. [World]
Then click on next.
The map wizard reads the spatial data and renders it for you on the Choose spatial data and map view options page, as shown below.
In the next screen, remember to check the checkbox option Embed Map Data in this Report After that keep on clicking on next until you reach the Finish button, click on Finish and you should get the result as shown below:
Now save the report and copy the rdl report from its source location to C: \ Program Files (x86) Microsoft Visual Studio 11.0 \ Common7 \ IDE \ PrivateAssemblies \ MapGallery you should be able to see the map you just created in the map gallery when you create a new map in another report on the same system.
This .rdl file that was created can be shared and used on other projects.
Leave a comment