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.

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

2 Answers
2

You may try doing GROUP BY with ROLLUP:

GROUP BY

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;


Demo

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.

if (name == "Total" || name == "Grand Total")

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.

groups

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)

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.