Problem statement
Our requirement is to extract the data from JSON string, followed by filtering the extracted data, using business logics and display the result in SSRS Report.
Development challenges
- Data source is SharePoint list, so the developer isn't able to write SQL statement.
- SSRS allows only VB code and .NET Framework 2.0 or .NET Framework 3.5.
- Referenced and supporting DLL should present in the Report Server Bin folder and GAC.
- Function or method used inside the report should return only the string type.
Solution
The solution is to develop custom DLL file in .NET version 2.0 and reference the DLL in SSRS Report Code.
Let’s see the solution step by step.
Step 1
We have to create a DLL. Open Visual Studio 2005 File -> New Project -> Class Library.
![SQL Server]()
Step 2
We are going to use Newton Soft JSON DLL (No dependent DLL) to desterilize the JSON Data. Since SSRS supports .NET version below 3.5, we are going to use Newtonsoft.JSON.DLL version 2.0.
Download Newton soft JSON all version DLL -> Link.
The sample VB code to desterilize JSON data is given below.
- Imports Newtonsoft.Json.Linq
- Public Class Class1
- Public Shared Function Message(ByVal Jval As String, ByVal Val As String) As String
-
- Dim json As JObject = JObject.Parse(Jval)
- Return (json.SelectToken("Venue").SelectToken(Val).ToString)
-
- End Function
- End Class
![SQL Server]()
Step 3
We need to configure some properties before building DLL.
Project -> Properties -> select Signing tab -> Check Sign the Assembly -> Select <New from drop down.
![SQL Server]()
Step 4
Open AssemblyInfo.vb file and add <Assembly: AllowPartiallyTrustedCallers()> in the code, which allows the report Server to call this DLL.
Note
This code needs System.SecurityPackage.
![SQL Server]()
Step 5
Build the project. Right click on the Solution Explorer -> Build. Once build is successfully complete, DLL file is present in the Bin\Debug or Bin\Release.
![SQL Server]()
Step 5
Open SSDT tool and create sample SSRS Report. For demo, I created a sample report with two parameters JSON and Value.
Copy the DLL and paste it in the path given below.
C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\PrivateAssemblies
Note
The path gives abiis for SSDT 2016, Path may change based on the version of SSDT or BIDS.
By copying the DLL to the above folder SSRS Report Preview engine will refer to the DLL present in this path. SSRS Development Visual studio IDE supports all versions of .NET framework but SSRS report server supports only .NET Framework 2.0 and .NET Framework 3.5 (i.e.) developer can develop reports with .NET Version 4.0 or 4.5 but not able to deploy in report serverL.
Add DLL reference in SSRS Report by clicking Report -> Report Properties -> References.
![SQL Server]()
Step 6
Write the expression to the textbox following syntax: =AssemblyName.ClassName.FunctionName(arguments).
![SQL Server]()
Step 7
Before deploying the report into the Server, we have to do some tasks. Copy newly created DLL, Newton soft DLL and paste it in the path given below.
C:\Program Files\Microsoft SQL Server\MSRS13.SQLDEV\Reporting Services\ReportServer\bin
Note
The path given above may change based on the SQL Server version.
![SQL Server]()
Step 8
Install DLL in to GAC. Open Developer Command Prompt for Visual Studio as administrator.
Type the command: gacutil /i path_to_assembly_file /f
![SQL Server]()
GAC folder is given.
![SQL Server]()
Step 9
At the end, Restart the Report Server.
![SQL Server]()
Step 10
Upload the report to SSRS Report.
Here are the Report Previews.
Sample JSON
- {
- "Venue": {
- "ID": 3145,
- "Name": "Big Venue, Clapton",
- "NameWithTown": "Big Venue, Clapton, London",
- "NameWithDestination": "Big Venue, Clapton, London",
- "ListingType": "A",
- "ResponseStatus": {
- "ErrorCode": "200",
- "Message": "OK"
- }
- }
- }
![SQL Server]()
![SQL Server]()
Thank you.