🔥 Demo -> Json And [XML now too!] to SSRS Dataset (Updated!)

Experts' Corner
, , ,
1

Updated to add XML as well (below)

So I don’t usually take requests outside of the forum, but @kve asked me an interesting question offline.

He said he wanted to parse JSON in a string field inside an SSRS report as a table.

Well here is how it is done.

Well first I needed a BAQReport and a source of json.
So I made a BAQ, and added one calculated field Calculated_Json.
You could just add the json directly to that field, and the BAQ will give you one row of it, but i decided to make it a UBAQ, and added a post processing BPM on the GetList.

Post → Ice.[Company]/JsonSSRSTest.GetList->GrabABCAsJson

//using Newtonsoft.Json;

result.Results.Clear();

result.Results.Add(new ResultsUbaqRow()
{
    Calculated_Json = JsonConvert.SerializeObject(
          new
          {
              ABC = Db.ABCCode.ToList()            
          }).Replace(Session.CompanyID, "Woot!"),
          
    RowIdent = Guid.Empty.ToString()
});

{
    "ABC": [
        {
            "$id": "1",
            "Company": "000000",
            "ABCCode1": "A",
            "CountFreq": 30,
            "ExcludeFromCC": false,
            "StockValPcnt": 0,
            "PcntTolerance": 0,
            "CalcPcnt": false,
            "CalcQty": false,
            "CalcValue": false,
            "QtyTolerance": 0,
            "ValueTolerance": 0,
            "ShipToCustNum": 0,
            "SysRevID": "AAAAAAi6GeQ=",
            "SysRowID": "acbcb805-9d15-fca7-e311-33e0dbc8c227",
            "EntityKey": {
                "$id": "2",
                "EntitySetName": "ABCCode",
                "EntityContainerName": "ErpContext",
                "EntityKeyValues": [
                    {
                        "Key": "SysRowID",
                        "Value": "acbcb805-9d15-fca7-e311-33e0dbc8c227"
                    }
                ]
            }
        },
        {
            "$id": "3",
            "Company": "000000",
            "ABCCode1": "B",
            "CountFreq": 30,
            "ExcludeFromCC": false,
            "StockValPcnt": 0,
            "PcntTolerance": 0,
            "CalcPcnt": false,
            "CalcQty": false,
            "CalcValue": false,
            "QtyTolerance": 0,
            "ValueTolerance": 0,
            "ShipToCustNum": 0,
            "SysRevID": "AAAAAAAagVU=",
            "SysRowID": "acbcb805-9d15-fca7-e311-33e040fdc527",
            "EntityKey": {
                "$id": "4",
                "EntitySetName": "ABCCode",
                "EntityContainerName": "ErpContext",
                "EntityKeyValues": [
                    {
                        "Key": "SysRowID",
                        "Value": "acbcb805-9d15-fca7-e311-33e040fdc527"
                    }
                ]
            }
        },
        {
            "$id": "5",
            "Company": "000000",
            "ABCCode1": "C",
            "CountFreq": 60,
            "ExcludeFromCC": false,
            "StockValPcnt": 0,
            "PcntTolerance": 0,
            "CalcPcnt": false,
            "CalcQty": false,
            "CalcValue": false,
            "QtyTolerance": 0,
            "ValueTolerance": 0,
            "ShipToCustNum": 0,
            "SysRevID": "AAAAAAAagVY=",
            "SysRowID": "acbcb805-9d15-fca7-e311-33e05124c627",
            "EntityKey": {
                "$id": "6",
                "EntitySetName": "ABCCode",
                "EntityContainerName": "ErpContext",
                "EntityKeyValues": [
                    {
                        "Key": "SysRowID",
                        "Value": "acbcb805-9d15-fca7-e311-33e05124c627"
                    }
                ]
            }
        },
        {
            "$id": "7",
            "Company": "000000",
            "ABCCode1": "D",
            "CountFreq": 90,
            "ExcludeFromCC": false,
            "StockValPcnt": 0,
            "PcntTolerance": 0,
            "CalcPcnt": false,
            "CalcQty": false,
            "CalcValue": false,
            "QtyTolerance": 0,
            "ValueTolerance": 0,
            "ShipToCustNum": 0,
            "SysRevID": "AAAAAAAagVc=",
            "SysRowID": "acbcb805-9d15-fca7-e311-33e0624bc627",
            "EntityKey": {
                "$id": "8",
                "EntitySetName": "ABCCode",
                "EntityContainerName": "ErpContext",
                "EntityKeyValues": [
                    {
                        "Key": "SysRowID",
                        "Value": "acbcb805-9d15-fca7-e311-33e0624bc627"
                    }
                ]
            }
        },
        {
            "$id": "9",
            "Company": "Woot!",
            "ABCCode1": "A",
            "CountFreq": 3,
            "ExcludeFromCC": false,
            "StockValPcnt": 0,
            "PcntTolerance": 0,
            "CalcPcnt": false,
            "CalcQty": false,
            "CalcValue": false,
            "QtyTolerance": 0,
            "ValueTolerance": 0,
            "ShipToCustNum": 0,
            "SysRevID": "AAAAAAST/oU=",
            "SysRowID": "8429abd7-2649-4201-ac11-e2df2a9cd71a",
            "EntityKey": {
                "$id": "10",
                "EntitySetName": "ABCCode",
                "EntityContainerName": "ErpContext",
                "EntityKeyValues": [
                    {
                        "Key": "SysRowID",
                        "Value": "8429abd7-2649-4201-ac11-e2df2a9cd71a"
                    }
                ]
            }
        },
        {
            "$id": "11",
            "Company": "Woot!",
            "ABCCode1": "B",
            "CountFreq": 7,
            "ExcludeFromCC": false,
            "StockValPcnt": 0,
            "PcntTolerance": 0,
            "CalcPcnt": false,
            "CalcQty": false,
            "CalcValue": false,
            "QtyTolerance": 0,
            "ValueTolerance": 0,
            "ShipToCustNum": 0,
            "SysRevID": "AAAAAAST/oY=",
            "SysRowID": "d1840ac1-789e-406a-af2f-9bbbc1e77f70",
            "EntityKey": {
                "$id": "12",
                "EntitySetName": "ABCCode",
                "EntityContainerName": "ErpContext",
                "EntityKeyValues": [
                    {
                        "Key": "SysRowID",
                        "Value": "d1840ac1-789e-406a-af2f-9bbbc1e77f70"
                    }
                ]
            }
        },
        {
            "$id": "13",
            "Company": "Woot!",
            "ABCCode1": "C",
            "CountFreq": 14,
            "ExcludeFromCC": false,
            "StockValPcnt": 0,
            "PcntTolerance": 0,
            "CalcPcnt": false,
            "CalcQty": false,
            "CalcValue": false,
            "QtyTolerance": 0,
            "ValueTolerance": 0,
            "ShipToCustNum": 0,
            "SysRevID": "AAAAAAST/oc=",
            "SysRowID": "1abd15cd-61d5-4a32-ada8-4869eb7997fd",
            "EntityKey": {
                "$id": "14",
                "EntitySetName": "ABCCode",
                "EntityContainerName": "ErpContext",
                "EntityKeyValues": [
                    {
                        "Key": "SysRowID",
                        "Value": "1abd15cd-61d5-4a32-ada8-4869eb7997fd"
                    }
                ]
            }
        },
        {
            "$id": "15",
            "Company": "Woot!",
            "ABCCode1": "D",
            "CountFreq": 30,
            "ExcludeFromCC": false,
            "StockValPcnt": 0,
            "PcntTolerance": 0,
            "CalcPcnt": false,
            "CalcQty": false,
            "CalcValue": false,
            "QtyTolerance": 0,
            "ValueTolerance": 0,
            "ShipToCustNum": 0,
            "SysRevID": "AAAAAAST/og=",
            "SysRowID": "ee543161-d93a-4129-9266-882da9ee105f",
            "EntityKey": {
                "$id": "16",
                "EntitySetName": "ABCCode",
                "EntityContainerName": "ErpContext",
                "EntityKeyValues": [
                    {
                        "Key": "SysRowID",
                        "Value": "ee543161-d93a-4129-9266-882da9ee105f"
                    }
                ]
            }
        },
        {
            "$id": "17",
            "Company": "Woot!",
            "ABCCode1": "E",
            "CountFreq": 45,
            "ExcludeFromCC": false,
            "StockValPcnt": 0,
            "PcntTolerance": 0,
            "CalcPcnt": false,
            "CalcQty": false,
            "CalcValue": false,
            "QtyTolerance": 0,
            "ValueTolerance": 0,
            "ShipToCustNum": 0,
            "SysRevID": "AAAAAAST/ok=",
            "SysRowID": "542e8e01-4449-42af-85a7-3e0c8913f133",
            "EntityKey": {
                "$id": "18",
                "EntitySetName": "ABCCode",
                "EntityContainerName": "ErpContext",
                "EntityKeyValues": [
                    {
                        "Key": "SysRowID",
                        "Value": "542e8e01-4449-42af-85a7-3e0c8913f133"
                    }
                ]
            }
        },
        {
            "$id": "19",
            "Company": "Woot!",
            "ABCCode1": "F",
            "CountFreq": 60,
            "ExcludeFromCC": false,
            "StockValPcnt": 0,
            "PcntTolerance": 0,
            "CalcPcnt": false,
            "CalcQty": false,
            "CalcValue": false,
            "QtyTolerance": 0,
            "ValueTolerance": 0,
            "ShipToCustNum": 0,
            "SysRevID": "AAAAAAST/oo=",
            "SysRowID": "ac4f5cb9-1f59-4611-8eb4-1ee30c3b713f",
            "EntityKey": {
                "$id": "20",
                "EntitySetName": "ABCCode",
                "EntityContainerName": "ErpContext",
                "EntityKeyValues": [
                    {
                        "Key": "SysRowID",
                        "Value": "ac4f5cb9-1f59-4611-8eb4-1ee30c3b713f"
                    }
                ]
            }
        },
        {
            "$id": "21",
            "Company": "Woot!",
            "ABCCode1": "G",
            "CountFreq": 90,
            "ExcludeFromCC": false,
            "StockValPcnt": 0,
            "PcntTolerance": 0,
            "CalcPcnt": false,
            "CalcQty": false,
            "CalcValue": false,
            "QtyTolerance": 0,
            "ValueTolerance": 0,
            "ShipToCustNum": 0,
            "SysRevID": "AAAAAAST/os=",
            "SysRowID": "1944b5e1-a85d-4220-9173-ce2c77f74d1c",
            "EntityKey": {
                "$id": "22",
                "EntitySetName": "ABCCode",
                "EntityContainerName": "ErpContext",
                "EntityKeyValues": [
                    {
                        "Key": "SysRowID",
                        "Value": "1944b5e1-a85d-4220-9173-ce2c77f74d1c"
                    }
                ]
            }
        }
    ]
}

