By: Scott Murray | Comments (16) | Related: > Reporting Services Development
Problem
In a previous post on SQL Server Reporting Services (SSRS), internal drill down and drill through actions were discussed. What options are available to drill through to external sites such as Bing or Google Maps or a Currency Rate lookup.
Solution
Within any text box, an action can be designated. Many times these actions focus on drilling down to another report on the Report Server or showing / hiding various details concerning a particular number. Other times, though, a report user will want to actually navigate to a completely different website. SSRS allows for this navigation by utilizing the Action window within a text box's properties. However, as with any other solution, you need to be careful about forwarding users to non-owned sites along with the possibility that changes will occur on the forwarded site which would invalidate the action.
Text Box Actions
The first step is to create a new SSRS Project in SQL Server Data Tools (aka... Visual Studio 2010 with SSRS) as noted below. In prior versions, this tool was know as Business Intelligence Development Studio or BIDS, but in true Microsoft fashion, the name keeps evolving. For this example we will use the AdventureWorks 2012 sample database; specifically we will extract the list of Zip Codes from the AdventureWorks database Address table.
Second, the data source connections must be defined as shown in the below figure.
Subsequently, we are ready to add a new report. As viewed in the next illustration, Right Click on Reports, Select Add, New Item. Following this track, select Report as object type and name your report as appropriate.
Next, we will create a basic dataset called ZipList which will bring in a group of zip codes, along with a count of addresses that use that zip code.
The last step in the basic report creation is to drag a matrix object from the toolbox to the report grid. Next add PostalCode to the row group and Address_Count to the data area.
Our basic design thus produces the following report.
With the basic outline of the report finished, we can now begin to build out our Report Actions. We will make use of the text box that contains the Postal Code values. Our first step in this process will be to add an action to the text box properties by right clicking on the postal code text box and selecting Text Box Properties. Next Navigate to the Actions pane, select Go To URL, and enter http://www.bing.com/maps/default.aspx in the Select URL box.
To make it clear to the report consumers that the field is a hyperlink, format the postal code text box with a blue font and underline the text, as illustrated below. Additionally, add a Tool Tip to the text box which notes that the report consumer can "Navigate to Bing Maps".
After deploying to the report server, the end user will see the following report, with the ability to navigate directly to the Bing Maps site.
However, we can make this process even better by having Bing Maps open up to the zip code selected. We need to adjust the Selected URL expressions to add an additional URL parameter for the postal code, as shown below.
Now when we click on the link, Bing Maps opens and zooms to the selected zip code.
One additional request that most users want with these type of drill through actions, is the ability to open up a new window when the link is clicked. Thus, the SSRS report remains open while a new window opens to the link. Unfortunately, SSRS does not support this functionality natively, but this setup can be achieved by using a bit of JavaScript and is noted in the below expression box for the Selected URL.
Now the Bing Map opens a new tab while leaving the original report also open.
Let us add one last enhancement to the Bing Map detail. Since Adventure Works sells bikes, let us add not only the zip code zoom, but also use the search nearby feature to plot bike shops. As noted in the below figure, we append the URL with "&ss=Bicycles" to add this to the zip code criteria.
The final results are displayed on the following two figures.
Conclusion
In this tip, we displayed how to add an external link action to a text box. In particular we utilized a link to Bing maps from a SSRS report in order to display first a map of the selected postal code, and then further refined this map to include nearby bike stores. We also noted using JavaScript to open the link in a new tab as opposed to within the same window.
Next Steps
- Implement smart drill through for your reports
- Bing Maps URL Parameters -- http://onlinehelp.microsoft.com/en-us/bing/ff808440.aspx
- SSRS Helper Function for Open Report in new window -- http://jochenj.wordpress.com/2011/10/06/ssrs-helper-function-for-open-report-in-new-window/
About the author
Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
