HOWTO: Creating 2D Barcodes in Microsoft Reporting Service 2000 and 2005

SUMMARY

This article demonstrates how to add 2D barcodes (PDF417 or DataMatrix) to Microsoft Reporting Service. The method described here uses the font-based approach. Deployment is also included.

Warning

The method described in this KB article applies on DataMatrix Fontware version 3.x and PDF417 FOntware 3.x only. In later versions the function prototype has been changed. Rfer to their product manuals for more information.

MORE INFORMATION

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.

Preparation

Before adding barcodes into report, you need perform several preparation tasks - compiling and installing custom assembly that provides the barcode functionality.

Compiling Custom Assembly

The source code is attached and can be downloaded here. The zip file includes two C# files, and a VS2005 project file and a VS2003 project file (with extension .csproj.2003). If you are compiling on VS2005 or above, open the project file in Visual Studio. For VS2003, remove .2003 extension before opening it. After building the project, you will get the DLL file named Morovia.SupportTools.ReportService.dll. We recommend that you keep the file name intact.

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.

If you are using 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.

Warning

The computer must also have the fontware product installed. For example, if you are creating DataMatrix barcodes, DataMatrix Fontware is required.

Understanding How 2D Fonts Work

Creating barcodes using a barcode font involves two distinct processes: encoding and rendering. In the encoding process, the data to be encoded are applied with various algorithm, including error correction, and are finally translated into a two dimensional array comprising 1 and 0 only. Further these 1s and 0s are compressed into 16 characters, each character drawing a couple of cells. We call the string generated from the encoding process Barcode String.

After you have the barcode string, all you need is to format the whole string with our font and select an appropriate font size. This is the rendering process.

Figure 1. Barcode Creating Process

Barcode Creating Process

Designing Report

In this tutorial, we add a column called barcode, which encodes the table column CustomerID in data matrix format. Although the data encoded may have variable length, we want all data matrix barcodes having the same size - a rectangle symbol of 12 by 26 modules.

  1. If you are adding barcodes into an existing project, skip to step 8. Now we start a new project. In Visual Studio, select FileNew Project. In the Project Types list box, click on Business Intelligence Projects. In the Templates box, select Report Project Wizard. Give the project a name and click OK.

  2. On the next screen, the Report Wizard asks for the data source. For simplicity we use the NorthWind database here, which is installed by default when installing SQL Server. You can type the connection string in the edit box directly, or you can have the wizard fill out for you by clicking the Edit... and select the appropriate choices from the Data Link Properties dialog followed.

    After you finish constructing the connection string, click Next.

  3. At this step, the Report Wizard asks for a query string which will be used to populate the data set in the report. You can enter something like Select * from Customers; or you can click on Edit... to pop up Query Builder to build the query. Make sure that you include CustomerID in the result set.

  4. Next, select the type of the report (tabular or matrix). Click Next.

  5. Now select the fields which will be displayed in the report. In the Available fields list box, use your mouse and CTRL key, select CustomerID, CompanyName and ContactName fields. Move them to the right.

  6. Follow the instructions to complete the wizard.

  7. On the left side, switch to Solution View. Expand Reports node to find the report just created. Double click on the report file name to bring it up to the right side. You will notice that three tabs appear on the right view, and the current position is at Layout tab. In the report, a field name is expressed something like Fields!CustomerID.Value.

  8. We now add a new column to hold the barcodes. Click on the Company header column once, the report responds by showing a header row on the top. Right click on the header row, and select Insert Column at the right. Give the new column a name Barcode.

  9. To define the value for the column, you need to tell that you are going to use the the assembly from Morovia. When the current view is the report, the Report menu is visible on top of Visual Studio. Select Report followed by Report Properties to bring up the Report Properties dialog.

  10. Click on the Reference tab. In the top list box, click on the ...button. Click on Browse button once to locate the assembly: Morovia.SupportTools.ReportService.dll.

  11. Click on the General tab. Move the cursor to the Value edit box. Enter the following:

    =Morovia.SupportTools.ReportService.
    FontEncoder2D.DataMatrixEncode2(Fields!CustomerID.Value, 26) 

    The last part of the definition is the function name. Here we use DataMatrixEncode2 to make all barcodes the same size. Here 26 is the DataMatrixTargetSizeID (if you do not know the meaning of this parameter, see the DataMatrix Fontware Reference Manual). If uniformed size is not required, you can also use the DataMatrixEncode function.

  12. Click on the Font tab. From the font list, select MRV DataMatrix.

  13. Click on the Preview tab to view the report. The report should show as below:

  14. If you see a different shape rather than a rectangle, or just portions of a barcode, the width or the height of the column is too small for the barcode. Go back to the Layout view and use your mouse to make appropriate adjustments.

  15. After you are satisfied with the report, click BuildDeploy Solution, to deploy the solution to the Report Server. However, do read the deploy section in this article before you proceed with viewing report from your browser.

