CODEWITHSUNDEEP
birt
user3030118
user3030118

Reputation: 11

Get duplicate rows based on one column using BIRT

I have one table in BIRT Report : 

           |  Name    | Amount |
           |    A     |  200   |
           |    B     |  100   |
           |    A     |  150   |
           |    C     |  80    |
           |    C     |  100   |

I need to summarize this table in to another table as : I name is same and add corresponding values.

Summarized table would be : 

           |    A     |  350   |
           |    B     |  100   |
           |    C     |  180   |

Here A = 200 + 150 , B = 100 , C = 80 + 100

How I can summarize table from another table present in BIRT Report ?

Upvotes: 1

Views: 317

Answers (1)

Jeroen
Jeroen

Reputation: 1638

That is quite easy. Just add another table to your report, select the same datasource as the first table (on the tab binding)

Go to the tab groups and add a group on the your 'Name' column.

You'll see the table change. It added group header row and group footer row. The header will also have an element on which you grouped (in this case name)

Now right click next to name in the amount column. Select Insert->Aggregation.

Select function SUM, expression should be amount, Aggregate On should be your newly created group.

Now you can see the results but it will be something like:

       |    A     |  350   |
       |    A     |  200   |
       |    A     |  150   |
       |    B     |  100   |
       |    B     |  100   |
       |    C     |  180   |
       |    C     |  100   |
       |    C     |   80   |

If you delete the detail row from the table, you'll have the result your after.

For you information: Have a play with this, its good excersise. Move the new aggregation to the group footer, add a top border to that cell, put a label total in front if it and you'll have something like this:

       |    A     |        |
       |    A     |  200   |
       |    A     |  150   |
                  ----------
       | total    |  350   |
       |    B     |        |
       |    B     |  100   |
                  ----------
       | total    |  100   |
       |    C     |        |
       |    C     |  100   |
       |    C     |   80   |
                  ----------
       | total    |  180   |

Also, you don't have to select the datasource as the binding, you can also select your first table for the bindings: select the table, open the tab biding, select report item and pick your first table from the dropdown. This can create very complex situations, therefor I usually try to work from the original dataset.

Upvotes: 0

Related Questions