CODEWITHSUNDEEP
sqloracle-databasebirt
just10
just10

Reputation: 57

How to add total row in Birt?

I have a data set that display the Name and the value and sorted by Name.

How can I add row for sum on the birt report? Something like this.

    Name  |  Val
    ABC   |   3
    ABC   |   2
    ABC   |   1
    Total |   6
    DEF   |   3
    DEF   |   2
    Total |   5

I tried to do this by grouping the Name column but the output shows like this.

    Name  |  Val
    ABC   |   3
    ABC   |   2
    ABC   |   1
    Total |   6

    Name  |  Val
    DEF   |   3
    DEF   |   2
    Total |   5

Upvotes: 1

Views: 1502

Answers (2)

szefuf
szefuf

Reputation: 520

While Tim's answer is technically correct, you do not have to create such data in SQL, especially, it's not going to be pretty during formatting (you will have a lot of if (name == "Total" || name == "Grand Total") etc.

BIRT tables have things called groups. You can say that you are grouping by name, and every group can have it's header and/or footer. You choose footer and set total in there.

You can read more here: https://help.eclipse.org/mars/index.jsp?topic=%2Forg.eclipse.birt.doc%2Fbirt%2Fsg-DisplayTheTotalNumberOfCustomersInTheReport.html (See Fig. 7-12)

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522226

You may try doing GROUP BY with ROLLUP:

WITH cte AS (
    SELECT Name AS OrigName, SUM(Val) AS Val,
        ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY SUM(Val) DESC) rn
    FROM yourTable
    GROUP BY ROLLUP(Name, Val)
)

SELECT
    CASE WHEN rn=1 AND OrigName IS NOT NULL THEN 'Total'
         WHEN OrigName IS NULL THEN 'Grand Total'
         ELSE OrigName END AS NAME,
    Val
FROM cte
ORDER BY
    OrigName, Val;

enter image description here

Demo

Upvotes: 4

Related Questions