Chapter 8. Adding PDF417 Symbols to SQL Server Reporting Service

Microsoft SQL Server Reporting Service has gained momentum in enterprise reporting market. SQL Server Reporting service includes a Report Designer and Report Server. The Report Designer works with Visual Studio 2003 and 2005, allowing developers to quickly design and deploy a report.

8.1. Custom Assembly

SSRS can't use the encoder DLL directly. A ready-to-use custom assembly (ReportServicePlugin_PDF417.dll) built under Visual Studio 2003 (.net 1.1) 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_PDF417_src.zip, which is located under the program folder.

This assembly exposes two functions: PDF417Encode and PDF417CompactEncode. The prototype for this function is as below:

string PDF417Encode(string strDataToEncode, 
    int numRows, int numCols,
    int securityLevel, 
    double aspectRatio,
    double yHeight); 
    
string PDF417CompactEncode(string strDataToEncode, 
    int numRows, int numCols,
    int securityLevel, 
    double aspectRatio,
    double yHeight); 

The two functions have the same signature. The only difference is that the first function produces barcode strings for full size PDF417 barcodes, and the other compact PDF417 barcodes. or the meanings of the parameter, refer to Section 5.3, “PDF417Encode2”. Note that the yHeight parameter should match the font used in order to calculate the aspect ratio correctly, unless you are not using this parameter. To learn how parameters affect the symbol size, see Section 2.3, “Sizing Parameters”.

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, copy the DLL into the following directories:

  • Report Designer Folder: [Program Files]Microsoft Visual Studio 8\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 PDF417 Barcodes to Report

In this tutorial, we demonstrate how you can add barcodes into a report. Assume that we have a table called orders with the following data and structure:

The task is to print labels with OrderID printed on the top and a PDF417 barcode on the bottom with all fields encoded into the barcode. Each field is separated with a control character \n (ASCII 10).

  1. First we build the SQL statement required for this report. It should produce two fields, the first is OrderID and the second one with all field concatenated together. We give the second field a new name DataToEncode

    select OrderID, 
      Cast(OrderID As VarChar(4)) + Char(10) + 
      Company + Char(10) + ModelNumber + Char(10) + 
      SerialNumber As DataToEncode
    From Orders; 
  2. Layout the report as desired. Make changes to the format of each field. The result looks like below:

  3. In order to call the function we need to add reference to the custom assembly. Select ReportReport Properties to bring up Report Properties dialog.

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

  5. Click on the DataToEncode text box, and bring up its property sheet. Under the General tab, change its value to the formula below:

    =Morovia.ReportService.PDF417V4.PDF417Encode(Fields!DataToEncode.Value, 0, 4, 9, 0, 3.1)

    The formula calls the PDF417Encode function with the following parameter: rows (0), columns(4), security level (auto), aspect ratio(0) and y-height(3.1). We want the barcode grow vertically, so we set rows=0 and cols=4 (requiring the barcode to have 4 data columns).

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

  7. Format the text box with MRV PDF417 N3, 12 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.1, “List of PDF417 fonts”, MRV PDF417 N3 produces barcodes with X dimension at 13.35 mils at 12 points. Now translate it into inches and multiply the result by the printer resolution: 13.35x0.001*203=2.71.

  2. 2.71 is not a optimized value so we either round to 3, or 2. 3 will render X dimension as 3*1000/203=14.77 mils. The optimal font size therefore is 14.77*12/13.35=13.28 points.

  3. Change the font size of the barcode text box to 13.28 points. Now the barcode produced will have high quality when printed on target printers.

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 8\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):[6]

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



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