Sub total each cross tab group with grand total in end?

Question

I have result of query in form

EmpId     Profit   OrderID      CompanyName
------    ------   -------     --------------
  1       500 $      1         Acme Company
  1       200 $      1          Evolve Corp. 
  2       400 $      1         Acme Company   
  2       100 $      1          Evolve Corp.
  3       500 $      1         Acme Company  
  3       500 $      1          Evolve Corp.

Now the desired report format is

     EmpId  OrderId  Acme's Profit     Evolve's Profit
     -----  ------   -------------     ---------------
       1      1         700 $               700 $  
      Total  ----       -----               ------ 
                         700$                700$
       2      1         500 $               500 $ 
      Total  ----       -----               ------ 
                         500$                500$
       3      3         1000 $              1000 $ 
      Total  ----       -----               ------  
                        1000$               1000$
     -----   ----     ----------           ---------- 
   GrandTotal           2200 $               2200 $

I tried hard at the crosstab but I'm unable to figure out how to group the records. I tried moving CompanyName in CrossTab columns and moved EmpId in rows & tried a cross tab group but results are not as expected.

My questions are 1) Is this format achievable with a cross tab ? 2) How do I group record's by EmpId's in my crosstab in such a way that the Companies are moved horizontally ?

Edit: I also need sub totals & grand total field.

Don't forget to green-check the best answer to your question :) — PowerUser

PowerUser PowerUser · Accepted Answer · 2010-12-21T15:27:29

1

votes

Put the company in the columns section and you're good to go.

Sample Image

Sub total each cross tab group with grand total in end?

1 Answers