Then I made a simple BAQReport, and generated it for design.

In the designer, I added a new dataset, and chose “Use dataset embedded in my report.”

Edited the query for the DataSet: (only choosing top row and field)

="DECLARE @json NVARCHAR(MAX);

SELECT TOP 1 @json = [Calculated_Json] FROM dbo.[BAQReportResult_" + Parameters!TableGuid.Value + "];

SELECT
    jsonParsed.[Company],
    jsonParsed.[ABCCode1],
    jsonParsed.[CountFreq],
    jsonParsed.[ExcludeFromCC],
    jsonParsed.[StockValPcnt],
    jsonParsed.[PcntTolerance],
    jsonParsed.[CalcPcnt],
    jsonParsed.[CalcQty],
    jsonParsed.[CalcValue],
    jsonParsed.[QtyTolerance],
    jsonParsed.[ValueTolerance]
FROM OPENJSON(@json, '$.ABC') 
WITH (
    [Company] NVARCHAR(MAX),
    [ABCCode1] NVARCHAR(MAX),
    [CountFreq] INT,
    [ExcludeFromCC] BIT,
    [StockValPcnt] FLOAT,
    [PcntTolerance] FLOAT,
    [CalcPcnt] BIT,
    [CalcQty] BIT,
    [CalcValue] BIT,
    [QtyTolerance] FLOAT,
    [ValueTolerance] FLOAT
) AS jsonParsed;"

