For reporting tools such as Birt, expressions grouped by natural months are generally provided, but it is difficult to deal with such requirements grouped by irregular months. If we want to solve this problem, we often need to write a program to realize the custom report dataset, and grouping according to this rule in the program, not only the programming workload is not small, but more importantly, it is basically not universal.
Now, if esProc is introduced into the reporting tool, it will be easy to solve this problem. Let’s take Birt reporting tool as an example to illustrate the implementation process. For other reporting tools, they are basically the same.
In this case, the monthly sales from 2013-01-16 to 2013-08-15 will be counted. The original data in the table salesall are as follows:
We need to get the target representation as shown in the following figure:
esProc’s SPL codes to solve this problem:
|2||=A1.query("select OrderID,Amount,OrderDate from salesall where OrderDate>=? and OrderDate<? order by OrderDate",startDate,endDate)|
A1 Connect the database.
A2 The order data between the start date and the end date are found from the database order table. StartDate and endDate are the date parameters.
A3 Calculate the monthly intervals based on the start and end date, such as the six-month intervals between 2013-01-31 and 2013-07-31.
B3 Calculate the start date of an irregular month based on the start date and date interval, and insert the start date in the first place. “|” means merge and “~” means the current members of the set, i.e. numbers 1 to 6. With different options, elapse function can correctly calculate various irregular months.
A4 Group A2 according to B3 interval, and count the sales in irregular months, keep two decimal places, and take B3 as the last column. The function pseg returns the parameter, which is the interval number of the order date. The ~ in ~.sum(Amount) represents the current group after grouping. # Represents the current group number of A2.
A5 Take the group number of A4 as Number, the second column as TotalAmount, and the third column as StartDate of the group to generate a new table.
A6 Close the database.
A7 Return A5 as a dataset for the reporting tool.
Save the esProc SPL scripts into file orders.dfx and introduce it into Birt report. For introducing SPL scripts into Birt report, please refer to the article 《How to call an SPL script in Birt》.
In BIRT, a simple list table is designed as follows:
Reporting tool calls esProc in exactly the same way as calling stored procedures. For example, in BIRT’s stored procedure dataset, call orders (?,?) can be used to call them. After entering the start date parameter 2013-01-16 and the end date parameter 2013-08-15, preview report to see the required target form.