Create barcodes in a Reporting Services Report with SQL Server Data Tools

SQL Server Data Tools (SSDT) is a modern development tool for building SQL Server relational databases, Azure SQL databases, Analysis Services data models, Integration Services packages, and Reporting Services reports. This tutorial illustrates how to create industrial quality barcodes in a Reporting Services Report with SSDT using Visual Studio.

Prerequisites

  • Visual Studio 2017 installed
  • SSDT for Visual Studio installed. The SSDT (SQL Server Data Tools) component found in Workloads->Data storage and processing->SSDT installed with Visual Studio 2017 installer.
  • SSDT standalone. The Analysis Services, Integration Services, and Reporting Services project support installed using SSDT Standalone installer.
  • SQL Server 2017 installed
  • AdventureWorks database (or other database) installed
  • Reporting Services 2017 installed
  • ConnectCode Barcode Software and Fonts installed
Configuring Visual Studio 2017
Copying "ConnectCodeBarcodeFontsLibrary.dll"

You need to copy "ConnectCodeBarcodeFontsLibrary.dll" (Resource\Net DLL Integration Samples\DLL\.Net4.0 subdirectory) to the "SSRS" directory of Visual Studio 2017. The default location of this directory is at


C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\CommonExtensions\Microsoft\SSRS		



You will also need to copy "ConnectCodeBarcodeFontsLibrary.dll" to the "PrivateAssemblies" directory of Visual Studio 2017. The default location of this directory is at


C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\PrivateAssemblies		



Editing "RSPreviewPolicy.config" to grant FullTrust permission

The "RSPreviewPolicy.config" is the preview policy file of Report Designer. The default path for this file is


C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\CommonExtensions\Microsoft\SSRS\RSPreviewPolicy.config



The following tag needs to be added to the "RSPreviewPolicy.config" file to grant FullTrust permission to the "ConnectCodeBarcodeFontsLibrary.dll". This is required for previewing a Report that uses a DLL in the Report Designer.


<CodeGroup class="UnionCodeGroup" Name="BarcodeFonts" version="1" PermissionSetName="FullTrust"		
Description="This code group grants ConnectCodeBarcodeFontsLibrary.dll FullTrust permission."> 
<IMembershipCondition class="UrlMembershipCondition" version="1" 
Url="C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\
CommonExtensions\Microsoft\SSRS\ConnectCodeBarcodeFontsLibrary.dll"/>
</CodeGroup>  



Configuring Reporting Services
At a file level SQL Server Reporting Services 2017 is now completely separated from SQL Server file structure. This different from previous versions of Reporting Services and SQL Server.

Copying "ConnectCodeBarcodeFontsLibrary.dll"

You need to copy "ConnectCodeBarcodeFontsLibrary.dll" (Resource\Net DLL Integration Samples\DLL\.Net4.0 subdirectory) to the "ReportServer\bin" directory of SQL Server Reporting Services 2017. The default location of this directory is at


C:\Program Files\Microsoft SQL Server Reporting Services\SSRS\Reporting Services\ReportServer\bin		



Editing "rssrvpolicy.config" to grant FullTrust permission

The "rssrvpolicy.config" is the Report Server policy configuration file. The default path for this file is


C:\Program Files\Microsoft SQL Server Reporting Services\SSRS\Reporting Services\ReportServer\rssrvpolicy.config	



The following tag needs to be added to the "rssrvpolicy.config" file. It grants FullTrust permission to ConnectCodeBarcodeFontsLibrary.dll in the Report Server.


<CodeGroup class="UnionCodeGroup" Name="BarcodeFonts" version="1" PermissionSetName="FullTrust" 
Description="This code group grants ConnectCodeBarcodeFontsLibrary.dll FullTrust permission."> 		
<IMembershipCondition class="UrlMembershipCondition" version="1"
Url="C:\Program Files\Microsoft SQL Server Reporting Services\SSRS\
Reporting Services\ReportServer\bin\ConnectCodeBarcodeFontsLibrary.dll"/> 
</CodeGroup>
 	 


Create barcodes in a Report Server project (SSDT Reporting Services)
1. Launch Visual Studio and create a new Project. Select "Business Intelligence->Report Server Project Wizard". Enter a name for your project and click on the "OK" button. If you did not see the "Business Intelligence" item in your project type, make sure you have installed SSDT and SSDT standalone successfully.