Added the fields:

image
image650Ă—671 10.8 KB

And then proceeded to make the report:

image
image1008Ă—309 6.29 KB

And ran the report:

image
image987Ă—421 41 KB

Thus endeth the demo.

19 Likes
2

BAQ, XML, RDL → JsonSSRSTest.zip (25.2 KB)

3 Likes
3

This is so cool! :exploding_head:

2 Likes
4

I suppose you can pass in json to a BAQ parameter which you then put in your calculated field? I.e. from a screen you could send json to a BAQ report? Not sure what the use case is yet, as most times you’d want to report from data that’s in the database not on a screen, potentially uncommitted, but there’s some cases where we print labels that aren’t data driven and are hand typed or whatever so I guess that’s a use case.

Pretty cool y’all.

2 Likes
5

I believe @kve’s use case was he had some configurator inputs serialized to a ud field on a record, and some other guy wanted to see it as a table.

I can think of a few others now that I know we can do it.

3 Likes
6

I am today’s years-old when I learned that one can put multiple statements in an SSRS DataSet expression. :exploding_head:

9 Likes
7

Haha. I feel this way about a lot of what I see posted. I’m always wowed at what all of you guys can DO… but then I sit there and think, “but why do you need to do this?”

I’m not criticizing in the slightest. I’m just always appreciative when posts explain the problem they were trying to overcome. Then, in my head, I can think… “oh yeah, maybe I could use that approach over here!” Hearing what the use cases are and the current limitations that drive these creative solutions is good information, when its provided.

1 Like
8

Combine these two quotes and I just thought of something.

Querying json data against db data using SQL can be done.

3 Likes
9

Also, now we have an easy way to bring in multiple datasets to one report.

2 Likes
10

AND you could probably do XML while you’re at it. That opens up some options…

3 Likes
11

Man, it’s a really great thing, I do it all the time.

12

Yes

13

I’ve never tried since I’ve been denied on more advanced SELECT statements and thought, “Well, crap, If I can’t do a GROUP BY…”

2 Likes
14

Well I will say it wasn’t for quite some time I even tried it, but eventually I was like, this is SQL… I’m gonna try XYZ. And it worked hahah

3 Likes
15

But yeah the syntax in the built in SSRS reports with the quotes, etc… that’s cumbersome.

4 Likes
16

:fire: Multiple DataSets → JSON & XML in same Report!!! :fire:

Angry Season 3 GIF by The Office

I made a BAQ, with two calculated fields Calculated_Json, and Calculated_XML.

Here is how it is generated, using a UBAQ with a customization on GetList:

Post → Ice.[Company]/AlternativeDSTests.GetList->GrabABCAsJsonAndXML

//using Newtonsoft.Json;
//using System.Xml.Linq;

//Recursive Func<T>...
Func<object, string, XElement> JsonToXml = null;
JsonToXml = (json, rootName) =>
{
    var jObject = json as JObject;
    if (jObject != null)
    {
        XElement element = new XElement(rootName);

        foreach (var property in jObject.Properties())
        {
            element.Add(JsonToXml(property.Value, property.Name));
        }

        return element;
    }

    var jArray = json as JArray;
    if (jArray != null)
    {
        XElement element = new XElement(rootName);

        foreach (var item in jArray)
        {
            element.Add(JsonToXml(item, "Item"));
        }

        return element;
    }

    return new XElement(rootName, json.ToString());
};

result.Results.Clear();

var someData = Db.ABCCode.ToList();

string jsonData = JsonConvert.SerializeObject(
          new
          {
              ABC = someData            
          }).Replace("$", String.Empty).Replace(Session.CompanyID, "Woot!");


var jsonObject = JsonConvert.DeserializeObject(jsonData);

string xmlData = JsonToXml(jsonObject, "Root").ToString();

