Grand Total column on Matrix Report

You want to achieve this through SQL statement, is it correct?

select year, month, prod, count(*) as qty
 from mytab
group by year, month, prod
union
select 'Grant', 'Total', 'Over Time', count(*) as qty
 from mytab;

cs97jim3,
thanks for the post but this describes how to add a grand total row to the bottom of a report, which I have done previously.  I am looking for a grand total COLUMN at the far right end of my table.

Geotiger,
I am not sure what exactly you mean here.  If I were to append a summary row to the output of my query using SQL, how would I then get this into my matrix as a separate column?  Many Thanks.

You can achieve this as well through sql. For instance, you have a table with the following data

year, month, prod,amount
2007,12,A,20
2008,01,A,30
2008,01,B,20
2008,01,A,40

You want to see a report having year, month, prod_a, prod_b, total_of_ab

select a.year, a.month, a.prod_a, a.prod_b, b.total_of_ab
  from (select year, month, sum(case when prod='A' then prod else null end) as prod_a,
            sum(case when prod='B' then prod else null end) as prod_b
          from mytab
          group by year, month) a,
        (select year, month, sum(*) as total_of_ab
           from mytab
         group by year, month) b
where a.year = b.year
   and a.month = b.month;

If you give more details on your table, I might be able to help you more.

Hi geotiger,
Your example table is essentially what I am using.  If I understand how to apply the principle to this simple case I can then easily apply it to my table.
I am looking to have a total for each product (i.e row) in a column at the very far right.  Essentailly a total for all dates.
I am not sure how to create the matrix using the output of your query to achieve this.  From the SQL it looks like you are creating a total across products for each date, not a total across all dates for each product.  I may be wrong ;-)
Assuming the table you have above, could you please explain how to build the matrix?  Thanks for your time.

Hi,

I have accepted cs97jim's answer as it was the one that (by proxy) mentioned subtotaling on column groups.

It was a simple matter of adding a subtotal to my Year Grouping.  I am certain I tried this before and got some very strange behaviour from the rendered report, therefore I came here looking for a solution.  Whilst explaining to my colleague with the words "look how this subtotal does not do what you think it would....." I viewed the resulting report (behaving exactly as I had been complaining it wouldn't for the past few days)my colleague commented with "yeah, and......?".  Exactly.