This will allow you to create "Data-Driven" subscriptions on your Standard SQL Server version.
While the QRCode4CS open source library used in the article, Generate two-dimensional QR Code® bar codes in an SSRS report, enables the display of QR Codes in SSRS reports, it turns out that it only supports the Micro QR Code standard, which limits the input string to 34 characters.
In contrast, the open source QRCoder library can support an input string of up to 2952 characters.
This article uses code extracted from the following QRCoder project archive: https://github.com/codebude/QRCoder/archive/master.zip >> QRCoder-master.zip
These are the steps required to create an SSRS report that displays QR code symbols using the QRCoder library
- Obtain existing or compile the two QRCoder assemblies with strong names and the PartiallyTrustedCallers attribute*
- Install the two assemblies to the global assembly cache (GAC) using the gacutil.exe utility
- Create a new SSRS report that queries a table of test data
- Add a reference to the QRCoderImageToByteArray GAC assembly
- Add a custom code function that sends a string to the QRCoderImageToByteArray assembly and accepts a byte array in return
- Add a field to the report and increase the physical dimensions of the field to accomodate a larger QR code symbol
- Drag-and-drop an image componenet to the new field to bring up the image properties dialogue
- Change the image source to Database, add a function that references the custom code function and change the MIME type to image/png
- Set the size property of the image component to fill the available field space while maintaining the original aspect ratio
- Execute the report to display the data and the QR code generated from the data
*A strong name is required to insert an assemby into the GAC. SSRS requires assemblies it references to include the PartiallyTrustedCallers attribute.
Resource file content
Unzip the attached QRCoderResources.zip file and browse to the extracted QRCoderResources folder in File Explorer.
The QRCoderDemoExe folder contains an executable that can be used to test the output of the QRCoder library.
The QRCoderDemoSln, QRCoderImageToByteArraySln and QRCodeSln folders contain the C# projects used to compile the QR Coder demo application and assemblies. (You can load these projects into Visual Studio or SharpDevelop and compile them yourself if you wish.)
The StronglyNamedPartiallyTrustedCallersDLLs folder contains the assemblies that will be used to generate QR Codes in an SSRS report.
The QRCoder-master.zip file is the original QRCoder archive downloaded from github.
Test the QRCoder library
Browse to the QRCoderDemoExe folder to display the QRCoder.dll assembly and the QRCoderDemo application that uses it to generate QRCodes.
Double-click the QRCodeDemo application to generate a QRCode symbol.
By default the application generates a QR Code for the string 1234567890, but you can enter any string you wish into the textbox to generate a symbol for that string.
Test the application with typical character strings you wish to convert to QR Code symbols and read the symbols with your QR Code reader of choice to confirm the library is satisfactory for your purposes.
Assemblies used to generate QR Code symbols in SSRS reports
Browse to the StronglyNamedPartiallyTrustedCallersDLLs folder.
The QRCoder.dll assembly can generate QR Code symbols from an input string in a variety of image formats including bitmap.
SQL Server Reporting Services cannot display images directly, however, but requires images to be streamed as byte arrays. To address this, the QRCoderImageToByteArray.dll assembly passes an input string to the BitmapByteQRCode() function of the QRCoder.dll assembly and streams the returned bitmap image as a byte array to SSRS.
Install the assemblies to the global assembly cache (GAC)
Find gacutil.exe with Agent Ransack or File Explorer.
Copy the path of the appropriate 32- or 64-bit version of gacutil.exe:
C:Program Files (x86)Microsoft SDKsWindowsv10.0AbinNETFX 4.6.1 Toolsx64gacutil.exe
Open a command console as administrator and change to the StronglyNamedPartiallyTrustedCallersDLLs folder.
Install the assemblies to the GAC with the following commands:
"C:Program Files (x86)Microsoft SDKsWindowsv10.0AbinNETFX 4.6.1 Toolsx64gacutil.exe" -i QRCoder.dll
"C:Program Files (x86)Microsoft SDKsWindowsv10.0AbinNETFX 4.6.1 Toolsx64gacutil.exe" -i QRCoderImageToByteArray.dll
Confirm the assemblies have been added to the C:WindowsMicrosoft.NETassemblyGAC_MSIL folder.
Create test data for the report
Open a new query window in SQL Server Management Studio and paste the following SQL code into it.
BEGIN TRY DROP DATABASE TestDB END TRY BEGIN CATCH END CATCH GO BEGIN TRY CREATE DATABASE TestDB END TRY BEGIN CATCH END CATCH GO USE TestDB BEGIN TRY DROP TABLE TestData END TRY BEGIN CATCH END CATCH GO CREATE TABLE [dbo].[TestData]( [City] [nvarchar](50) NULL, [Zip] [nvarchar](50) NULL ) ON [PRIMARY] GO INSERT INTO [dbo].[TestData] (City,Zip) VALUES ('Seattle','98101') INSERT INTO [dbo].[TestData] (City,Zip) VALUES ('Bangor','04402') INSERT INTO [dbo].[TestData] (City,Zip) VALUES ('Miami','33133') INSERT INTO [dbo].[TestData] (City,Zip) VALUES ('Houston','77001') INSERT INTO [dbo].[TestData] (City,Zip) VALUES ('Boston','02201') SELECT * FROM [dbo].[TestData]
Execute the query to create a database named TestDB and a table named TestData that contains some zip code data.
Create and execute the QRCoder Demo SSRS report
Start a new Report Server Project Wizard in Visual Studio.
Insert the appropriate connection string information.
Insert the following code into the Query string text box.
SELECT city,zip FROM dbo.TestData
Select tabular report type.
Move the city and zip fields into the Details text box.
Click the finish button.
Select Report Properties from the Report menu.
Click on the References tab followed by the add button then the browse tab and browse to the QRCoderImgeToByteArray.dll assembly in the GAC.
Click on the Code tab and add the following function to the custom code text box:
Public Function QRCoderByteArray(ByVal inputString As String) as Byte() Return QRCoderImageToByteArray.QRCoderImageToByteArrayClass.renderBmpToByteArray(inputString) End Function
RIght-click on the top of the zip column and insert a new column to the right.
Increase the height of the data row and the width of the new column to make room for a larger QRCode symbol.
Drag-and-drop an image component into the new field to bring up the image properties window.
Select Database as the image source and click on the use this field function button.
Enter the following code into the expression text box:
=Code.QRCoderByteArray(Fields!city.Value + " " + Fields!zip.Value)
Select image/png as MIME type:
Click on the size node and select the fit proportional radio button and click OK.
Click on the Preview tab of the report console.
The city, zip and QR code for the combined city and zip are displayed in the report.
The barcode scanner app on my phone correctly deciphers the first QR code symbol.
The previous article on displaying QR code symbols in SSRS reports used an open source library which, unknown to me at the time, only supported the Micro QR Code.standard, which limited it to a 34-character input string.
The QRCoder library is a more rigorous implementation of the ISO/IEC 18004 QR Code standard. It encodes up to 2952 alphanumeric characters, which is the specified binary/byte maximum for the standard.