Problem source:
http://developer.actuate.com/community/forum/index.php?/topic/36323-months-and-quarters-group-issue/.
Irregular month grouping: If the start date is 2014-01-10, group dates from this date to 2014-02-09 together and dates from 2014-02-10 to 2014-03-9 together. If the start date is 2014-01-31, put dates from this date to 2014-02-27 into a group and take dates from 2014-02-28 to 2014-03-30 as a group.
esPro code:
|
|
A |
B |
|
1 |
=myDB1.query(“select OrderID,Amount,OrderDate from salesall where OrderDate>=? and OrderDate<? order by OrderDate”,startDate,endDate) | |
|
2 |
=interval@m(startDate,endDate) | =startDate|A2.(after@m(startDate,~)) |
|
3 |
=A1.group(B2.pseg(OrderDate);~.sum(Amount),B2(#)) | |
|
4 |
result A3.new(#2:mAmount,#3:mDate) | |
A1: Query database according to the start date and end date. Both startDate and endDate are external parameters.
A2: Count the number of months between the start date and the end date. For example, there are 6 month between 2014-01-31 and 2014-07-31.
B2: Find the day when each of the irregular months begins according to the initial start date and the intervals. Put the start date before the expression for this computation. The sign “|” represents concatenation and “~” represents the current member in the set, i.e. the numbers from 1 to 6. after function is used to get the irregular months.
A3: Group A1’s data by B2’s intervals and calculate sales amount for each of these irregular months; B2 is appended as the last column. pseg function returns the sequence number of the interval in which the data is held. “~” in ~.sum(Amount) represents the current group. “#” represents the sequence number of the current group. Result is as follows:
A4: Retrieve the second and third column from A3 and return them to the reporting tool. esProc provides JDBC interface outward to be identified by a reporting tool as a normal database.
Create a simple list table with BIRT:
A report calls an esProc script in the same way as it calls the stored procedure. Save the above script as BirtUnregulMonth.dfx. You can invoke it with call BirtUnregulMonth(?,?) from BIRT’s stored procedure designer. Below is a preview of the appearance and layout of the result report:
