Extract JSON Data Using Custom DLL In SSRS

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

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.

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

Thank you.

2 Comments

Join the conversation! Your thoughts help the community grow.

Former member9y ago
I have one question regarding SSRS and large data handling. i post it as question in this web site. here is the url http://www.c-sharpcorner.com/forums/how-ssrs-handle-large-data-when-pagination-is-configured please see my question and answer if you know it. thanks
Gowtham Rajamanickam9y ago
Good article.....keep writing....