result.Results.Add(new ResultsUbaqRow()
{
    Calculated_Json = jsonData,
          
    Calculated_XML = xmlData,
   
    RowIdent = Guid.Empty.ToString()
});
Json Sample 
{
    "ABC": [
        {
            "$id": "1",
            "Company": "000000",
            "ABCCode1": "A",
            "CountFreq": 30,
            "ExcludeFromCC": false,
            "StockValPcnt": 0,
            "PcntTolerance": 0,
            "CalcPcnt": false,
            "CalcQty": false,
            "CalcValue": false,
            "QtyTolerance": 0,
            "ValueTolerance": 0,
            "ShipToCustNum": 0,
            "SysRevID": "AAAAAAi6GeQ=",
            "SysRowID": "acbcb805-9d15-fca7-e311-33e0dbc8c227",
            "EntityKey": {
                "$id": "2",
                "EntitySetName": "ABCCode",
                "EntityContainerName": "ErpContext",
                "EntityKeyValues": [
                    {
                        "Key": "SysRowID",
                        "Value": "acbcb805-9d15-fca7-e311-33e0dbc8c227"
                    }
                ]
            }
        },
        {
            "$id": "3",
            "Company": "000000",
            "ABCCode1": "B",
            "CountFreq": 30,
            "ExcludeFromCC": false,
            "StockValPcnt": 0,
            "PcntTolerance": 0,
            "CalcPcnt": false,
            "CalcQty": false,
            "CalcValue": false,
            "QtyTolerance": 0,
            "ValueTolerance": 0,
            "ShipToCustNum": 0,
            "SysRevID": "AAAAAAAagVU=",
            "SysRowID": "acbcb805-9d15-fca7-e311-33e040fdc527",
            "EntityKey": {
                "$id": "4",
                "EntitySetName": "ABCCode",
                "EntityContainerName": "ErpContext",
                "EntityKeyValues": [
                    {
                        "Key": "SysRowID",
                        "Value": "acbcb805-9d15-fca7-e311-33e040fdc527"
                    }
                ]
            }
        },
        {
            "$id": "5",
            "Company": "000000",
            "ABCCode1": "C",
            "CountFreq": 60,
            "ExcludeFromCC": false,
            "StockValPcnt": 0,
            "PcntTolerance": 0,
            "CalcPcnt": false,
            "CalcQty": false,
            "CalcValue": false,
            "QtyTolerance": 0,
            "ValueTolerance": 0,
            "ShipToCustNum": 0,
            "SysRevID": "AAAAAAAagVY=",
            "SysRowID": "acbcb805-9d15-fca7-e311-33e05124c627",
            "EntityKey": {
                "$id": "6",
                "EntitySetName": "ABCCode",
                "EntityContainerName": "ErpContext",
                "EntityKeyValues": [
                    {
                        "Key": "SysRowID",
                        "Value": "acbcb805-9d15-fca7-e311-33e05124c627"
                    }
                ]
            }
        },
        {
            "$id": "7",
            "Company": "000000",
            "ABCCode1": "D",
            "CountFreq": 90,
            "ExcludeFromCC": false,
            "StockValPcnt": 0,
            "PcntTolerance": 0,
            "CalcPcnt": false,
            "CalcQty": false,
            "CalcValue": false,
            "QtyTolerance": 0,
            "ValueTolerance": 0,
            "ShipToCustNum": 0,
            "SysRevID": "AAAAAAAagVc=",
            "SysRowID": "acbcb805-9d15-fca7-e311-33e0624bc627",
            "EntityKey": {
                "$id": "8",
                "EntitySetName": "ABCCode",
                "EntityContainerName": "ErpContext",
                "EntityKeyValues": [
                    {
                        "Key": "SysRowID",
                        "Value": "acbcb805-9d15-fca7-e311-33e0624bc627"
                    }
                ]
            }
        },
        {
            "$id": "9",
            "Company": "Woot!",
            "ABCCode1": "A",
            "CountFreq": 3,
            "ExcludeFromCC": false,
            "StockValPcnt": 0,
            "PcntTolerance": 0,
            "CalcPcnt": false,
            "CalcQty": false,
            "CalcValue": false,
            "QtyTolerance": 0,
            "ValueTolerance": 0,
            "ShipToCustNum": 0,
            "SysRevID": "AAAAAAST/oU=",
            "SysRowID": "8429abd7-2649-4201-ac11-e2df2a9cd71a",
            "EntityKey": {
                "$id": "10",
                "EntitySetName": "ABCCode",
                "EntityContainerName": "ErpContext",
                "EntityKeyValues": [
                    {
                        "Key": "SysRowID",
                        "Value": "8429abd7-2649-4201-ac11-e2df2a9cd71a"
                    }
                ]
            }
        },
        {
            "$id": "11",
            "Company": "Woot!",
            "ABCCode1": "B",
            "CountFreq": 7,
            "ExcludeFromCC": false,
            "StockValPcnt": 0,
            "PcntTolerance": 0,
            "CalcPcnt": false,
            "CalcQty": false,
            "CalcValue": false,
            "QtyTolerance": 0,
            "ValueTolerance": 0,
            "ShipToCustNum": 0,
            "SysRevID": "AAAAAAST/oY=",
            "SysRowID": "d1840ac1-789e-406a-af2f-9bbbc1e77f70",
            "EntityKey": {
                "$id": "12",
                "EntitySetName": "ABCCode",
                "EntityContainerName": "ErpContext",
                "EntityKeyValues": [
                    {
                        "Key": "SysRowID",
                        "Value": "d1840ac1-789e-406a-af2f-9bbbc1e77f70"
                    }
                ]
            }
        },
        {
            "$id": "13",
            "Company": "Woot!",
            "ABCCode1": "C",
            "CountFreq": 14,
            "ExcludeFromCC": false,
            "StockValPcnt": 0,
            "PcntTolerance": 0,
            "CalcPcnt": false,
            "CalcQty": false,
            "CalcValue": false,
            "QtyTolerance": 0,
            "ValueTolerance": 0,
            "ShipToCustNum": 0,
            "SysRevID": "AAAAAAST/oc=",
            "SysRowID": "1abd15cd-61d5-4a32-ada8-4869eb7997fd",
            "EntityKey": {
                "$id": "14",
                "EntitySetName": "ABCCode",
                "EntityContainerName": "ErpContext",
                "EntityKeyValues": [
                    {
                        "Key": "SysRowID",
                        "Value": "1abd15cd-61d5-4a32-ada8-4869eb7997fd"
                    }
                ]
            }
        },
        {
            "$id": "15",
            "Company": "Woot!",
            "ABCCode1": "D",
            "CountFreq": 30,
            "ExcludeFromCC": false,
            "StockValPcnt": 0,
            "PcntTolerance": 0,
            "CalcPcnt": false,
            "CalcQty": false,
            "CalcValue": false,
            "QtyTolerance": 0,
            "ValueTolerance": 0,
            "ShipToCustNum": 0,
            "SysRevID": "AAAAAAST/og=",
            "SysRowID": "ee543161-d93a-4129-9266-882da9ee105f",
            "EntityKey": {
                "$id": "16",
                "EntitySetName": "ABCCode",
                "EntityContainerName": "ErpContext",
                "EntityKeyValues": [
                    {
                        "Key": "SysRowID",
                        "Value": "ee543161-d93a-4129-9266-882da9ee105f"
                    }
                ]
            }
        },
        {
            "$id": "17",
            "Company": "Woot!",
            "ABCCode1": "E",
            "CountFreq": 45,
            "ExcludeFromCC": false,
            "StockValPcnt": 0,
            "PcntTolerance": 0,
            "CalcPcnt": false,
            "CalcQty": false,
            "CalcValue": false,
            "QtyTolerance": 0,
            "ValueTolerance": 0,
            "ShipToCustNum": 0,
            "SysRevID": "AAAAAAST/ok=",
            "SysRowID": "542e8e01-4449-42af-85a7-3e0c8913f133",
            "EntityKey": {
                "$id": "18",
                "EntitySetName": "ABCCode",
                "EntityContainerName": "ErpContext",
                "EntityKeyValues": [
                    {
                        "Key": "SysRowID",
                        "Value": "542e8e01-4449-42af-85a7-3e0c8913f133"
                    }
                ]
            }
        },
        {
            "$id": "19",
            "Company": "Woot!",
            "ABCCode1": "F",
            "CountFreq": 60,
            "ExcludeFromCC": false,
            "StockValPcnt": 0,
            "PcntTolerance": 0,
            "CalcPcnt": false,
            "CalcQty": false,
            "CalcValue": false,
            "QtyTolerance": 0,
            "ValueTolerance": 0,
            "ShipToCustNum": 0,
            "SysRevID": "AAAAAAST/oo=",
            "SysRowID": "ac4f5cb9-1f59-4611-8eb4-1ee30c3b713f",
            "EntityKey": {
                "$id": "20",
                "EntitySetName": "ABCCode",
                "EntityContainerName": "ErpContext",
                "EntityKeyValues": [
                    {
                        "Key": "SysRowID",
                        "Value": "ac4f5cb9-1f59-4611-8eb4-1ee30c3b713f"
                    }
                ]
            }
        },
        {
            "$id": "21",
            "Company": "Woot!",
            "ABCCode1": "G",
            "CountFreq": 90,
            "ExcludeFromCC": false,
            "StockValPcnt": 0,
            "PcntTolerance": 0,
            "CalcPcnt": false,
            "CalcQty": false,
            "CalcValue": false,
            "QtyTolerance": 0,
            "ValueTolerance": 0,
            "ShipToCustNum": 0,
            "SysRevID": "AAAAAAST/os=",
            "SysRowID": "1944b5e1-a85d-4220-9173-ce2c77f74d1c",
            "EntityKey": {
                "$id": "22",
                "EntitySetName": "ABCCode",
                "EntityContainerName": "ErpContext",
                "EntityKeyValues": [
                    {
                        "Key": "SysRowID",
                        "Value": "1944b5e1-a85d-4220-9173-ce2c77f74d1c"
                    }
                ]
            }
        }
    ]
}
XML Sample 
<Root>
  <ABC>
    <Item>
      <id>1</id>
      <Company>000000</Company>
      <ABCCode1>A</ABCCode1>
      <CountFreq>30</CountFreq>
      <ExcludeFromCC>False</ExcludeFromCC>
      <StockValPcnt>0</StockValPcnt>
      <PcntTolerance>0</PcntTolerance>
      <CalcPcnt>False</CalcPcnt>
      <CalcQty>False</CalcQty>
      <CalcValue>False</CalcValue>
      <QtyTolerance>0</QtyTolerance>
      <ValueTolerance>0</ValueTolerance>
      <ShipToCustNum>0</ShipToCustNum>
      <SysRevID>AAAAAAi6GeQ=</SysRevID>
      <SysRowID>acbcb805-9d15-fca7-e311-33e0dbc8c227</SysRowID>
      <EntityKey>
        <id>2</id>
        <EntitySetName>ABCCode</EntitySetName>
        <EntityContainerName>ErpContext</EntityContainerName>
        <EntityKeyValues>
          <Item>
            <Key>SysRowID</Key>
            <Value>acbcb805-9d15-fca7-e311-33e0dbc8c227</Value>
          </Item>
        </EntityKeyValues>
      </EntityKey>
    </Item>
    <Item>
      <id>3</id>
      <Company>000000</Company>
      <ABCCode1>B</ABCCode1>
      <CountFreq>30</CountFreq>
      <ExcludeFromCC>False</ExcludeFromCC>
      <StockValPcnt>0</StockValPcnt>
      <PcntTolerance>0</PcntTolerance>
      <CalcPcnt>False</CalcPcnt>
      <CalcQty>False</CalcQty>
      <CalcValue>False</CalcValue>
      <QtyTolerance>0</QtyTolerance>
      <ValueTolerance>0</ValueTolerance>
      <ShipToCustNum>0</ShipToCustNum>
      <SysRevID>AAAAAAAagVU=</SysRevID>
      <SysRowID>acbcb805-9d15-fca7-e311-33e040fdc527</SysRowID>
      <EntityKey>
        <id>4</id>
        <EntitySetName>ABCCode</EntitySetName>
        <EntityContainerName>ErpContext</EntityContainerName>
        <EntityKeyValues>
          <Item>
            <Key>SysRowID</Key>
            <Value>acbcb805-9d15-fca7-e311-33e040fdc527</Value>
          </Item>
        </EntityKeyValues>
      </EntityKey>
    </Item>
    <Item>
      <id>5</id>
      <Company>000000</Company>
      <ABCCode1>C</ABCCode1>
      <CountFreq>60</CountFreq>
      <ExcludeFromCC>False</ExcludeFromCC>
      <StockValPcnt>0</StockValPcnt>
      <PcntTolerance>0</PcntTolerance>
      <CalcPcnt>False</CalcPcnt>
      <CalcQty>False</CalcQty>
      <CalcValue>False</CalcValue>
      <QtyTolerance>0</QtyTolerance>
      <ValueTolerance>0</ValueTolerance>
      <ShipToCustNum>0</ShipToCustNum>
      <SysRevID>AAAAAAAagVY=</SysRevID>
      <SysRowID>acbcb805-9d15-fca7-e311-33e05124c627</SysRowID>
      <EntityKey>
        <id>6</id>
        <EntitySetName>ABCCode</EntitySetName>
        <EntityContainerName>ErpContext</EntityContainerName>
        <EntityKeyValues>
          <Item>
            <Key>SysRowID</Key>
            <Value>acbcb805-9d15-fca7-e311-33e05124c627</Value>
          </Item>
        </EntityKeyValues>
      </EntityKey>
    </Item>
    <Item>
      <id>7</id>
      <Company>000000</Company>
      <ABCCode1>D</ABCCode1>
      <CountFreq>90</CountFreq>
      <ExcludeFromCC>False</ExcludeFromCC>
      <StockValPcnt>0</StockValPcnt>
      <PcntTolerance>0</PcntTolerance>
      <CalcPcnt>False</CalcPcnt>
      <CalcQty>False</CalcQty>
      <CalcValue>False</CalcValue>
      <QtyTolerance>0</QtyTolerance>
      <ValueTolerance>0</ValueTolerance>
      <ShipToCustNum>0</ShipToCustNum>
      <SysRevID>AAAAAAAagVc=</SysRevID>
      <SysRowID>acbcb805-9d15-fca7-e311-33e0624bc627</SysRowID>
      <EntityKey>
        <id>8</id>
        <EntitySetName>ABCCode</EntitySetName>
        <EntityContainerName>ErpContext</EntityContainerName>
        <EntityKeyValues>
          <Item>
            <Key>SysRowID</Key>
            <Value>acbcb805-9d15-fca7-e311-33e0624bc627</Value>
          </Item>
        </EntityKeyValues>
      </EntityKey>
    </Item>
    <Item>
      <id>9</id>
      <Company>Woot!</Company>
      <ABCCode1>A</ABCCode1>
      <CountFreq>3</CountFreq>
      <ExcludeFromCC>False</ExcludeFromCC>
      <StockValPcnt>0</StockValPcnt>
      <PcntTolerance>0</PcntTolerance>
      <CalcPcnt>False</CalcPcnt>
      <CalcQty>False</CalcQty>
      <CalcValue>False</CalcValue>
      <QtyTolerance>0</QtyTolerance>
      <ValueTolerance>0</ValueTolerance>
      <ShipToCustNum>0</ShipToCustNum>
      <SysRevID>AAAAAAST/oU=</SysRevID>
      <SysRowID>8429abd7-2649-4201-ac11-e2df2a9cd71a</SysRowID>
      <EntityKey>
        <id>10</id>
        <EntitySetName>ABCCode</EntitySetName>
        <EntityContainerName>ErpContext</EntityContainerName>
        <EntityKeyValues>
          <Item>
            <Key>SysRowID</Key>
            <Value>8429abd7-2649-4201-ac11-e2df2a9cd71a</Value>
          </Item>
        </EntityKeyValues>
      </EntityKey>
    </Item>
    <Item>
      <id>11</id>
      <Company>Woot!</Company>
      <ABCCode1>B</ABCCode1>
      <CountFreq>7</CountFreq>
      <ExcludeFromCC>False</ExcludeFromCC>
      <StockValPcnt>0</StockValPcnt>
      <PcntTolerance>0</PcntTolerance>
      <CalcPcnt>False</CalcPcnt>
      <CalcQty>False</CalcQty>
      <CalcValue>False</CalcValue>
      <QtyTolerance>0</QtyTolerance>
      <ValueTolerance>0</ValueTolerance>
      <ShipToCustNum>0</ShipToCustNum>
      <SysRevID>AAAAAAST/oY=</SysRevID>
      <SysRowID>d1840ac1-789e-406a-af2f-9bbbc1e77f70</SysRowID>
      <EntityKey>
        <id>12</id>
        <EntitySetName>ABCCode</EntitySetName>
        <EntityContainerName>ErpContext</EntityContainerName>
        <EntityKeyValues>
          <Item>
            <Key>SysRowID</Key>
            <Value>d1840ac1-789e-406a-af2f-9bbbc1e77f70</Value>
          </Item>
        </EntityKeyValues>
      </EntityKey>
    </Item>
    <Item>
      <id>13</id>
      <Company>Woot!</Company>
      <ABCCode1>C</ABCCode1>
      <CountFreq>14</CountFreq>
      <ExcludeFromCC>False</ExcludeFromCC>
      <StockValPcnt>0</StockValPcnt>
      <PcntTolerance>0</PcntTolerance>
      <CalcPcnt>False</CalcPcnt>
      <CalcQty>False</CalcQty>
      <CalcValue>False</CalcValue>
      <QtyTolerance>0</QtyTolerance>
      <ValueTolerance>0</ValueTolerance>
      <ShipToCustNum>0</ShipToCustNum>
      <SysRevID>AAAAAAST/oc=</SysRevID>
      <SysRowID>1abd15cd-61d5-4a32-ada8-4869eb7997fd</SysRowID>
      <EntityKey>
        <id>14</id>
        <EntitySetName>ABCCode</EntitySetName>
        <EntityContainerName>ErpContext</EntityContainerName>
        <EntityKeyValues>
          <Item>
            <Key>SysRowID</Key>
            <Value>1abd15cd-61d5-4a32-ada8-4869eb7997fd</Value>
          </Item>
        </EntityKeyValues>
      </EntityKey>
    </Item>
    <Item>
      <id>15</id>
      <Company>Woot!</Company>
      <ABCCode1>D</ABCCode1>
      <CountFreq>30</CountFreq>
      <ExcludeFromCC>False</ExcludeFromCC>
      <StockValPcnt>0</StockValPcnt>
      <PcntTolerance>0</PcntTolerance>
      <CalcPcnt>False</CalcPcnt>
      <CalcQty>False</CalcQty>
      <CalcValue>False</CalcValue>
      <QtyTolerance>0</QtyTolerance>
      <ValueTolerance>0</ValueTolerance>
      <ShipToCustNum>0</ShipToCustNum>
      <SysRevID>AAAAAAST/og=</SysRevID>
      <SysRowID>ee543161-d93a-4129-9266-882da9ee105f</SysRowID>
      <EntityKey>
        <id>16</id>
        <EntitySetName>ABCCode</EntitySetName>
        <EntityContainerName>ErpContext</EntityContainerName>
        <EntityKeyValues>
          <Item>
            <Key>SysRowID</Key>
            <Value>ee543161-d93a-4129-9266-882da9ee105f</Value>
          </Item>
        </EntityKeyValues>
      </EntityKey>
    </Item>
    <Item>
      <id>17</id>
      <Company>Woot!</Company>
      <ABCCode1>E</ABCCode1>
      <CountFreq>45</CountFreq>
      <ExcludeFromCC>False</ExcludeFromCC>
      <StockValPcnt>0</StockValPcnt>
      <PcntTolerance>0</PcntTolerance>
      <CalcPcnt>False</CalcPcnt>
      <CalcQty>False</CalcQty>
      <CalcValue>False</CalcValue>
      <QtyTolerance>0</QtyTolerance>
      <ValueTolerance>0</ValueTolerance>
      <ShipToCustNum>0</ShipToCustNum>
      <SysRevID>AAAAAAST/ok=</SysRevID>
      <SysRowID>542e8e01-4449-42af-85a7-3e0c8913f133</SysRowID>
      <EntityKey>
        <id>18</id>
        <EntitySetName>ABCCode</EntitySetName>
        <EntityContainerName>ErpContext</EntityContainerName>
        <EntityKeyValues>
          <Item>
            <Key>SysRowID</Key>
            <Value>542e8e01-4449-42af-85a7-3e0c8913f133</Value>
          </Item>
        </EntityKeyValues>
      </EntityKey>
    </Item>
    <Item>
      <id>19</id>
      <Company>Woot!</Company>
      <ABCCode1>F</ABCCode1>
      <CountFreq>60</CountFreq>
      <ExcludeFromCC>False</ExcludeFromCC>
      <StockValPcnt>0</StockValPcnt>
      <PcntTolerance>0</PcntTolerance>
      <CalcPcnt>False</CalcPcnt>
      <CalcQty>False</CalcQty>
      <CalcValue>False</CalcValue>
      <QtyTolerance>0</QtyTolerance>
      <ValueTolerance>0</ValueTolerance>
      <ShipToCustNum>0</ShipToCustNum>
      <SysRevID>AAAAAAST/oo=</SysRevID>
      <SysRowID>ac4f5cb9-1f59-4611-8eb4-1ee30c3b713f</SysRowID>
      <EntityKey>
        <id>20</id>
        <EntitySetName>ABCCode</EntitySetName>
        <EntityContainerName>ErpContext</EntityContainerName>
        <EntityKeyValues>
          <Item>
            <Key>SysRowID</Key>
            <Value>ac4f5cb9-1f59-4611-8eb4-1ee30c3b713f</Value>
          </Item>
        </EntityKeyValues>
      </EntityKey>
    </Item>
    <Item>
      <id>21</id>
      <Company>Woot!</Company>
      <ABCCode1>G</ABCCode1>
      <CountFreq>90</CountFreq>
      <ExcludeFromCC>False</ExcludeFromCC>
      <StockValPcnt>0</StockValPcnt>
      <PcntTolerance>0</PcntTolerance>
      <CalcPcnt>False</CalcPcnt>
      <CalcQty>False</CalcQty>
      <CalcValue>False</CalcValue>
      <QtyTolerance>0</QtyTolerance>
      <ValueTolerance>0</ValueTolerance>
      <ShipToCustNum>0</ShipToCustNum>
      <SysRevID>AAAAAAST/os=</SysRevID>
      <SysRowID>1944b5e1-a85d-4220-9173-ce2c77f74d1c</SysRowID>
      <EntityKey>
        <id>22</id>
        <EntitySetName>ABCCode</EntitySetName>
        <EntityContainerName>ErpContext</EntityContainerName>
        <EntityKeyValues>
          <Item>
            <Key>SysRowID</Key>
            <Value>1944b5e1-a85d-4220-9173-ce2c77f74d1c</Value>
          </Item>
        </EntityKeyValues>
      </EntityKey>
    </Item>
  </ABC>
