solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips














































SSRS data specific report drill through to Bing Maps

MSSQLTips author Scott Murray By:   |   Read Comments (10)   |   Related Tips: > 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.

SSDT

Report Server Project

Second, the data source connections must be defined as shown in the below figure.

new data source

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.

add new report

new report name

  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. 

create dataset

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. 

ReportBasic

Our basic design thus produces the following report. 

Basic Report Preview
  

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. 

Text Box Action step 1

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".

Font Underline and Tooltip

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.

Report 1

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.

Textbox with Postal Code Parameter

Now when we click on the link, Bing Maps opens and zooms to the selected zip code.

Bing Map with Postal 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. 

Open New Tab

Now the Bing Map opens a new tab while leaving the original report also open. 

Report New Window

Bing Map New Window

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.

Search Nearby

The final results are displayed on the following two figures.

Report with Bikes

Bing with bikes

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


Last Update: 4/10/2013


About the author
MSSQLTips author Scott Murray
Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips
Related Resources


print tip Print  
Become a paid author




Recommended For You








Comments and Feedback:
Wednesday, April 10, 2013 - 2:49:22 AM - Satish Read The Tip

Thank you for the nice post :)


Wednesday, April 10, 2013 - 5:24:45 AM - Dennis van Geffen Read The Tip

Thank you for this tip. I will put is to use…

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.

For instance:

…default.aspx&where1=#par1#&ss=#par2#’,’_blanc’))

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.


Wednesday, April 10, 2013 - 11:29:18 AM - Dave Elliott Read The Tip

What a great but simple tip.  I have a full house address field and a full CityStateZip field so adding these two fields to the expression pinpoints the address:

="javascript:void(window.open('http://bing.com/maps/default.aspx?where1=" & Fields!FullAddress.Value & ", " & Fields!CityStateZip.Value & "','_blank'))"

So easy.

Thanks


Wednesday, April 10, 2013 - 2:03:10 PM - Bob Read The Tip

How did you get BIDS in VS 2010 ??    

What v SQL are you using 2012 ?


Wednesday, April 10, 2013 - 2:57:44 PM - Scott Murray Read The Tip

Yes 2012


Wednesday, April 10, 2013 - 6:50:26 PM - Mohammed Read The Tip

Cool Stuff.


Thursday, April 11, 2013 - 8:53:18 AM - Jack Hellmuth Read The Tip

You mention Google Maps could be done the same way but don't they require some kind of license to do this without adverse consequences?


Monday, October 21, 2013 - 7:26:02 AM - Gangadhar Read The Tip

Thank you for the nice post :)


Monday, October 28, 2013 - 9:39:28 AM - Jason W Read The Tip

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.

jump to address
="javascript:void(window.open('http://maps.google.com/maps?q="+CStr(Fields!Address.Value)+","+CStr(Fields!City.Value)+","+CStr(Fields!State.Value)+","+CStr(Fields!Zip.Value)+"','_blank'))"


jump to directions with start and finish - you can mix and match coordinates or physical address
="javascript:void(window.open('http://maps.google.com/?saddr="+CStr(Fields!Address.Value)+","+CStr(Fields!City.Value)+","+CStr(Fields!State.Value)+","+CStr(Fields!Zip.Value)+" &daddr= "+CStr(Fields!Lattitude.Value)+","+CStr(Fields!Longitude.Value)+"','_blank'))"

jump to coordinates
="javascript:void(window.open('http://maps.google.com/maps?q="+CStr(Fields!Lattitude.Value)+","+CStr(Fields!Longitude.Value)+"','_blank'))"

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.

http://maps.google.com/maps?q=from:+ Lat ,Lon+to:+ Lat ,Lon+to:+ Physical Address 


Wednesday, March 05, 2014 - 4:27:49 AM - Ganesh Shankar Dixit Read The Tip

Excellent explinantion Scott!! Thanks



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.