Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
MSBI 49
#BI 39
BRH 39
SSRS 35
#MSBI 29
SQL Server 28
#SSRS 22
SSAS 22
#SQL Server 21
Reporting 19

Archive · View All
April 2010 8
May 2010 7
January 2013 6
November 2012 5
March 2012 5
July 2012 4
January 2012 4
October 2011 4
June 2010 4
February 2013 3

Some Random Thoughts

Drill down in SSRS Map Reports

Dec 19 2010 9:35PM by Jason Thomas   

I have been always interested in the potential of map reports. So naturally, when I found out a site which had quite a number of shapefiles for free, I decided to blog something just for the fun of working with map reports. Being around in the forums, I had seen a lot of posts asking how to drill down or zoom to the next level in map reports and I always had to explain the answer in words. I decided to take this opportunity to blog down the answer with some pictures.

To implement this, I downloaded the shapefiles of India and followed the following steps :-

1) For the demo, I have used the administrative level 1 (States) and administrative level 2 (Districts) of India. The 2 above mentioned shapefiles are imported into the database (to see how to import shapefiles to tables, refer to Import Shapefiles into SQL Server and Aggregate Spatial Data (Geometry)) as the size of the shapefiles was around 2 MB and that would slow down the report.

2) Make a basic map report for the States with the source of spatial data as a SQL Server spatial query.

State report

The above report shows colouring by sales for each state. The dataset used for this report is shown below

SELECT NAME_1 AS State, Sales, geom AS Geo
FROM     IND_adm1

Dataset

The above query returns all the states for India.

3) Make the second report which shows a similar report for the next level. The dataset used for the query is given below

SELECT NAME_2 AS District, Sales, geom AS Geo, NAME_1 AS State
FROM     IND_adm2
WHERE  (NAME_1 = @PAR_State)

Dataset Districts

The important part to note here is the WHERE condition. This query returns all the districts for the state which is equal to the report parameter. Since this query is used as the source of the map, the map report will automatically show just the selected state.

4) Make a report parameter PAR_State in the second report and give a default value of any state, for eg, Kerala. Check whether the report is working by previewing it.

Kerala

5) Go back to the first report and specify an action to the second report. For this, right click on the map and select Polygon Properties.

1 Action - Polygon Properties

Then go to the Action tab and pass the State field to the PAR_State report parameter as shown below

2 Action - Report parameter passing

6) Click on OK and deploy both the reports. Now you should be able to click on the states and drill down to the districts as shown below.

Screen Captures

Now this might not be the only way to achieve drill down in map reports but I hope this gives you an idea on how to further play around with it and get your results. Meanwhile, for those extra attentive guys who must be wondering how I managed to change the background images for each state, I have uploaded a different image for each state in the database (No, I am not getting paid by the Indian Tourism Board for the extra effort Smile) and used it based on the state (refer SSRS reports using database images). Have fun and festive greetings in advance…

Tags: SSRS, BRH, MSBI, Reporting, SQL Server, #BI, #SSRS, #SQL Server, #MSBI, #TSQL,


Jason Thomas
19 · 9% · 2997
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

1  Comments  

  • Thanks Thomas Jason for this lesson. I tested it today. It worked.

    Tiruvazhi.

    commented on Dec 30 2011 6:13PM
    Tiruvazhi
    652 · 0% · 52

Your Comment


Sign Up or Login to post a comment.

"Drill down in SSRS Map Reports" rated 5 out of 5 by 1 readers
Drill down in SSRS Map Reports , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]