. Sum aggregation with rank condition in BIRT Report


Assuming you have two tables.

Table Name: scores

ID           User_ID      Score        Datetime
-----        -----        -----        -----
1            1            35           2014-07-01 11:00:00
2            1            17           2014-07-01 12:00:00
3            2            36           2014-07-01 11:00:00
4            2            27           2014-07-01 12:00:00
5            1            66           2014-07-02 11:00:00
6            1            77           2014-07-02 12:00:00
7            2            93           2014-07-02 12:00:00


Table Name: users

ID          Username
-----       -----
1           Xxx
2           Yyy
3           Zzz

You are trying to get the output :

ID          User_ID     Username    Sum(Score)  Date
-----       -----       ------      -----       ------
1           1           Xxx         52          2014-07-01
2           1           Xxx         143         2014-07-02
3           2           Yyy         63          2014-07-01

There are two easy ways to get the answer.
1). Use SQL - Move the problem to the database level.

select s.user_id, sum(s.datetime,'day')as theday, sum(score)as score,
       row_number()over(partitionby s.user_id orderby sum(score)desc)as seqnum
from scores s
groupby s.user_id;

To get the rest of the information, use this as a subquery or CTE:

select u.*, s.score
from(select s.user_id, sum(s.datetime,'day')as theday, sum(s.score)as score,
             row_number()over(partitionby s.user_id orderby sum(s.score)desc)as seqnum
from scores s
groupby s.user_id
)s join
     users u
on s.user_id = u.users_id
where seqnum <=3
orderby u.user_id, s.score desc;

It is not difficult to process the code indirectly with advanced techniques such as window function and keep/top/rownumber. However, many databases (such as MySQL) do not have these advanced functions, so they can only be implemented with complex JOIN statements and nested sub-queries.

2). Use esproc SPL - Move the problem to the middleware level.

Here is the esProc SPL script:



=dataSource.query("select s.ID ID,s.User_ID User_ID,u.Username Username,s.Score Score,s.Datetime Datetime from scores s join user u on s.User_ID = u.ID")




=A2.(~.groups(User_ID,Username,date(Datetime):day; sum(Score):sumScore))





Sometimes when we work with databases, it’s hard to write complex SQL queries or implement complex calculations in SQL, or things become thorny if data to be handled isn’t stored in the database. In those cases, we get stuck with logics that are easily implemented with window functions in reporting tools. SPL offers special functions to extract from a group the first N rows or values, the maximum or minimum value by their sequence numbers. This makes it easy to handle top N problems. More explanations and examples can be found in the post grouping Subsets.

For detail SPL integration with BIRT, see How to Call an SPL Script in BIRT.