4 Replies Latest reply on Apr 5, 2009 9:07 PM by Ahmy Yulrizka

    Seam Excel Subtotal

    Ahmy Yulrizka Newbie

      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

        • 1. Re: Seam Excel Subtotal
          Daniel Roth Apprentice

          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...

          • 2. Re: Seam Excel Subtotal
            Nicklas Karlsson Master

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

            • 3. Re: Seam Excel Subtotal
              Ahmy Yulrizka Newbie

              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



              • 4. Re: Seam Excel Subtotal
                Ahmy Yulrizka Newbie

                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