3 Replies Latest reply on Jun 7, 2013 9:00 AM by philmodjunk

    Report With Group By Claus ( as in SQL)

    Dees

      Title

      Report With Group By Claus ( as in SQL)

      Post

           I have a table that has the following fields:

           RecordId

           EmployeeId

           EmployeeName

           InvoiceId

           CommissionAmount

           Date

            

           Lets say it has the following data:-

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                          RecordId                     EmployeeId                     EmployeeName                     InvoiceId                     CommissionAmount                     Date
                          1                     1                     Ant                     1000                     4.21                     06/01/2013
                          2                     1                     Ant                     1000                     -4.21                     06/02/2013
                          3                     4                     Den                     1001                     36.25                     06/02/2013
                          4                     3                     Bil                     1002                     6.75                     06/02/2013
                      
                     Preferences
                      
                     Preferences

           I want to create a report :-

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
                          Ant
                                                $4.21
                                                -$4.21
                          Total                     $ 0.00
                          Den
                                                $36.25
                          Total                     $36.25
                          Bill
                                                $6.75
                          Total                     $6.75
                           
                          Grand Total                     $43.00

            

           I created a report using Sub-summary by EmployeeName(Leading) but it does not group by employee:- 

           Here's how it looks like:-

            

            

                      
                     Preferences
                      

            

                                                                                                                                                                                                                                                                                           
                          Ant
                                                $4.21
                                                -$4.21
                                                $36.25
                                                $6.75

            

           How do make it group by employee name and how do I do the subtotals an the grand total? 

           Your help will be very much appreciated.

            

            

           P.s - if you see "Ant" with the background green color that is not what I want - dont know why the rows have that green color .

           Thanks

           Dee

            

                      
                     Preferences
                      
                     Preferences

            

                      
                     Preferences
      §
      1
      2
      3
      4
      5
      6
      7
      8
      9
      0
      -
      =
      Backspace
                           
      Tab
      q
      w
      e
      r
      t
      y
      u
      i
      o
      p
      [
      ]
                                
      Return
                                
                           
      capslock
      a
      s
      d
      f
      g
      h
      j
      k
      l
      ;
      '
      \
                           
      shift
      `
      z
      x
      c
      v
      b
      n
      m
      ,
      .
      /
      shift
                           
                                
                               English
                                     
                                     
      alt
      alt
                           
                      
                     Preferences

            

        • 1. Re: Report With Group By Claus ( as in SQL)
          philmodjunk
               

                    I created a report using Sub-summary by EmployeeName(Leading) but it does not group by employee:

               Check to see how you records are sorted. You need to use sort records to sort them by EmployeeName.

               And I would recommend that you sort your records by EmployeeName and by EmployeeID and set up your sub summary part's break field to be EmployeeID instead of EmployeeName. That way you can get an alphabetized list of employees, but still get separate sub totals even if you have two employees with the same name.

          • 2. Re: Report With Group By Claus ( as in SQL)
            Dees

                 I have done that. My Script is:-

                   

                            
                           Preferences
            §
            1
            2
            3
            4
            5
            6
            7
            8
            9
            0
            -
            =
            Backspace
                                 
            Tab
            q
            w
            e
            r
            t
            y
            u
            i
            o
            p
            [
            ]
                                      
            Return
                                      
                                 
            capslock
            a
            s
            d
            f
            g
            h
            j
            k
            l
            ;
            '
            \
                                 
            shift
            `
            z
            x
            c
            v
            b
            n
            m
            ,
            .
            /
            shift
                                 
                                      
                                     English
                                           
                                           
            alt
            alt
                                 
                            
                           Preferences

                 Go to Layout["Commission"]     -- this the layout for the report

                 Sort Records by Field[Ascending;tb_Commission::EmployeeName]  

                 Enter Preview Mode[]

                  

                 However I still dont see the report where it shows the commission grouped by employee name

            • 3. Re: Report With Group By Claus ( as in SQL)
              philmodjunk

                   Try using Sort Records [ Restore ; no dialog]

                   or try sorting the records manually from the sort menu.

                   and please note that you need to use exactly the same reference as you set up in the sub summary layout part. You could have more than one table occurrence for the same table (the name to the left of :: ) and you need to use exactly the same one in both places.