Drill down in SSRS Map Reports

I have been always interested in the potential of map reports. So naturally, when I found out asitewhich 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 toImport 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.

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

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)

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.

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.

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

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.

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) and used it based on the state (referSSRS reports using database images). Have fun and festive greetings in advance…

How to add a Document Map to your reports

One of the most common things I do during taking interviews is to give real life scenarios and assess the person on how he tackles the situation. Most of the times, what I really look for is how he goes forward in solving the problem – the approach and the way of thinking rather than the answer itself. Another thing that I look for is whether he tries to put some extra effort to implement the solution (especially the reporting part) in an aesthetic and user-friendly way. Most of the candidates would know the answer to some simple question like how to add data labels to their charts but they stutter when I ask what are smart labels or how can we prevent the cluttering of data labels. I would easily give brownie points to someone who details his solution with some extra attention to details. It must be my frequent rendezvous with creating big reports that make me extra fond of document maps in SSRS and an essential part of my SSRS interview questions.

It is hard to imagine going through a big book that has no table of contents. But many users and developers have no problem at all in designing big reports without any navigational aid (although at some point of their wretched life, the users are going to curse the person who developed those reports), even though Microsoft has provided this feature. Document Map is a navigational feature in SSRS that, when implemented, allows the user to navigate through the document and its hierarchies. With this feature, you can add a panel to the left of the reports where you can have the list of “contents” of the report. What makes it more special is that on clicking the “content” in the list, you will be directly taken to the page where the content is present. Cool feature, huh?

Requirement

Suppose you already have a matrix or a table with some groupings as shown below

Suppose the number of subcategories are very large and spans into multiple pages and the requirement is such that each user has to specifically search for a set of subcategories at a particular time of the day. It would make his life a lot easier if there was a document map listing the subcategories on clicking which he would be directly taken to the clicked subcategory

Solution

1) Go to the design mode of the report and click on the group properties of subcategory.

2) Go to the advanced tab of the properties and set the Document map as the field Subcategory (you can select the appropriate field name from the drop-down).

3) Save the report, deploy it and then preview it .

Already you can see the panel at the left hand side which contains the list of subcategories. To hide/unhide this panel, you can click on the icon that has been highlighted in red in the above image. Notice that the “Document Map” text highlighted in blue is the report name.

4) Now you can click on any of the subcategories, say Handlebars and you would be taken to the page where the clicked subcategory is.

5) (a) You can also make document map labels for each of your report item. For eg, if you have 2 tables, you can click on the table and press F4 to see the properties.

5) (b) You can give the required name in the DocumentMapLabel property as shown above and then see the required result when you preview the report.

6) Now that you have seen the toggle symbol, you would have already guessed that it is possible to create hierarchies also in the document map label, for e.g., Subcategory—>Product. For that, all you need to do is to enable Document Map property for the Product group also.

Note

Quoting a section fromPacktub Learning SQL Server 2008 Reporting Services-

“ Document Map is mostly forHTMLrendering.Otherrenderers render differently.

·  PDF: Uses Bookmarks.

·  Excel:Uses named worksheet with hierarchical links. Reportsectionsappear in other sheets.

·  Word document also has a document map like the table ofcontents.

·  Tiff, CSV, and XML ignore this setting.

·  Recursive data is related to the idea of self joins where therelationship between parent and child is represented by fieldsin the dataset. “

So there ends the part 3 of my interview series. In my next blog, I would be taking a momentary break from my interview question series and presenting a very interesting application of Document Maps, wait for it :)