Building PDF Reports with Charts
A very common business requirement is to build reports. And good reports have charts.
In this post we'll look at a real-life example of building monthly management reports in InfoLobby. The reports will show the current values for sales, billed hours, and net profit, will compare each value to the previous month and year, and include a chart for the previous 12 months.
The Table Setup
Our table is rather straight-forward. We have fields for:
- Period (year and month)
- Total Sales
- Total Billed Hours
- Total Income
- Total Expenses
- Net Profit (Calculation of Income - Expenses)
- Button to trigger a flow to generate the report
- Attachment field to store the generated PDF
The Flow
Here's where the magic occurs. I'll walk you through the flow in steps.
First, we need to get the previous 12 months worth of data, for both the chart and the comparison values. We'll use the Custom Query action to do this.
It's important that we limit the query to 13 records only and in reverse order. That way the first record in the array is the current one, and the last one is from the same month but last year.
For charting, we'll use QuickChart.io. It's a rather impressive chart library that can be used to generate image charts on the fly.
To make subsequent steps easier, we'll create a scaffold data object with most of the keys that will be repeated for each chart.
If you're building this as we go, here's a copy-pastable version:
$data = {
type:"bar",
data:{
labels:[],
datasets:[{
data:[],
fill: true,
"borderWidth": 2
}]
},
options:{
legend:{
display:false
}
}
};
return $data;
Now we extend this scaffold object with the data we need for each chart and use it to generate image urls that we can insert into our PDF.
Next, we'll create the HTML we need for the comparison texts. We use the current record for the current period value, the second element (1 when zero-indexed) we got from the custom query as last month's value, and the 13th element (12 when zero-indexed) as last year's value.
Before we make the PDF, we'll clear out any existing files from the attachments field, in case we've run the flow before but had to update values etc.
Finally, we'll use the Generate PDF action to generate the PDF. It's just a simple table with the tokens inserted for the HTML we generated in the previous step, and the image urls we generated for the charts.
The Result
So now, when we look at any record in the table, we can click the button to generate the report.
And here's the resulting PDF.
This can easily be extended to also email the report to someone at the same time. After all, we have the HTML and the PDF already.
