Pages

Monday, October 10, 2011

Generating QR codes in SSRS

Over the short span of my career, I have seen many people get burnt out and change their careers from technology to some other field. It is easy to get disillusioned with a job that requires you to sit on a chair the entire day and "code" the same stuff. At the same time, I have also met people who are so passionate and enthusiastic about what they do and are still coding away to glory after 15-20 years. What makes those people different, and why don't they get bored of the "same, old mundane coding stuff"? The answer is simple if you do get to observe them - their creative spirit. To quote Edward De Bono, "Creativity involves breaking out of established patterns in order to look at things in a different way." For these people, every day teaches them something new and they like to challenge their limits. They make out new things from the same stuff by looking at it from a different angle. I always make it a point to imbibe this quality in the people I mentor, and try to put across some puzzles where they have to reuse their previous learning in a new way. One of my recent questions was on how to display QR codes in SSRS.

Generating QR code in SSRS


On asking this question, most of the answers involved in getting some custom code written (but no-one knew how to write the code) or to get some plug-in from third party vendors (which involved licence costs). But the team had to put on their thinking caps when I said that the only thing they would require is access to the internet.

For demonstrating the purpose, I have made a report which will generate business cards for all the employees in my fictional company with QR codes. Follow the steps below to generate the report:-

1) Create a dataset with the following query

SELECT        'Jason Thomas' AS name, 'www.road-blogs.blogspot.com' AS URL, '00447574713732' AS mob
UNION ALL
SELECT        'Jay Thomas' AS name, 'www.beyondrelational.com' AS URL, '67891' AS mob
UNION ALL
SELECT        'Jean Elizabeth' AS name, 'http://twitter.com/de_unparagoned' AS URL, '12345' AS mob

Instead of this, you can also use information from your database tables or SSAS cubes. For the sake of simplicity, I am directly hardcoding my dataset.

2) Drag and drop a list into the layout. Then insert the name, URL and mob details into the list. Also add a company logo to make it look jazzy Winking smile

BIDS Layout - List

3) Drag and drop an image to the list and select the image properties.

Image properties

4) Go to the General tab and select the Image Source as External. Then click on the expression and enter the following expression

="http://qrcode.kaywa.com/img.php?s=8&d=" + Fields!name.Value + Constants.vbcrlf + Fields!URL.Value + Constants.vbcrlf + Fields!mob.Value

This is shown in the image below.

How to generate QR code

(You can append any string after the URL though I am using the Name, URL and mobile here)

5) Click on preview and you should be able to get the desired results.

End Result - QR codes in SSRS

The secret of getting this done is by using the Kaywa site which will accept parameters and output the QR code for that as an image. Now there are plenty of sites online which will generate QR codes as well as barcodes in this format, and you can use any one of them for doing the same. And since you have already learned before that it is possible to display External images in SSRS, generating QR codes or barcodes is just an application of that knowledge.

Disclaimer
This report was developed just for having some fun with SSRS and for educational purposes. For people who are going to be using this for commercial purposes, I would ask them to go through the conditions of the site which generates QR code (for eg, Kaywa states it’s use should be only for non-commercial reasons).

4 comments:

  1. Good post. I learned something new. Thanks for sharing.
    -ken-

    ReplyDelete
  2. I like this a lot, clean and easy for non technical people to use to generate new cards.

    Depending on the resources you have. Building the QR codes is possible 'in house' (avoiding the legal and other issues of relying on a third party site).

    I don't know about MS operating systems but under unix systems there is the 'qrencode' application which does the hard work. It wouldn't be difficult for someone with web development and linux skills to build a simple page to encode QR codes with a string handed to the web application.

    I built them in batch using google docs to capture data and a shell script to encode.

    My notes: http://david-halliday.co.uk/wiki/doku.php?id=linux:scripts:encode_qr_codes

    ReplyDelete
  3. QR codes are dynamic, you can redirect. This makes a permanent position in a prominent customer interaction constant current source QR code. This is why indicators are important.

    custom qr codes

    ReplyDelete
  4. Nice read, Thanks for sharing, I like the way you have scored your article. A big God Bless :-)

    ReplyDelete