Making of double-interval cross-report

When using reporting tools such as Jasper or Birt, you often encounter unconventional statistics that are difficult to handle with the report tool itself or SQL, such as the row and column groups of the crosstab are segmented intervals, and the measure (Measurem) comes from other database tables. With a structured and robust computing engine, the integration is simple and can assist reporting tools to easily implement such requirements. The following is an example to illustrate the implementation of the two-interval cross-table.

The primary key for table Account_detail is Account_no, which is a 1-to-many relationship with table Paysoft_result and Naedo. The foreign key of the Paysoft_result is the Custno,naedo foreign key is customer_code. The report requires that the Account_detail field empirica_score be segmented as a row group by an external parameter, and the field Mfin_score is also segmented as a column group by an external parameter. The algorithm of measure is: the number of records of the corresponding Paysoft_result table at the intersection of Account_no divided by account_no corresponding Naedo table.

Is the relationship between the library tables and the relationships between the fields and the report:

Prepare the data with the collector first, with the following code:

A1=mydb1.query ("SELECT * from Account_detail ORDER by Empirica_score,mfin_score")

The code above can fetch data from the Account_detail table. MyDB1 is the name of the data source, pointing to the database. The function query executes the SQL query. The A1 calculation results are as follows:

A2=mydb1.query ("SELECT * from Paysoft_result")

b2=mydb1.query ("SELECT * from Naedo")

Similarly, A2 and B2 extracted data from the Paysoft_result table and the Naedo table, respectively, with the following results:

A3=rowlist.array ()

B3=collist.array ()

These two lines of code convert the parameters from the report to the collector sequence. The parameter rowlist represents a row group, for example "560,575,585,595,605,615,625,635,645,654,665" is 10 consecutive intervals, and the parameter collist represents a column group, such as "39,66,91,116,137,155" is a 5 consecutive interval. The function array converts a comma-separated string into a sequence with the following result:

A4=a1.select (empirica_score>=a3 (1) && mfin_score>=b3 (1))

The data in this case is out of range, such as the "No501" customer's Empirica_sore equals 540, smaller than the interval lower limit of 560. To improve performance and simplify expressions, you can filter out data that is less than the interval lower in A4.

The function select can query or filter data, Empirica_score is a field in A1, A3 (1) represents the 1th member of A3, that is, the interval lower bound 560, the logical operator "&&" for "and". The A4 calculation results are as follows:

A5=a4.group (A3.pselect (empirica_score<~[1]): Row,
B3.pselect (mfin_score<~[1]): col; ~:accounts,
a2.select (accounts. ( ACCOUNT_NO). Pselect (~==custno)):p,
b2.select (accounts. ( ACCOUNT_NO). Pselect (~==customer_code)): N
)    

This Code groups A4 (Account_detail) According to the intervals in A3 (rowlist) and B3 (Collist), and finds the corresponding records for each group of data in A2 (Paysoft_result) and B2 (Naedo).

The function group can group data according to multiple fields (or grouping criteria), such as A.group (field1,field2 ...), can also be grouped after each group of data to be counted or recalculated, such as A.group (field1, Field2 ...; Subtotal1,subtotal2 ...). After grouping, the fields can be renamed with ": New name", and there are 5 fields for the above grouping results, respectively, row,col,accounts,p,n. The results are as follows:

    algorithm for grouping standard row: The Empirica_score field in A4 is grouped by the interval in A3, with the code: a3.pselect ( EMPIRICA_SCORE<~[1]) . The function Pselect can select a member ordinal that matches a condition in the A3, where the symbol "~" represents the current member of the A3, the previous member is represented by the ~[-1], and the next member is denoted by ~[1]. The current interval should be , Since A4 is already above the interval lower bounds, the expression can be simplified to empirica_score<~[1] . For example, "560,575,585,595,605,615,625,635,645,654,665" can divide A1 into 10 intervals: 560-574, 575-584,585-594,595-604,605-614,615-624,625-634,635-644,645-654,655-664, the group numbers are 1 to 10, respectively.

The algorithm for grouping standard col is similar: the Mfin_score field in A4 is grouped by the interval in B3, and the code is b3.pselect (mfin_score<~[1]). For example, "39,66,91,116,137,155" can divide the A1 into 5 intervals: 39-65,66-90,91-115,116-136,137-154, the group numbers are 1 to 5, respectively.

Summary field The account takes the grouped data directly out of the group, where ~ represents the members of the current group. Click on the blue font of the accounts column to see the members in the group, where "row=1,col=1" means the double interval "560-574,39-65", "row=2,col=5" means the double interval "575-584,137-154", such as:

Summary field P algorithm: from A2 to find the corresponding record in accounts, code:a2.select (accounts. ACCOUNT_NO). Pselect (~==custno)). The function select can query the A2 by condition and find the record that matches the condition. where "row=1,col=1", "row=2,col=5" when the P column corresponds to the following records (accounts and A2 are 1-to-many relationship):

&NBSP;&NBSP; the algorithm for summarizing field N is similar: find the corresponding record in accounts from B2, code: b2.select (accounts. ( ACCOUNT_NO). Pselect (~==customer_code))

a6=a5.derive (P.count ():p count,n.count (): ncount)

This code adds a new column pcount,ncount in A5, which calculates the number of records for P and N in each set of data, and calculates the result as follows:

a7=a6.derive (pcount/ncount:rate)

This code adds a new column rate in A6, the algorithm is Pcount divided by ncount, and the result is as follows:

A8=a7.run (String (A3 (row)) + "-" +string (A3 (row+1)-1): Row,string (B3 (col)) + "-" +string (B3 (col+1)-1): COL)

This code echoes the group number in row and col as an interval, and the function run represents the same calculation for each member in A6, such as Row=1,col1=1, which is a member. function string to convert a number to a string. The expression "A3 ()" Can remove a member from a A3 based on an ordinal number, such as A3 (1) equals 560. The A7 calculation results are as follows:

The A8 already contains the three fields required for the report, which simply consists of a new two-dimensional table of row, col, and rate, with the JDBC interface returning the report tool, the code in A9: result A8.new (row,col,rate) .

The function new can take the specified column or computed column from A8 and form a new two-dimensional table, a8.new (row,col,rate) evaluates as follows:

It is worth noting that the set of operators has the parentheses operator, which evaluates the comma-delimited expression sequentially and returns the value of the last expression. With the parentheses operator, you can refine a4-a7 to a single line of code:

A4=a1.select (EMPIRICA_SCORE>=A3 (1) && mfin_score>=b3 (1)). Group (

A3.pselect (empirica_score<~[1]): Row,

B3.pselect (mfin_score<~[1]): col;

(Accounts=~,a2.count (accounts. ( ACCOUNT_NO). Pselect (~==custno))/

B2.count (accounts. ( ACCOUNT_NO). Pselect (~==customer_code)): rate

)

The calculation results are as follows:

A9 is the data set required by the Report tool, the following is a simple cross-table with Jasperreport as the template:

There are three points to note: The crosstab cannot be placed in the detail band, the data Pre sorted property needs to be set to true, and the corresponding parameters of the collector, such as prowlist,pcollist, need to be defined in the report. The report is previewed as follows:

The report calls the collector the same way you call a stored procedure, such as saving this script as UNREGUL.DFX, in the Jasperreport SQL designer, you can use unregul $P {prowlist}, $P { Pcollist} to invoke. For specific integration scenarios, refer to the relevant documentation.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Making of double-interval cross-report