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.

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.

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.

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.

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.

Step 6

Write the expression to the textbox following syntax: =AssemblyName.ClassName.FunctionName(arguments).

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

GAC folder is given.

Step 9

At the end, Restart the Report 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"
                }
        }
}

Thank you.