Subtotal and Total

[joecog]

Hi,

I have a crosstab report which shows the top 10 best performed salesman for the selected month and the layout as below :

NetAmount                          Selected Month
RegionA      Salesman 1               1000
                Salesman 2               2000
                (...till Salesman10)

                Total for 10 Salesman
Total For Region A
RegionB      Salesman 1               1500
                Salesman 2               200
                (...till Salesman10)

                Total for 10 Salesman
Total For Region B
Grand Total for All Region


I manage to get the "Total for 10 Salesman" for every Region. Problem is I can't get the Total for every Region. This total is the Total for all Salesman in that Region.

Any idea to solve this ?

[joecog]

Hi,

Urgently needed.
Any idea?

[SynexusStuart]

Have you tried grouping by region and adding the subtotal?

[joecog]

Hi SynexusStuart,

I had not tried grouping and do a subtotal on region.
When I click on Region and click Group, "You can't group the selected objects."

May I know how do I do this?

Thanks.

[Arsenal]

Hi SynexusStuart,

I had not tried grouping and do a subtotal on region.
When I click on Region and click Group, "You can't group the selected objects."

May I know how do I do this?

Thanks.

That is because you're trying to group on something which is already grouped - remember, a crosstab is a container that groups by default. I believe what you're really looking for is a grouped LIST report that you can group on Salesmen and Region, then select Total (from the Aggregations dropdown in the toolbar) so that you can get subtotals for each of them and a grand total at the bottom. You may have to play around with list footers (View Menu) as well as merging cells together to get the sub totals to align correctly. I'm not great at verbally writing down steps (unlike some of the other folks here who excel at it) but there are tech notes and best practices on IBM that show you how to manipulate list footers and merge cells in case you need one.

[joecog]

Hi,

If I want the report as crosstab report, and not list report, how do I do the grouping and subtotal?
Kindly advise as I'm new in cognos.
Thanks.