Chapter 8. Adding DataMatrix Symbols to SQL Server Reporting Service

Table of Contents

8.1. Custom Assembly
8.1.1. Installing Custom Assembly
8.2. Adding DataMatrix Barcodes to Report
8.3. Deployment

Adding data matrix barcodes to SQL Reporting Service report becomes an easy task after you installed DataMatrix 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.

Note

This chapter contains instructions of working on SSRS 2008 and prior versions. For working with SQL Server Reporting Service 2010 or above, visit KB10643.

8.1. Custom Assembly

8.1.1. Installing Custom Assembly

SSRS can't use the 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.zip, which is located under the program (x86) folder.

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

string DataMatrixEncode(string strDataToEncode, 
    int sizeIDRequested);

The function is quite simple: the first parameter is the data encoded, followed by the size ID requested. The function returns the barcode string if succesful, otherwise ERROR is returned.

8.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.

8.2. Adding DataMatrix Barcodes to Report

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

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

  2. In Query builder, enter SQL statement SELECT * FROMPerson.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.DataMatrixV5.DataMatrixEncode(Fields!EmailAddress.Value,-1)

    The formula calls the DataMatrixEncode function with automatic size selection.

  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 DataMatrix5, 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, “DataMatrix Font Characteristics”, MRV DataMatrix5 produces barcodes with X dimension at 20 mils at 6 points. Now translate 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.

8.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):[2]

    <CodeGroup class="FirstMatchCodeGroup" 
  version="1" 
  PermissionSetName="FullTrust"
  Name="ReportServicePlugin_DataMatrix5.dll" Description="ReportServicePlugin_DataMatrix5.dll">
  <IMembershipCondition class="UrlMembershipCondition" version="1"
Url="C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\" \
"PrivateAssemblies\ReportServicePlugin_DataMatrix5.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_DataMatrix5.dll" Description="ReportServicePlugin_DataMatrix5.dll">
  <IMembershipCondition class="UrlMembershipCondition" version="1"
Url="C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\" \
"ReportServer\bin\ReportServicePlugin_DataMatrix5.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.


[2] 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.