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.
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.
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.
You can use Google but it's limited in the amount of times per day. If you go over the limit you need to apply for and use their API.
Here are a few ways in which you can use Google Maps.
You can also put in multiple points but I believe Google limits the amount of characters you can supply and will reject the call if the limit is exceeded.
I already use this technique to drill down to MS Dynamics Nav (open screen with the right project selected).An addition tip would be to manage the URL’s is a (parameter)table in you DWH. In this table you archive all your URL-definition with ‘parameter-handles’, that can be replaced at runtime.
Next: in for instance SSAS you can create is a calculated measure which replaces #par1# and #par2# with the actual data your analyzing. Het measure returns a complete URL.
I created the measures because they are easy to use and because I found it difficult to write, test and maintain the URLs is de separate reports.