How can I sum in group by type?

Question

I'd like to create a Jasper template, something which gives the following output:

2016-01-01
     Category         Descritpiton               Income
     --------         ------------               ------
            A         ...                        1230
            B         ...                        1000
            A         ...                         100
            C         ...                        2500
            B         ...                          10
            A         ...                          10
            A         ...                           5
    +--------------------------------------------------+
    Sum
            A                                    1355
            B                                    1010
            C                                     100

2016-01-02
     Category         Descritpiton               Income
     --------         ------------               ------
            A         ...                         500
            B         ...                         100
            B         ...                         100
            E         ...                        2500
            B         ...                          10
            A         ...                          10
            A         ...                           5
    +--------------------------------------------------+
    Sum
            A                                     515
            B                                     210
            E                                    2500

(more days...)

I have an SQL datasource, with a Table with the necessary columns. I could create a report, where the incomes grouped by the day (I achieved this with one group, and an order-by in the SQL select statement). Something like this:

2016-01-01
     Category         Descritpiton               Income
     --------         ------------               ------
            A         ...                        1230
            B         ...                        1000
            A         ...                         100
            C         ...                        2500
            B         ...                          10
            A         ...                          10
            A         ...                           5

2016-01-02
     Category         Descritpiton               Income
     --------         ------------               ------
            A         ...                         500
            B         ...                         100
            B         ...                         100
            E         ...                        2500
            B         ...                          10
            A         ...                          10
            A         ...                           5

(more days...)

But I cannot create the sums at the end of each day. Is there a way, to achieve the given sum-by-category part of the report?

Answer 1

When grouping in jasper report, you will get access to two new bands, groupHeader and groupFooter.

Your need to add the sum table in the groupFooter.

Normal design (re-design report to avoid the need for a new datasource, grouping also on category)

2016-01-01
     Category         Description               Income
     --------         ------------               ------
            A         ...                        1230
            A         ...                         100 
     +--------------------------------------------------+
    Total sum category A                         1335

            B         ...                        1000

           ..........................................
    Total sum  all                               6000

If this is not an option you will need a new datasource for your sum table

Solution 1 (re query):

Add a subreport or jr:table in the groupFooter and re-query your database with the date as parameter (group on category and sum value)

Solution 2 (Create jasper report scriplet, JRDefaultScriptlet)

Create a java class extending the JRDefaultScriptlet class, that stores and sum your values as the report is filled. In solution 1 call scriptlet to get for example a JRBeanCollectionDataSource that can be used as datasource to fill your sum table.