When there is an encoding error, for example, the data is too large to be encoded in the size specified, the function returns an error message in the place of the barcode. If an irregular barcode appears while there are enough spaces for the barcode display, most likely an error occurred. If that is the case, format the field with a text font and examine the message. If the size specified is too small, set a size that is capable of encoding the whole data, or set it to 0 for automatic size selection.

Function List

This section details the four functions supported by this assembly.

DataMatrixEncode

String DataMatrixEncode(String strDataToEncode); 
  • strDataToEncode

    A string type holds the data to be encoded.

Returns the barcode string for the data to be encoded. The barcode string becomes a data matrix barcode after being formatted with Morovia DataMatrix font. The barcode produced has the smallest possible size.

DataMatrixEncode2

String DataMatrixEncode2(String strDataToEncode, int target_size) 
  • strDataToEncode

    A string type holds the data to be encoded.

  • target_size

    An integer which indicates the DataMatrix shape. The value must be between 0 and 30. If the value is 0, the function produces the smallest barcode possible. You need to make sure that the size is big enough to hold the data, otherwise an error is returned.

Returns the barcode string which becomes a data matrix barcode after being formatted with a Morovia DataMatrix font. This function allows you to specify the size that you desire.

PDF417Encode

String PDF417Encode(String strDataToEncode); 
  • strDataToEncode

    A string type holds the data to be encoded.

Returns the barcode string which becomes a PDF417 barcode after being formatted with a Morovia PDF417 font. The barcode is created under SecurityLevel at 9 (automatic), and both MaxRows and MaxCols are 0 (i.e. you have no control over the final size of the barcode).

PDF417Encode2

String PDF417Encode2(String strDataToEncode, 
    int max_rows,
    int max_cols,
    int security_level); 
  • strDataToEncode

    A string type holds the data to be encoded.

  • max_rows

    The number of rows in the PDF417 barcodes created. Must be either between 3 and 90, or 0, under which the program determines the actual value used.

  • max_cols

    The number of columns in the PDF417 barcodes created. The valid range for this parameter is between 0 and 30. When 0 is specified, the program determines the actual value used.

  • security_level

    The security level to be used in the PDF417 barcodes created. A PDF417 barcode can have security level ranging from 0 and 8. The program uses 9 for automatic selection of security level.

Changing Security Policy

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):

    <CodeGroup class="FirstMatchCodeGroup" 
      version="1" 
      PermissionSetName="FullTrust"
      Name="MoroviaSupportToolsReportServicedll" Description="Morovia.SupportTools.ReportService.dll">
      <IMembershipCondition class="UrlMembershipCondition" version="1"
         Url="C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\Morovia.SupportTools.ReportService.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, as the image below:

    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.

APPLIES TO

  • PDF417 Fontware

  • DataMatrix Fontware

  • Visual Studio 2003 / Reporting Service 2000

  • Visual Studio 2005 / Reporting Service 2005