SSRS - Create a QR report

QR code is becoming popular. So what if we put QR code into the report, such as a contact report with QR codes? Sounds interesting, isn't it? Following steps below you can create a simple report with QR code integrated, no code required!


Firstly we need a report to show some basic contact information. In this example, I just make it very sample, as shown below

Then I drag and drop a tablix control on to the report. After set first few columns, I drop an image into the last column

Now here is the interesting part: the QR code actually is coming from http://qrcode.kaywa.com/. This site can encode your input into QR code (static) and export the result as an image. And for personal use, you can create a free account. So what we need to do is to set the image property to the output of this site

As you can see, I change the image source to external, and then I set the image expression to:

 = "http://qrcode.kaywa.com/img.php?s=8&d=" + Fields!name.Value + "%0A" + Fields!site.Value + "%0A" + Fields!mob.Value  

Now run the report:

When you run the report, you may see the warning message:

"Images with external URL references will not display if the report is published to a report server without an UnattendedExecutionAccount or the target image(s) are not enabled for anonymous access."

It is because of reporting service execution account setting. To configue a default reporting service execution account, you can open Reporting Service Configuration Manager, and navigate to Execution Account page, fill in default account information, as shown below.

SQL Server Spatial: let's draw something

Now it is the second post in my blog. In this post I will show you steps to draw a picture in SQL Server by utilizing its spatial function. The idea is coming from here but will be a bit different.

Firstly we need to find a source picturem, such as below one

 



Now we need to convert it to eps. I found this website provide quite good function to do so. Simply upload the image, select the output format, click button, save the result, then done.

My next step is to open the file in text editor, and copy all contents into Excel. As mentioned, Excel is quite good to do some batch jobs in this case.

So starts from here, we need to do some batch jobs.

Firstly, we can see there are quite a lot indicators in the file, such as /m { moveto } bd, /l { lineto } bd, etc. We only need to handle moveto, lineto, and curveto to draw the image. So use Ctrl+H in Excel to replace all "/l" to "/lineto", "/m" to "/moveto", and "/c" to "/curveto".

Next in column B I extract last few characters from column A(=TRIM(RIGHT(TRIM(A2), 7))) , because through Excel filter, we can remove rows we dont need. I name this column as direction, because it tells me what I need to do:

• moveto means jump to another point, I am not going to handle it;
• lineto means draw a LINESTRING
• curveto means a CIRCULARSTRING

Then in column C I use replace function to get points (=TRIM(REPLACE(A2, FIND(B2, A2, 1), LEN(B2), ""))). This column is named as position



So now we have a file with all point information and direction information.

Finally we can use excel function to generate statements, such as
select geometry::Parse('CIRCULARSTRING(158.356 181, 152.733 183.330, 155 189)')


One extra step you might want to take, is to optimize the spatial statements. In my case, the Santa image contains more than 3800 lines, after I combine them and order them, excution speed is much faster.

Try it and enjoy the image :P