RaqForum 28 No.
465 View  • 
     ...

Get Top N from Subgroups and Union Them – For JasperReport

 
SQL(179)

【Question】
How to print each group with a certain number of records whose limit was set by end user when using group in iReport subreport?

Here’s table1:

ID NAME GENDER AGE

 

3 Nicholas M 17

 

5 Michael  M 20

 

6 John F 18

 

6 Aaron M 16

 

6 Hale F 21

 

6 Lke  F 19

 

6 Lsmael F 18

 

6 Brthur M 17

 

6 Quincy F 20

 

6 Sam M 18

 

【Answer】
Printing the top certain number of records can be a part of data source preparation work. But many databases, such as MySQL, doesn’t support window functions. In this case, you can use esProc SPL (Structured Process Language) to help prepare the data source for JasperReport. Below is SPL script:

A

1

$select * from table1

2

=A1.group(GENDER)

3

=A2.(~.top(-3;AGE))

4

=A3.conj()

5

return A4

A2: Group records by GENDER.

A3: Get records with the top N largest AGE values from each group.

undefined

A4: Union those records.

undefined

A5: Return the result to JasperReport.

You can connect to esProc via JDBC to call the SPL script in JasperReport. To learn more about the invocation, see How to Call an SPL Script in Java.


SQL(179)
 
 •  465 View  •