Hi guys:

 

I am a complete newbie to this tool and am struggling a bit trying to develop a report for HR that will breakdown headcount by country and city for each of our 6 divisions.  I've tried doing this with a crosstab and it's close, but I haven't found a way to include additional fields when grouping by city (e.g. HR wants both the city AND the legal entity name to show in the row).

 

An example of what they are wanting is shown below: 

Country / Physical Location	Physical Location City	Division 1		Division 2		Division 3		Total Headcount
		Office	Factory	Office	Factory	Office	Factory
Australia		0	15	73	150	0	0	238
Company Name - Australia	City A	10	15	0	0	0	0	25
Company Name - Australia Pty Ltd	City B	0	0	73	150	0	0	223
Austria		0	0	12	25	50	96	183
Company Name - ABCD GmbH (sales)	City C	0	0	12	25	0	0	37
Company Name Austria GmbH	City D	0	0	0	0	50	96	146

 

Also, is there a way to selectively turn off totals for groups within a crosstab?  Still reading the manual, but haven't found it as yet.  As I noted above, I tried doing this with a crosstab, but I’m getting a total column for each division and HR isn’t wanting this.  In otherwords, for Division 1 I get a column for Office, Factory, and a Total of both.

 

Any words of wisdom would be appreciated.

 

Thanks in advance....

 

Jim

Edited by JimR - 21 Feb 2009 at 9:47am

I have exactly the same issue with an almost identical report format

It seems that the nesting of two sources at the top of the cross-tab masks the nested total from the effects of "suppress column grand totals". If you look in design view the suppressed totals are hatched out, but the unwanted total is still clear, and there appears to be no way to remove/suppress it.

I'd love to know what the answer to this is, as I have been working towards producing a report for about 9 months and this is the only oustanding obstacle. I'm sure it must be a bug.

Not very helpful as a solution I'm afraid

The best I've managed is to edit the text and delete the word "Total" so that the field is blank, then reduce the width of the column to as small as possible.