SQL Server Data Tools Reporting Services QR Code

Create QR Code in Microsoft Reporting Services (SQL Server Data Tools - SSDT)

Prerequisites

Visual Studio 2017 or 2019
SQL Server Data Tools (SSDT) standalone or SSDT for Visual Studio installed. The SSDT component can be found in Visual Studio installer -> Workloads -> Data storage and processing -> SSDT.
SQL Server 2017 or 2019 installed
AdventureWorks database (or other databases) installed
Reporting Services installed
ConnectCode QR Code package installed

Configuring Visual Studio

Copying "QRCodeLibrary.dll"

You need to copy "QRCodeLibrary.dll" from the Resource\ReportingServices subdirectory to the "SSRS" directory of Visual Studio.


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

or 

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

You will also need to copy "QRCodeLibrary.dll" to the "PrivateAssemblies" directory of Visual Studio.


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

or

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.


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

or 

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 "QRCodeLibrary.dll". This is required for previewing a Report that uses a DLL in Report Designer.


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

or 

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

Configuring Reporting Services

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

Copying "QRCodeLibrary.dll"

You need to copy "QRCodeLibrary.dll" from the Resource\ReportingServices subdirectory to the "ReportServer\bin" directory of SQL Server Reporting Services.


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.


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 “QRCodeLibrary.dll” in Report Server.


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

Create QR Code in a Report Server project (Microsoft Reporting Services - SSDT)

1. Launch Visual Studio and create a new Project. Select "Report Server Project Wizard". Enter a name for your project and click on the "OK" button.

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 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. If you are using Visual Studio 2019, click on "Report1.rdl" in "Solutions Explorer", and then the "..." button in "Properties -> Assemblies". If you are using Visual Studio 2017, click on the "Report -> Report Properties" menu item. We are going to add a reference to our "QRCodeLibrary.dll". This DLL (Dynamic Link Library) will be used to help you generate a QR Code 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)\ConnectCodeQRCode\Resource\ReportingServices" folder. Select the "QRCodeLibrary.dll" and click on the "OK" button.

Click on the "Add" button in "Add or remove classes". Enter "Net.ConnectCode.BarcodeFontsStandard2D.QR" as the class name and "qrcode" 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 a QR Code.

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

	
Public Function MakeBarcode(datastring As String) as String 
return qrcode.Encode(datastring,"H",0)
End Function

The programming codes above use the barcode instance object to generate a QR Code barcode with the “datastring” parameter as the input data, “H” as the Error Correction Level, and 0 as the QR Code Mask.

Supported Error Correction Level

L - Allows recovery of up to 7% data loss
M - Allows recovery of up to 15% data loss
Q - Allows recovery of up to 25% data loss
H - Allows recovery of up to 30% data loss

Supported Mask: 8 (0 to 7 or 8 for Auto)

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 QR Codes.

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 "QRCodeLibrary.dll" to generate a QR Code. To be more exact, the "MakeBarcode" function returns a stream of output characters. These output characters when applied with QR Code barcode font gives you an industrial quality QR Code 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 "CCodeQR" (or "CCodeQR_Trial") and "FontSize" to "7". You can reduce or increase the "FontSize" later to meet the size requirements of your QR Code.

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