Pages

Sunday, December 19, 2010

Drill down in SSRS Map Reports

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…

Monday, December 6, 2010

Subscripts and Superscripts in SSRS Reports

               Two weeks back, I was reading an insightful article by Thomas LaRock - Delivering a Great Presentation. A couple of lines kept on lingering in my mind, mainly
- "Storytelling is a very powerful way for you to communicate ideas because they are such a powerful way for people to learn"
- "There are five stories that make up effective presentations. Every great story falls into one of these five:- the Quest, the Stranger, the Love story, the Rags to Riches and the Revenge story"
The more I thought about it, the more I could relate the same to blogs. I always tended to remember more articles or facts which were woven around a story than those which were plainly narrated. And also, I found that most blogs which I liked, if not all, could be categorized into the above said sections. Well, today's blog is going to be the story of a quest, something which I had tried to achieve months before, lost hope and abandoned it, and then again renewed efforts and finally, found the solution – how to use superscripts and subscripts in SSRS reports.
 
It all started with one of my colleagues challenging me to write a chemical equation and a mathematical equation together in SSRS. What I failed to understand when I took up the bet was that it was a cleverly disguised challenge to implement superscripts and subscripts in SSRS. The challenge was to display the following in SSRS:-
 
Chemical Mathematical equation
 
Five minutes in BIDS and I understood that I was trapped. All I could remember next was me frantically pacing through all the search engines racing against time and a flurry of web pages opening. But apart from this link in the SSRS forum, I couldn’t find much help and I had to concede defeat. Laden with a bruised ego and a lighter pocket, I decided to delve more deeper into it. After quite a bit of research, I found 2 ways -
 
Solution 1

1. Note down the following codes

Superscript and subscript codes

2. Use expressions along with the chrw function in textbox.

Eg:-

="a"&chrw(8304)&chrw(185)&chrw(178)&chrw(179)&chrw(8308)&chrw(8309)&chrw(8310)&chrw(8311)&chrw(8312)&chrw(8313)

Expression builder

3. Make sure that the font is Lucida Sans Unicode.

Font

4. Now you can preview the result.

Preview report

 

Solution 2
 
1. Click on StartRun and type charmap.exe. Click OK.
Run window
 
2. Change the font to Lucida Sans Unicode and then find the character that you need. For eg, Subscript Two is selected in the image below.

Charmap

Once that is selected, click on Select and continue writing your chemical/mathematical equation in the same way.

3. Once that is done, click on copy button (and not Ctrl+C or any other shortcut button) and all you have to do is to paste it in a textbox in the report. Make sure that the font is Lucida Sans Unicode for the textbox.

Textbox with chemical equation

4. Click on preview and you will have the equation that you wanted.

Preview report with equation

Note : You can select any character in the Character Map and then use the chrw function. For that, note down the number in the character map (for eg, in the image above for Step 2, the number for Subscript 2 is 2082) and convert it from hex to binary (You can use sites like this for converting it if you don’t want to do the maths) and then use it within the chrw().

Wednesday, December 1, 2010

Conditional Navigation in SSRS

Why do people write blogs or help other people in the technical forums? This was a frequent question that I used to ask to people around me. For some reason or the other, I couldn't fathom why people would want to spend their personal times on such stuff when they could spend the evening with their family, having a drink with friends or simply take a stroll (not to speak of hard core partying at a nightclub, common, you can at least watch the girls if you don't have the guts to ask them out! Hot smile). Modern life seems to have spoiled us with options. Even though I used to ask questions in the Microsoft forums, I had always been a "take" person rather than a "give" person. Just to experience this feeling and to get my answers, I decided to take the plunge into the forums as well as the blogosphere around one year back. Now, looking back, I am happy that I did take this step and could give many reasons - making new friends, networking with peers and staying updated on the latest developments, the joy that you get in sharing,etc but if I was asked to name one most important aspect, I would say it as - the opportunity to learn new things. You get exposed to thousands of people and their project related problems, you get to think on those issues and experience it first-hand which is almost as good as getting the experience of those thousand projects. Just to give an example, Jamie Thomson (MVP) had recently asked a question in the SSRS forums on how to conditionally turn on an action in the reports. This looked like a piece of cake until the requirements became more specific (don't know why I feel like drawing an analogy to the projects during estimation and the same projects during development Smile )

To tell you more about this, Jamie wanted to use an action to launch another report conditionally when clicking on a textbox. To simulate this, I have come up with a simple report and the following steps:-
1) Design a simple report with a datetime report parameter. In the layout, have a textbox which will display only the date part of the report parameter as shown below.

1 Sample Report 


2) Right click on the textbox, select properties and go to the navigation tab. Then select the Jump to URL radio button and enter the following expression

=iif(day(Parameters!Report_Date.Value)=”19”,”http://www.google.com”,””)

2 Navigation property 

The above expression basically enables the hyperlink to the specified site if the Report_Date report parameter is 19, else it disables the hyperlink because the blank string is passed.

Note : Care should be taken that the URL is prefixed with http. The URL of Google can be replaced by any valid URL including the report URL

Eg:- http://servername/ReportServer?/Folder/Report%20Name&rs:Command=Render

3) Now, if we preview the report, we will be able to see that the hyperlink works as expected. If 19 is selected, then the hand symbol appears when we hover on the textbox indicating that it is hyperlinked, else only the arrow icon appears indicating that it is not hyperlinked.

3 Test functionality 

Now this should work well in most of the cases because usually all the reports would be viewed online, and hence they should have been deployed to the report server. So whenever we need to navigate to a particular report that is deployed online based on a condition, we can use this technique. But the requirement here was that Jamie needed to show the reports in SQL Server Management Studio (Read this link to know how to create custom reports in SSMS) and hence there was not going to be any report URLs.

4) In this case, there is only one option which is to use the expression builder for Jump to Report instead of Jump to URL. In the expression, we can specify the report name directly. So if we have to conditionally navigate to a report called MyReport, we would expect the expression to be

=iif(day(Parameters!Report_Date.Value)=”19”,”MyReport”,””)

This had two issues – first, the hand symbol would always be present even if the condition is false, and secondly the following error would throw up on clicking the textbox when the condition is false (that is, when the empty string is passed)

“ An error occurred during local report processing. Report / cannot be compiled. Verify the report name and that the report is in the current project “

5) After fiddling around with the expressions, I found out that the following piece of code would work

=iif(day(Parameters!Report_Date.Value)=”19”,”MyReport”,0)

Yes, we just have to replace the double quotes with a constant like 0 and then we can find out that the hyperlink works as expected based on the condition.

If it was not for the post in the forum, I might have never stumbled upon this learning. It is small things like this which makes me addicted to the forums and blogs. No matter how less experienced a person is, I believe there is always something to share because the individual experiences would be always unique. And this is the very reason why I always encourage my peers to write blogs and share their knowledge. And well, if you are still on two minds whether to write a blog and contribute, read BI expert Vincent Rainardi’s take on Why write a blog? I hope it will give you the final push to start your own blog.Thumbs up