Seam Excel Subtotal

[yulrizka]

Hi,

I have a query on database, say that table is employee. the table looks like this

Date   Employee Name Sales
----------------------------------
1/1/09 A             10   
1/1/09 B             12   
1/1/09 C             12   
2/1/09 A             10   
2/1/09 B             12   

basicly i want to create excel like this

Date   Employee Name Sales
----------------------------------
1/1/09 A             10   
1/1/09 B             12   
1/1/09 C             12   
SUBTOTAL               34   
2/1/09 A             10   
2/1/09 B             12   
SUBTOTAL               22   

How can i achieve that ?

Thank you very much, i really appreciate any response

[danielc.roth]

Create a list with exact the content of what you want to show, i.e. create the additional rows that are not in the recordset...

[nickarls]

You could also use formulas, although that has a bit of manual overhead, too (keeping track of the range of the sum to cover)...

[yulrizka]

Thank you all for the quick response.
i think im going to go with create a list.

but, bump into another problem.
what if i had a custom hibernate query like

select e.Date, e,employeeName,SUM(case when e.sales > 10 then sales else 0 end) as cTen from Employee e

do i have to create a transient field on the Employee model ? or there are any other way

i successfully retrieve the data, but not always getting this error

[Ljava.lang.Object; cannot be cast to model.Employee

[yulrizka]

sorry for that, i found something that is work

List<Object> result = (List<Object>) EmployeeList.createQuery("select e.Date, e,employeeName,SUM(case when e.sales > 10 then sales else 0 end) as cTen from Employee e").getResultList();
          
          for (Iterator it = result.iterator(); it.hasNext();) {
               Object row[] = (Object[]) it.next();
               System.out.println(row[2]);
          }

Thank you all for helping me