2. A "Welcome to the Report Wizard" will be launched to provide you with an overview of the steps required to create a report. Click on the "Next" button. You should see the "Select the Data Source" dialog as shown below:



3. Click on the "Edit" button to setup the Data source. In the screenshot below our "AdventureWorks" database is stored in the "DESKTOP-UNTIFK9" Server with Windows Authentication. You can choose to setup the connection to your own database. After setting up the properties, you can click on the "Test Connection" button to ensure a successful connection before proceeding. Click on the "OK" button when you are ready.



4. In the "Select the Data Source" dialog, click on the "Next" button. In the "Design the Query" dialog, click on the "Query Builder" button. You should see the following dialog as shown in the screenshot below:



5. We are going to select 3 fields (columns) from the "Production" table. Enter the following query and click on the "OK" button:


SELECT ProductID, Name, ProductNumber from Production.Product
 


6. In the "Select the Report Type" dialog, select a "Tabular" report and click on the "Next" button. In the "Design the Table" dialog, select all the available fields and add them to the "Details" section as shown below:



7. Click on the "Next" button followed by the "Finish" button.



8. We have now successfully created Reporting Services (.rdl) report in Visual Studio. Click on the "Design" tab and then click on the "Report->Report Properties" menu item. We are going to add a reference to our "ConnectCodeBarcodeFontsLibrary.dll". This DLL (Dynamic Link Library) will be used to help you generate a barcode in the report.

Click on the "References" tab and click on the "Add" button followed by the "..." button to add an assembly. In the "Add Reference" dialog, click on the "Browse" tab and navigate to the "C:\Program Files (x86)\ConnectCode\Resource\Net DLL Integration Samples\.Net4.0" folder. Select the "ConnectCodeBarcodeFontsLibrary.dll" and click on the "OK" button.



Click on the "Add" button in "Add or remove classes". Enter "Net.ConnectCode.Barcode.BarcodeFonts" as the class name and "barcode" as the instance name and click on the "OK" button. We have successfully added a reference to the DLL and created an instance object for generating barcodes.

9. Click on the Code tab and enter the following:


Public Function MakeBarcode(datastring As String) as String 
barcode.BarcodeType = Net.ConnectCode.Barcode.BarcodeFonts.BarcodeEnum.Code128Auto	
barcode.Data = datastring
barcode.encode()
return barcode.EncodedData 
End Function  



The programming codes above uses the barcode instance object to generate a Code 128 (Auto Subset) barcode. In a Code 128 barcode, the check digit is mandatory. For some barcodes such as Code 39, the check digit is optional. You can use the following line to turn the check digit on or off.


barcode.CheckDigit = Net.ConnectCode.Barcode.BarcodeFonts.YesNoEnum.Yes		



Next, click on the "OK" button to exit from the "Report Properties" dialog.

10. In the "Design" tab of "Report1.rdl", right click on the last column of the table and select "Insert Column->Right". We are going to add a column for our barcodes.



11. In the Toolbox, select a "Text box"object and add it into the column from the previous step. The output characters generated by the DLL will be placed in the "Text box".



12. Right click on the "Text Box" object and select "Expression". Enter the following expression:


=Code.MakeBarcode(Fields!ProductNumber.Value)



If you recall, "MakeBarcode" is a function we have defined in "Report Properties". And, in the above, we apply the function on the "ProductNumber" field. The result will be placed in the "Text box" field.

13. In the previous steps, we have defined a "MakeBarcode" function that uses the "ConnectCodeBarcodeFontsLibrary.dll" to generate a barcode. To be more exact, the "MakeBarcode" function returns a stream of output characters. These output characters when applied with a barcode font gives you an industrial quality barcode.

Next, we are going to apply a barcode font to the "Text box" that contains our output characters. Click on the "Text box" object and expand the "Font" properties as shown below. Set the "Font" to "CCode128_S3" (or "CCode128_S3_Trial") and "FontSize" to "24". You can reduce or increase the "FontSize" later to meet the size requirements of your barcode. The registered package of ConnectCode Barcode Software and Fonts also comes with a complete set of fonts with different heights to satisfy all requirements required by the auto-id industry.



14. Save all the files and click on the "Preview" tab. You should see the following report with Code 128 barcodes generated using the "ProductNumber" field.



The complete list of barcode API functions and fonts available are described at the following:

https://barcoderesource.com/dotNetDLLBarcodeFonts.shtml