</Root>

Simple BAQReport, Generated it for design.

In the designer, I added two datasets, and chose “Use dataset embedded in my report.”

Query for DataSetJSON: (only choosing top row and field)

="DECLARE @json NVARCHAR(MAX);

SELECT TOP 1 @json = [Calculated_Json] FROM dbo.[BAQReportResult_" + Parameters!TableGuid.Value + "];

SELECT
    jsonParsed.[Company],
    jsonParsed.[ABCCode1],
    jsonParsed.[CountFreq],
    jsonParsed.[ExcludeFromCC],
    jsonParsed.[StockValPcnt],
    jsonParsed.[PcntTolerance],
    jsonParsed.[CalcPcnt],
    jsonParsed.[CalcQty],
    jsonParsed.[CalcValue],
    jsonParsed.[QtyTolerance],
    jsonParsed.[ValueTolerance]
FROM OPENJSON(@json, '$.ABC') 
WITH (
    [Company] NVARCHAR(MAX),
    [ABCCode1] NVARCHAR(MAX),
    [CountFreq] INT,
    [ExcludeFromCC] BIT,
    [StockValPcnt] FLOAT,
    [PcntTolerance] FLOAT,
    [CalcPcnt] BIT,
    [CalcQty] BIT,
    [CalcValue] BIT,
    [QtyTolerance] FLOAT,
    [ValueTolerance] FLOAT
) AS jsonParsed;"

