Grouping Data
A common building block of report templates is the ability for layout components (Row, Column, Grid and Inline) to include data queries. The dataQuery element in each of these components allows for refining data and repeating one or more components for each occurrence of a specific data element. Consider the following report that displays sales information grouped by month, quarter and then year:
The data used to generate this report is the following:
{
"transactions": [
{
"date": "1/12/2022",
"merchant": "kroger",
"amount": 10.10
},
{
"date": "2/6/2022",
"merchant": "autozone",
"amount": 210.89
},
{
"date": "2/16/2022",
"merchant": "amazon",
"amount": 47.20
},
{
"date": "5/2/2023",
"merchant": "walmart",
"amount": 20.12
},
{
"date": "6/11/2022",
"merchant": "target",
"amount": 15.27
},
{
"date": "8/6/2023",
"merchant": "amazon",
"amount": 47.20
},
{
"date": "9/14/2022",
"merchant": "mcdonald's",
"amount": 28.19
},
{
"date": "11/11/2022",
"merchant": "home depot",
"amount": 73.12
}
]
}
Notice that the data in the report has been grouped by the month and year of the transaction. This is done by applying a data query to a Column component as shown below:
{
"type": "Column",
"dataQuery": "($getYear := function($d){$split($d,'/')[2]};$getMonth := function($d){$split($d,'/')[0]};$getQrtr := function($d){($q:=$d ~> $getMonth() ~> $number(); $q <=3 ? '1' : $q <=6 ? '2' : $q <=9 ? '3' : '4')};transactions.{'year': date ~> $getYear(),'month': date ~> $getMonth(),'qrtr': date ~> $getQrtr(),'merchant': merchant,'amount': amount,'date':date}){year:$} ~> $each(function($v,$k){{'year': $k,'transactions': [$v]}})",
"components": [
{
...
}
]
}
The component specifies a JSONata query to group the data as needed to generate our report. This is a complex query that uses custom functions to extract month, quarter and year information from the transaction date. Here is a better look at the query:
[(
$getYear := function($d){
$split($d,'/')[2]
};
$getMonth := function($d){
$split($d,'/')[0]
};
$getQrtr := function($d){
(
$q:=$d ~> $getMonth() ~> $number();
$q <=3 ? '1' : $q <=6 ? '2' : $q <=9 ? '3' : '4'
)
};
transactions.{
'year': date ~> $getYear(),
'month': date ~> $getMonth(),
'qrtr': date ~> $getQrtr(),
'merchant': merchant,
'amount': amount,
'date':date
}
){ year:$ } ~> $each(
function($v,$k){
{
'year': $k,
'transactions': [$v]
}
}
)]
The result of running this query against the data above is the following:
[
{
"year": "2022",
"transactions": [
{
"year": "2022",
"month": "1",
"qrtr": "1",
"merchant": "kroger",
"amount": 10.1,
"date": "1/12/2022"
},
{
"year": "2022",
"month": "2",
"qrtr": "1",
"merchant": "autozone",
"amount": 210.89,
"date": "2/6/2022"
},
{
"year": "2022",
"month": "2",
"qrtr": "1",
"merchant": "amazon",
"amount": 47.2,
"date": "2/16/2022"
},
{
"year": "2022",
"month": "5",
"qrtr": "2",
"merchant": "walmart",
"amount": 20.12,
"date": "5/2/2022"
},
{
"year": "2022",
"month": "6",
"qrtr": "2",
"merchant": "target",
"amount": 15.27,
"date": "6/11/2022"
},
{
"year": "2022",
"month": "8",
"qrtr": "3",
"merchant": "amazon",
"amount": 47.2,
"date": "8/6/2022"
},
{
"year": "2022",
"month": "9",
"qrtr": "3",
"merchant": "mcdonald's",
"amount": 28.19,
"date": "9/14/2022"
},
{
"year": "2022",
"month": "11",
"qrtr": "4",
"merchant": "home depot",
"amount": 73.12,
"date": "11/11/2022"
}
]
}
]
Now that the data is formatted in this way, we can use additional Column components to further organize our data for the report. Each nested Column component will use the data from its parent. So, in the second nested column, our query is as follows:
transactions{qrtr:$} ~> $each(function($v, $k){
{
'qrtr': $k,
'transactions': [$v]
}
})
This simpler query takes the data and groups it by quarter. The output is the following data:
[
{
"qrtr": "1",
"transactions": [
{
"year": "2022",
"month": "1",
"qrtr": "1",
"merchant": "kroger",
"amount": 10.1,
"date": "1/12/2022"
},
{
"year": "2022",
"month": "2",
"qrtr": "1",
"merchant": "autozone",
"amount": 210.89,
"date": "2/6/2022"
},
{
"year": "2022",
"month": "2",
"qrtr": "1",
"merchant": "amazon",
"amount": 47.2,
"date": "2/16/2022"
}
]
},
{
"qrtr": "2",
"transactions": [
{
"year": "2022",
"month": "5",
"qrtr": "2",
"merchant": "walmart",
"amount": 20.12,
"date": "5/2/2022"
},
{
"year": "2022",
"month": "6",
"qrtr": "2",
"merchant": "target",
"amount": 15.27,
"date": "6/11/2022"
}
]
},
{
"qrtr": "3",
"transactions": [
{
"year": "2022",
"month": "8",
"qrtr": "3",
"merchant": "amazon",
"amount": 47.2,
"date": "8/6/2022"
},
{
"year": "2022",
"month": "9",
"qrtr": "3",
"merchant": "mcdonald's",
"amount": 28.19,
"date": "9/14/2022"
}
]
},
{
"qrtr": "4",
"transactions": [
{
"year": "2022",
"month": "11",
"qrtr": "4",
"merchant": "home depot",
"amount": 73.12,
"date": "11/11/2022"
}
]
}
]
We now have an array of all transactions grouped by Quarter. The Column component will repeat for each element in the array, or 4 quarters. This allows us to gather the Quarter totals and display them in a text component:
{
"type": "Text",
"textElements": [
{
"text": "Qrtr @(qrtr) "
},
{
"text": "@(transactions[0].date)",
"style": {
"textOptions": {
"format": {
"type": "date",
"formatString": "yyyy"
}
}
}
},
{
"text": " Total: "
},
{
"text": "@($sum(transactions.amount))",
"style": "currency"
}
],
"style": "bold, right, large"
}
This text component uses String Substitution and three different queries against the data extracted above. There is a similar Column component for each Month in the Quarter which contains a table that displays the details of each transaction.
Layout component queries are one of the most powerful features of the Reporting engine. It allows for recursion over your data and generating very large, complex reports using JSONata queries to group and organize data in any way imaginable.