Chapter 10. Adding QRCode Symbols to SQL Server Reporting Service

Table of Contents

10.1. Custom Assembly
10.1.1. Installing Custom Assembly
10.2. Adding QRCode to Report
10.3. Deployment

Adding QRCode symbols to SQL Reporting Service report is straightforward with QRCode Font & Encoder 5. This chapter explains how you can achieve the objective. Our report service plugin supports SQL Reporting Service 2000, 2005 and 2008. This chapter uses Visual Studio 2008/SQL Reporting Service 2008 for demonstration. To follow the steps, you also need to have AdventureWorks sample database installed.

10.1. Custom Assembly

10.1.1. Installing Custom Assembly

SSRS can't use the native encoder DLL directly. A ready-to-use custom assemblies built under Visual Studio 2005 (.net 2.0) is included in the software.

Note

In case that you need to build the assembly by yourself, The source code is also included in the distribution. The project files are packed in ReportServicePlugin_src.zip, which is located under the program (x86) folder. The build script is written in NAnt.

This assembly exposes single function: QRCodeEncode. The prototype for this function is as below:

  string QRCodeEncode(string strDataToEncode, 
    int versionRequested, int ecLevel);

The function is quite simple: the first parameter is the data encoded, followed by the version ID requested and error correction level. The function returns the barcode string if successful, otherwise ERROR is returned.

The screen shots in this tutorial are taken from Visual Studio 2008. If you are using VS 2005, remember to change the directory to [Program Files]Microsoft Visual Studio 9.0. Other parts are identical between the two.

10.1.1. Installing Custom Assembly

For Reporting Service 2000, copy the DLL into the following two directories:

  • Report Designer Folder: [Program Files]Microsoft SQL Server\80\Tools\Report Designer.

  • Deploy folder [Program Files]Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin.

For Reporting Service 2005 or 2008, copy the DLL into the following directories:

  • Report Designer Folder: [Program Files]Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies.

  • Deploy folder [Program Files]Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin.

If you have multiple SQL Server instances, you will have multiple MSSQL.n directories. Locate the one that the Reporting Service is installed under.

If you are running VisualStudio 2008, or BI Development Studio 2008, you need to perform an additional task. You need to grant additional permission in order to print preview the report. Refer to Section 10.3, “Deployment” to modify policy file RSPreviewPolicy.confg.

10.2. Adding QRCode to Report

In this tutorial, we demonstrate how you can add QRCode symbols into a report. The data table is Person.Contact, available in AdventureWorks sample database. We'd like to present email address in QRCode barcode.

  1. Open Visual Studio or SQL Server Business Intelligence Development Studio and create a new Report Server Project.

  2. In Query builder, enter SQL statement SELECT * FROM Person.Contact.

  3. Layout the report as desired. Make changes to the format of each field. Add a new column to the right that will hold the barcodes. The result looks like below:

  4. Select Report → Report Properties to bring up Report Properties dialog.

  5. click on References tab. Navigate to the location of the customer assembly and add it.

  6. Click on the new column created, and select expressions, change its value to the formula below:

    =Morovia.ReportService.QRCodeV5.QRCodeEncode(Fields!EmailAddress.Value, 0, 0)

    The formula calls the QRCodeEncode function with automatic size selection and error correction level L.

  7. Now preview the report. You should see lines of hexadecimal characters at the place of the barcode.

  8. Format the text box with MRV QRCode5, 6 points. Preview the report again, you should see the barcodes.

If you are printing to a laser printer, you should get a good quality barcode label printed. Now if you are printing to a low resolution thermal printer, such as a Zebra with 203 dpi in resolution, you should examine if the font size produces the optimal results. Follow the steps below:

  1. According to Table 2.2, “QRCode Font Characteristics”, MRV QRCode produces barcodes with X dimension at 20 mils at 6 points. Now convert it into inches and multiply the result by the printer resolution: 20x0.001*203=4.

  2. 4 pixels is optimized value. Therefore we can use 6 points on 203 thermal printer.

10.3. Deployment

The Reporting Service require any custom assembly defined in the security policy otherwise a run-time error will be thrown and all you get is #Error without any explanation. Follow the steps below to change security policy. Two security policy files are required to change:

  • RSPreviewPolicy.config. This policy file is used for DebugLocal preview in Visual Studio. This file is located in the Report Designer folder which is [Program Files]Microsoft SQL Server\80\Tools\Report Designer for RS2000 and [Program Files]Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies for RS2005.

  • rssrvpolicy.config, the policy file used for running Report Server. The file is located under [Program Files]Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin directory.

Perform the following steps to add security policy required to run custom assembly:

  1. Open RSPreviewPolicy.config, and add the following content at the end (just before two ending CodeGroup tags):[4]

    <CodeGroup class="FirstMatchCodeGroup" 
  version="1" 
  PermissionSetName="FullTrust"
  Name="ReportServicePlugin_QRCode5.dll" Description="ReportServicePlugin_QRCode5.dll">
  <IMembershipCondition class="UrlMembershipCondition" version="1"
Url="C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\" \
"PrivateAssemblies\ReportServicePlugin_QRCode5.dll" />
</CodeGroup>

    Note that on RS2000 the custom assembly is located in a different directory.

  2. Save the file. In Visual Studio, change the active configuration to DebugLocal and run the report. You should see the barcodes on the report. Examine the contents in the Output window.

    If you see message A first chance exception of type 'System.Security.SecurityException' occurred in mscorlib.dll, the security is not configured properly.

  3. After you have successfully run the report under DebugLocal configuration, publish the report to the Report Server. Open rssrvpolicy.config and add similar lines.

    <CodeGroup class="FirstMatchCodeGroup" 
  version="1" 
  PermissionSetName="FullTrust"
  Name="ReportServicePlugin_QRCode5.dll" Description="ReportServicePlugin_QRCode5.dll">
  <IMembershipCondition class="UrlMembershipCondition" version="1"
Url="C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\" \
"ReportServer\bin\ReportServicePlugin_QRCode5.dll" />
  </CodeGroup>

    Note that you may change the file path if it is located in a different location.

  4. Restart SQL Server Reporting Service and browse the report. You should see the barcodes on the report this time.


[4] The value for Url attribute is too long to print in a single line If you copy the code verbatim, combine the two lines together manually. Do the same for the next XML snippet.