Conditional Navigation in SSRS

December 1, 2010

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”,””,””) 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

Posted by SQLJason

1 comment

Suresh Kumar has rightly pointed out that NOTHING can be used instead of 0 like shown below

Leave a Reply