JSON
DynamicPDF Designer uses JSON data to create a report.Designer uses JSON as the data structure for providing data to DLEX documents created using Designer. The JSON document consist of top-level data followed by one or more arrays. Layout data can consist of named and unnamed objects.
DLEX report JSON data is driven by an array where each element in the array is a line of data in the report.
Figure 1. JSON element in an array.
Think of your JSON document like this: top-level JSON elements are for static placement, the first JSON array after top-level elements is for the report, then any nested arrays are for subreports.
If you use Microsoft SQL Server as your database, then an easy way to work with a JSON document is by using the for json auto clause in your SQL statement. For example, the following SQL statement produces a JSON document.
select CategoryName Name, ProductID, ProductName, QuantityPerUnit, Discontinued, UnitPrice from Products, Categories as ProductsByCategory
where Products.CategoryID = ProductsByCategory.CategoryID
order by CategoryName
for json auto, root('ProductsByCategory')
{
"ProductsByCategory": [
{
"Name": "Beverages",
"Products": [
{
"ProductID": 1,
"ProductName": "Chai",
"QuantityPerUnit": "10 boxes x 20 bags",
"Discontinued": false,
"UnitPrice": 18
},
{
"ProductID": 2,
"ProductName": "Chang",
"QuantityPerUnit": "24 - 12 oz bottles",
"Discontinued": false,
"UnitPrice": 19
}
]
}
]
}
Top Level Elements
Top level elements are ideal for using on a cover page. JSON data from top-level elements can be placed on a page or any report section.
{
"ReportName": "Widget Stock By Region State and City",
"Date": "09/21/2021",
"Author": "John Doe"
}
Figure 2. Using top-level JSON elements in a Page.
If creating a header with static top-level JSON fields, consider using a Subreport rather than a header or conditional header.
Repeating Data Elements
Use an array to represent JSON data as data rows. For example, in the following JSON data each element in the regions array constitutes a data-row. When placed in a report's Detail section, it adds each array element as a record, and the elements within the array as fields.
{
"ReportName": "Widget Stock By Region State and City",
"Date": "09/21/2021",
"Author": "John Doe",
"regions": [
{
"name": "North East",
"description": "The north east region's stock by state.",
"units": 369444
},
{
"name": "South West",
"description": "The South West region's stock by state.",
"units": 3514011
}
]
}
For example, in the preceding JSON document, the regions array is the repeating data row.
Figure 3. The regions array in a JSON document.
Add the name, description, and units elements as RecordBox elements in the Detail section and when the PDF is generated each array element is printed as a data row.
Figure 4. Repeating data elements represented as rows in the generated PDF.
If you wish to have a header for the data, use a label for the data name.
Figure 5. Repeating data elements and labels represented as rows in a generated PDF.
Nested Arrays
JSON arrays can have nested arrays, where each nested array corresponds to a Subreport layout element. For example, in the following JSON document, the regions array is the top-level report's Detail section, while states array is a Subreport. The cities array is a nested Subreport.
You can nest as many Subreport layout elements as is reasonable in your DLEX report.
{
"ReportName": "Widget Stock By Region State and City",
"Date": "09/21/2021",
"Author": "John Doe",
"regions": [
{
"name": "North East",
"description": "The north east region's stock by state.",
"units": 369444,
"states": [
{
"name": "New Jersey",
"units": 22609,
"cities": [
{
"name": "Toms River",
"units": 333
},
{
"name": "Newark",
"units": 22276
}
]
},
{
"name": "New York",
"units": 346835,
"cities": [
{
"name": "Albany",
"units": 323423
},
{
"name": "Catskill",
"units": 23412
}
]
}
]
},
{
"name": "South West",
"description": "The South West region's stock by state.",
"units": 3514011,
"states": [
{
"name": "Arizona",
"units": 2156764,
"cities": [
{
"name": "Tempe",
"units": 2124312
},
{
"name": "Phoenix",
"units": 32452
}
]
},
{
"name": "New Mexico",
"units": 1357247,
"cities": [
{
"name": "Las Cruces",
"units": 1234124
},
{
"name": "Albuquerque",
"units": 123123
}
]
}
]
}
]
}
Figure 6. A nested JSON array and corresponding subreport rows.
Multiple Same-Level Arrays
Designer also supports JSON with multiple arrays on the same level. For example, the following JSON has animals and plants on the same level.
{
"store" : "petstore",
"animals": [
{
"name": "dog"
},
{
"name": "cat"
}
],
"plants": [
{
"name": "Ivy"
},
{
"name": "grass"
}
]
}
When displayed in Designer, the Data Explorer lists the top-level element, store, and then the two arrays, animals and plants.
Figure 7. Designer with two arrays on the same level.
However, notice that when adding the array to a report's details, you must select the animals array OR the plants array as the Report layout element's dataName property. If you tried to add the name under plants to the details, then Designer notifies you of the error, informing you that you must change the report's dataName to plants.
Figure 8. Designer setting data name for the report.
Multiple Arrays as Subreports
You also cannot create a subreport, as that implies that plants is nested within animals. Instead, you must treat separate arrays on the same level as different reports. Designer allows creating documents consisting of multiple reports.
Figure 9. Arrays on the same level must be in different reports.
However, you can include multiple sub arrays within a single top-level array. For example, suppose we added a top-level array named stores that contained a sub array of plants and a sub array of animals; the following JSON illustrates.
{
"stores": [
{
"name": "PetStore One",
"animals": [
{
"name": "dog"
},
{
"name": "cat"
}
],
"plants": [
{
"name": "Ivy"
},
{
"name": "grass"
}
]
},
{
"name": "PetStore Two",
"animals": [
{
"name": "dog2"
},
{
"name": "cat2"
}
],
"plants": [
{
"name": "Ivy2"
},
{
"name": "grass2"
}
]
}
]
}
What this JSON allows is a top-level report of stores, nested by two subreports, animals and plants.
Figure 10. Two subreports on the same level below stores.
Objects
The DynamicPDF Designer supports JSON data consisting of a object hierarchy. You refer to individual fields using dot (.) notation. For example, in Figure 10 the report's dataName is barrels. The repeating records are unnamed, however, consist of a nested object named content. So to refer to the nested object, when creating a data row, you would refer to the object as object-name.fieldname.
Figure 11. Report with content objects in the data array.
You are not limited to simple JSON object hierarchies. Designer supports complex JSON object hierarchys. The following JSON illustrates a more complex JSON object hierarchy.
{
"OrderID": 11077,
"OrderDate": "2019-01-06T00:00:00",
"CustomerID": "RATTC",
"ShippedDate": "2019-01-30T00:00:00",
"CustomerDetail":{
"ShipperName": "United Package",
"ShipTo": "Rattlesnake Canyon Grocery\n2817 Milton Dr.\nAlbuquerque, NM 87110\nUSA",
"BillTo": "Rattlesnake Canyon Grocery\n2817 Milton Dr.\nAlbuquerque, NM 87110\nUSA"
},
"Freight": 8.53,
"OrderDetails": [
{
"Product":{
"ID": 2,
"Name": "Chang"
},
"Quantity": 24,
"UnitPrice": 19
},
{
"Product":{
"ID": 3,
"Name": "Aniseed Syrup"
},
"Quantity": 4,
"UnitPrice": 10
},
{
"Product":{
"ID": 4,
"Name": "Chef Anton\u0027s Cajun Seasoning"
},
"Quantity": 1,
"UnitPrice": 22
},
...
The JSON consists of unnamed top-level data that contains a CustomerDetail object in addition to several other top-level fields. It then consists of an array of elements named OrderDetails. Each unnamed element in OrderDetails consists of a nested Product object and also the Quantity and Price fields.
Figure 12. Object model for JSON data.
When loading this JSON into Designer the Data Explorer shows the following data field names in a hierarchy.
Figure 13. Object model for JSON data.
The report in Figure 13 places the top-level data in the Header and the data in a subreport's Detail section. The report's header consists of the top-level data and the nested CustomerDetails. Because CustomerDetail is an object in the top-level data it refers to its fields as CustomerDetail.ShipperName, CustomerDetail.BillTo, and CustomerDetail.ShipTo.
The report's Detail section consists of a nested Subreport. The subreport's dataName is OrderDetails which is the array driving the data row generation. The subreport's Detail section contains the fields Product, Quantity, and Price. These are the fields repeated for each data row. Because Product is an object in each element of the OrderDetails array, it refers to its fields as Product.ID and Product.Name.
Figure 14. Object model for JSON data.
Generating the report results in the following PDF document.
Figure 15. Object model for JSON data.
Nested Objects
Refer to nested objects using dot notation only refer to the property using the parent object's name and the nested object's name. For example, consider the following Content object.
"Content": {
"Description": "Coffee beans from Brazil.",
"Origin": {
"Country": "Brazil",
"State" :"Sao Paulo"
},
"Type": "coffee"
}
The Content object contains a nested Origin object, therefore to refer to the Country or State property you would use Content.Origin.Country or Content.Origin.State when using it in an array as the details of a report.
"Id":"13456X",
"Date":"11/10/2023",
"Client":"Acme Drink Company",
"Barrels": [
{
"Content": {
"Description": "Coffee beans from Brazil.",
"Origin": {
"Country": "Brazil",
"State" :"Sao Paulo"
},
"Type": "coffee"
},
"Volume": 24,
"Material": "Oak",
"Quantity": 300
},
{
"Content": {
"Description": "Burgundy wine.",
"Origin": {
"Country": "USA",
"State" :"California"
},
"Type": "burgundy"
},
"Volume": 32,
"Material": "Cedar",
"Quantity": 45
}
]
}
For example, the following report refers to the Barrels array as the report's dataName. Each array element drives one row in the report's details. Therefore, to refer to properties in the Content object such as Description, you use Content.Description as the data element name. Because Origin is a nested object in Content, you use Content.Origin.Country and Content.Origin.State to refer to the County and State properties.
Figure 16. Nested objects in JSON data.
