Category Archives: SSRS

Using Google static API in SSRS reports

In this post we will see how we can use Google API in SSRS reports, this will help us to facilitate Google Maps in our report. You can get all the information about Google API here.  In this post we will see:
1. How to use API as default.
2. How to pass parameters in API.
First lets create a simple SSRS report. For this go to Start->All Programs->Microsoft SQL Server 2012->SQL Server Data Tools and create a new Report Server Project.

SSDT Step-1

 

 

 

 

 

 

 

 

 

 

 

Now, add one report and name it- MapAPI as per below image:

Step-2

Now drag an Image from toolbox to report surface and select external from Image source and click on expression button and paste the below expression in it and click OK
=”http://maps.googleapis.com/maps/api/staticmap?center=India&zoom=4&size=500×500&maptype=hybrid&sensor=false” (Copy this expression from here)

Step-3 Step-4
Now go to size properties of Image and select Original Size as display and click OK.

Step-5

 

 

 

 

 

 

 

 

 

 

 

We are done, check the preview of your report which looks like below image:
Step-6

 

 

 

 

 

 

 

 

 

 

 


Now the next step, to pass a parameter in this image. We can pass different type of parameters in Google API (list is
here). We will see Map Type parameter in this post. As per Google documentation:

maptype (optional) defines the type of map to construct. There are several possible maptype values, including roadmap, satellite, hybrid, and terrain.

For this go to report design and do right click on Parameters in Report Data and click on Add Parameter, enter MapType in name as well as in Prompt and click on Available Values and select specify values and add roadmap, satellite, hybrid, and terrain in value and click on OK.
Step-7

 

 

 

 

 

 

 

 

 

Again to go report design and do right click on Image properties and change the existing expression to below expression:
=”http://maps.googleapis.com/maps/api/staticmap?center=India&zoom=4&size=500×500&maptype=”&Parameters!MapType.Value & “&sensor=false” (Copy this expression from here)
Click on OK twice to complete the report.

Step-8

Now check the report preview. Your report will display the map type as per parameter value. Below is the example of Satellite view of report.

Step-9

 

 

 

 

 

 

 

 

 

 

 

 

In next post we will see how we can do the same by using Map control in SSRS report.

MAP Reports in SSRS 2012- Part-1

This post is in continuation of our last post where we learned to import shape files in SQL server. In this post we will learn how to use this shape file in SSRS reports.
From SSRS 2008 R2 onward Microsoft Introduced Map reports in Data Visualization category. Map reports allow us to create maps or maps layers to let us visualize data against a geographic background.
The idea in this post to demonstrate the map reports by using the SQL Server special query and Map Gallery. For this demonstration we will connect AdventureWorks2012 data source and use below query in Dataset:

SELECT StateProvinceCode,SUM(SOH.SubTotal) as Amount
FROM Sales.SalesOrderHeader SOH
INNER JOIN Person.Address A ON SOH.BillToAddressID = A.AddressID
INNER JOIN Person.StateProvince SP ON A.StateProvinceID = SP.StateProvinceID
INNER JOIN Sales.SalesTerritory ST ON SP.CountryRegionCode = ST.CountryRegionCode
WHERE ST.CountryRegionCode = ‘US’
GROUP BY SP.StateProvinceCode
DataSource DataSet

The very first thing you will see when you drag the Map on report surface as:

Map Layer

You can select the data from below three sources:

  1. Map Gallery: The map gallery contains maps from reports that are located in the map gallery folder for the report authoring environment. Maps from the gallery provide a quick start to add a map to your report.
  2. ERSI Shapefile: Used to take the source of special data as ERSI Shapefile (.shp).
  3. And SQL Server Spatial Query: Take special data as a source from SQL Server database.

First we will choose Map Gallery as source of special data. From the map gallery choose “USA by state Inset” and click on Next. 
Map Layer_1

The next screen is important because you can fit and zoom your map according to you by using Arrows and zoom buttons. When are done this screen click on Next. This screen will open Map Visualization screen where you can choose one of the visualization among three default options. In our case we will choose the Color Analytical Map and click on Next button.

