SQL Reporting Service – Create An Application

Well, here I am again. I seem to be getting better at posting these days, and here's a fun one!

A few years ago, I had a customer that had a report that listed items in a catalog. At times, they would print out the report and manually scratch out items that needed to be removed from the catalog. I recognized what they were doing one day and suggested we build a web page for them to use to edit the items. Since they were so familiar with the report, I decided to try to embed a link that would remove the items from the database.
So, we added a parameter to the default page that accepted input and passed in the ProductID, which was then fed to a SQL Stored Procedure which deleted the item from the catalog. Below is the link we used to make the call to the we page.

="javascript:void(window.open('http://www.mywebsite.com/default.aspx?param=Delete&prodid=" & Fields!ProductID.Value & "', '_new'))"


Where does this go?

Right click on top of the text or field you want to link to the trigger the event, in our case a delete event. Select the Text Box Properties link, and goto the Action dialog. Select Go to URL from the radio button list and then click the function button next to the Select URL text box.











In the Expression text box, type in the javascript call and click Ok.

  











That's it. Have Fun!

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!