Display formatted JSON in SSRS report

Question

I have a table where one field is JSON string.

"CX.UW.001": "03/08/2017", "CX.UW.001.AUDIT": "admin",

I want to produce an SSRS report where it appears in readable format like:

CX.UW.001: 03/08/2017
CX.UW.001.AUDIT: admin 

Is it possible?

Answer 1

If you are looking for multiple records, just about any parse/split function will do, or you can use a simple CROSS APPLY in concert with a little XML

Declare @YourTable table (ID int, JSON varchar(max))
Insert Into @YourTable values
(1,'"CX.UW.001": "03/08/2017", "CX.UW.001.AUDIT": "admin"')

Select A.ID
      ,DisplayAs = replace(B.RetVal,'"','')
 From  @YourTable A
 Cross Apply (
                Select RetSeq = Row_Number() over (Order By (Select null))
                      ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                From  (Select x = Cast('<x>' + replace((Select replace(A.JSON,',','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as X
                Cross Apply x.nodes('x') AS B(i)
             ) B

Returns

ID  DisplayAs
1   CX.UW.001: 03/08/2017
1   CX.UW.001.AUDIT: admin

Or If you want the string to wrap

Select A.ID
      ,DisplayAs = replace(replace(JSON,',',char(13)),'"','')
 From  @YourTable A

Returns

1   CX.UW.001: 03/08/2017
    CX.UW.001.AUDIT: admin