Using SQL window function to realize intra-group cross-line calculation in Birt

This article mainly introduces the method of intra-group cross-line calculation using SQL window function in Birt, which has a certain reference value and can be used for reference by friends who need it. The following information is about the detailed steps of cross-line calculation.

In this example, there are three fields, where SELLERID is the grouping field, DAY is the field to be compared, and the field type is date-time type. You need to design a grouping table. SELLERID is the grouping field, and the detail fields are DAY, AMOUNT and the calculation column SHIFTTIME. The DAY stored in SHIFTTIME differs from the DAY in the last record in this group by a few days. The source data is as follows:

Use the aggregator to organize the data:

A

1=connect ("db") .query ("select SELLERID, DAY,AMOUNT,0 as SHIFTTIME from SALES order by SELLERID, DAY") queries the database, sorts the SELLERID and DAY fields, and produces an extra column of constant spare 2=A1.group (SELLERID) .run (~ .run (interval (DAY [- 1], DAY): SHIFTTIME) grouped by SELLERID, and modify the calculation column SHIFTTIME3=A2.union () in each group of data and merge each group of data 4return A3 to return the calculation results to the report tool.

Save the above SPL code as sales.dfx and introduce it into the Birt report. For Birt to call SPL, please refer to "BIRT calling SPL script".

Step 1: add an aggregator JDBC data source.

Step 2: create the dataset and edit the Query Text.

Step 3: create a report. The report is designed as follows:

Step 4: WEB preview. The preview result is as follows:

After reading this article, have you learned how to use the SQL window function to implement cross-line computation within a group? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel. Thank you for reading.