Query for DataSetXML: (only choosing top row and field)

="
DECLARE @xml NVARCHAR(MAX);

SELECT TOP 1 @xml = [Calculated_XML] FROM dbo.[BAQReportResult_" + Parameters!TableGuid.Value + "];

DECLARE @xmlData XML;

SET @xmlData = CAST(@xml AS XML);

SELECT 
    x.value('(Company)[1]', 'VARCHAR(100)') AS [Company],
    x.value('(ABCCode1)[1]', 'VARCHAR(10)') AS [ABCCode1],
    x.value('(CountFreq)[1]', 'INT') AS [CountFreq],
    x.value('(ExcludeFromCC)[1]', 'BIT') AS [ExcludeFromCC],
    x.value('(StockValPcnt)[1]', 'DECIMAL(10, 2)') AS [StockValPcnt],
    x.value('(PcntTolerance)[1]', 'DECIMAL(10, 2)') AS [PcntTolerance],
    x.value('(CalcPcnt)[1]', 'BIT') AS [CalcPcnt],
    x.value('(CalcQty)[1]', 'BIT') AS [CalcQty],
    x.value('(CalcValue)[1]', 'BIT') AS [CalcValue],
    x.value('(QtyTolerance)[1]', 'DECIMAL(10, 2)') AS [QtyTolerance],
    x.value('(ValueTolerance)[1]', 'DECIMAL(10, 2)') AS [ValueTolerance]
FROM 
    @xmlData.nodes('/Root/ABC/Item') AS temp(x);
"

Added the fields to both DataSets:

image
image650Ă—671 10.8 KB

The report:

image
image889Ă—195 5.04 KB

And ran the report:

image
image1249Ă—999 105 KB

Files → AlternativeDSTests.zip (26.8 KB)

Thus endeth the second demo.

thousands GIF

8 Likes
17

Was going to post into Experts Corner but can’t make post there yet :slight_smile:

Dataset: expression to connect to stored procedure and pass in parameters.

="
DECLARE @Month  int;
DECLARE @Year   int;
DECLARE @Format int;
DECLARE @SQL    nvarchar(4000);

SET @Month  = " & CStr(Parameters!iMonth.Value)  & ";
SET @Year   = " & CStr(Parameters!iYear.Value)   & ";
SET @Format = " & CStr(Parameters!iFormat.Value) & ";

SET @SQL = 'EXEC [dbo].[sp_TrailerRegistrations] @Month = @Month, @Year = @Year, @Format = @Format';

EXECUTE sp_executesql @SQL , N'@Month int, @Year int, @Format int ', @Month , @Year , @Format
"

image
image877Ă—922 16.8 KB

1 Like
18

Couldn’t help myself lol

="
DECLARE @Month  int; 
DECLARE @Year   int;
DECLARE @Format int;
DECLARE @SQL    nvarchar(4000);

SET @Month  = " & CStr(Parameters!iMonth.Value)  & ";
SET @Year   = " & CStr(Parameters!iYear.Value)   & ";
SET @Format = " & CStr(Parameters!iFormat.Value) & ";

SET @SQL = 'EXEC [dbo].[sp_TrailerRegistrations] @Month = @Month, @Year = @Year, @Format = @Format';

EXECUTE sp_executesql @SQL , N'@Month int, @Year int, @Format int ', @Month , @Year , @Format
"
1 Like
19

Ummm, thx :smiley: once I had it edited I trimmed the extra whitespace :stuck_out_tongue:

2 Likes
20

It wouldn’t be me if I didn’t mention that if we’re going against the Live database, there are some security implications. First, we would make sure that the SSRS service principal only has READ access. We don’t want Drop Bobby Tables going on. Second, all the regular security issues with using direct SQL queries: no company, field, territory, site security applies.

So, definitely limit who can create and upload SSRS reports!

3 Likes