Add Carrier Tracking Link in SQL Reporting Services

I have run into this before. I need to add a link to an outside website and feed in a parameter. This time it was FedEx, UPS, USPS and DHL.

Instead of hard coding the URL’s into the report, and using a series of “If Thens” or “Case Selects” I decided to create a lookup table with all of the carriers and their URLs. From this table, I associated it with an order id and pulled the shipping type.

Orderid - Int32

Tracking Number - varchar(75)

Tracking Carrier - varchar(50)

URL - varchar(150)

Now, I also wanted this to open in a new window. Javascript seemed the obvious solution here. So, I right click on top of the field I want hyperlinked and choose Text Box Properties.

Now choose Action, select Go To URL and click the Function key next to the Select URL text box. This is where we will add the javascript.

The Expression box will be displayed. Make sure the fields are available from your query.

The Javascript used is:

="javascript:void(window.open('" &Fields!URL.Value & Fields!TrackingNumber.Value & "', '_new'))"

This will build the URL + Tracking Number and when clicked, present the user with a new browser window with the tracking information.

Here are the carriers and their URLs:

FedEx:
http://fedex.com/Tracking?action=track&tracknumber_list=%num%&cntry_code=us
DHL: :
http://www.dhl.com/content/g0/en/express/tracking.shtml?brand=DHL&AWB={tracking_number}
UPS: :
http://wwwapps.ups.com/etracking/tracking.cgi?TypeOfInquiryNumber=T&InquiryNumber1={tracking_number}
USPS: :
http://trkcnfrm1.smi.usps.com/PTSInternetWeb/InterLabelInquiry.do?origTrackNum={Tracking Number}


Have fun!