Map Layer_3

After this screen you will see another screen which will give you the freedom to choose dataset which will be used to show the data in Map. Choose MapDataSet which we have already created at the beginning of this post and click on next
Map Layer_4

On next window you will create the relationship between special and analytical data. The best way to relate them is to check the data which is shown in spatial data and Analytical data sub windows. In this example you will notice that we can relate STUSPS and StateProvienceCode from special and analytical data respectively. To make the relationship click on STUSPS check box and select StateProvienceCode from the dropdown with all available options of analytical data. When you have done this click on Next.
Map Layer_5

On this window you will choose the theme and data shown on Map. For this example let the theme and Color Rule as default and choose SUM(Amount) from Field to Visualize and #STUSPS from display labels and click on Next.
Map Layer_6

From the next window we have to set one more property. For this click on report and then Polygon and click on properties as per below screen shot and put below expression as per screen shot and click OK twice and finish.
=TRIM(Fields!StateProvinceCode.Value)
Map Layer_7 Map Layer_8

Now you have done with this report and check the preview of the report:
Final_Report

How to import Shape files in SQL Server.

 

 

In this post we will discuss how to use import Map (Shape file) in SQL Server. For this we need to download some data and executables. The following two points will give you the complete information about this.

  1. Download the world and India map files from here
  2. Then download the software which will use to import the shape file into SQL Server database from this link.
  3. Now unzipped both of the folders which you have downloaded in above two points.Now follow the instructions as per below image:
  4. Click on Shape2Sql.exe
    Folder_Structure

5. Browse the india_state.shp file from the folder from step 1.Configure the database server and database where you want to import the data of shape file.
6. Enter the table name where you want to import the data, in our case this name is India_State.
7. Finally click on Upload Database to create the table.
8. When you run select statement on India_State table it will show you result as per two images.

UploadShapeFile

 

 

 

 

 

 

 

 

9. When you run select statement on India_State table it will show you result as per two images.
SQLData SQLShape

 

 

 

 

 

 

 

 

 

 

 

 

10. In our next post we will create Map report in SSRS using this data.

 

 

Matrix Report in SSRS 2012

 

In our first post we will discuss on Matrix report in SQL Server Reporting Services 2012. Open SQL Server Data Tools and create a Report with name “MatrixDemo” and then use your local system for creating Data Source and connect it with AdventureWorks database.

DataSource
For Data set use below query:
USE AdventureWorks
GO
SELECT Sales.SalesTerritory.Name as Region, Sales.SalesTerritory.CountryRegionCode, Sales.SalesTerritory.[Group] as Territory, Year(Sales.SalesOrderHeader.DueDate) as Year, Month(Sales.SalesOrderHeader.DueDate) as Month, Sales.SalesOrderHeader.TotalDue
FROM Sales.SalesTerritory INNER
JOIN Sales.SalesOrderHeader ON
Sales.SalesTerritory.TerritoryID =Sales.SalesOrderHeader.TerritoryID

 DataSet
Now, drag and drop and Matrix on the report surface, which will look like:
Report
Now, do the below to fill the Matrix:
FillMatrix
Now run this report and it will looks like:

Preview1
Now at this point of time our report do not have any child group, so in our next step lets create child group on our report, For creating Group go to Row Groups and do right click and click on Child Group:

RowGroup
This will open another window and choose Region from Group By and click on OK.
RegionGroup
Now below is the preview of Report:

Preview2

Now play some more with this newly created Group. For this go to Region group and right click and properties and Visibility. Select Hide when report is initially run and select toggling as Territory and click on OK button

. ToggleRowGroup
Again, check the preview of report, which looks like:

Preview3
Now we will create Column Group. For this we need to do the similar work but we have to create a Child Column Group. Right Click on Column Group and Add Group and Then Child Group and select Month and OK. Again do right click on group Month and properties and then Visibility and select initially hide and toggle it with Year. Click on OK and go to Preview of the report.

ColumnGroup

ToggleColumnGroup

Preview4

Now, you are done with Matrix report. If you have any query related to this topic please comment on this post.