Say I have the following table
Employee ID....Name....Gender...City
I want to know how many males work in each city and put it in a column chart.
So I create crosstab query as follows...
Row Heading = Gender - Where males only
Column Heading = City
Value = Count of EmployeeID
Row Heading = Count of TotalOfID:EmployeeID
I need to generate the report in the following format:
report to be in tablular form (single row for males, one column per city to give the number of males)
add a column graph to the report (horizontal-Axis=City, vertical-axis = Total males)
The report in tabular form works well the way I do it.
In order to generate the graph, I would intuitively use Value = SumOfTotalOfEmployeeID, Axis=City, Series=Gender
Unfortunately that does not work because the result of the crosstab query yields individual city names for the column headings rather than having them grouped as a [City] field...and I can only put one item in the chart wizard for the axis (and not a whole bunch of different cities)
I have a workaround with the crosstab query...as follows
Row Heading = City ***add dummy row heading to have cities grouped as a single field to generate the graph***
Row Heading = Gender - Where males only
Column Heading = City
Value = Count of EmployeeID
Row Heading = Count of TotalOfEmployeeID
ID
This allows me to enter the following data in the graph wizard as follows
AXIS = City
DATA = SumOfTotalofEmployeeID
Series=Gender
which works well
But now unfortunately the tablular report is screwed up as I do not have a single line with all males in each city....my tabular report now has as many lines as I had cities where males are living.
Is there anyway to get both the graph and single-line tabular data in the report. I do not like the graph option of adding the datatable to the graph.
Thanks for your help.
Bruno
Employee ID....Name....Gender...City
I want to know how many males work in each city and put it in a column chart.
So I create crosstab query as follows...
Row Heading = Gender - Where males only
Column Heading = City
Value = Count of EmployeeID
Row Heading = Count of TotalOfID:EmployeeID
I need to generate the report in the following format:
report to be in tablular form (single row for males, one column per city to give the number of males)
add a column graph to the report (horizontal-Axis=City, vertical-axis = Total males)
The report in tabular form works well the way I do it.
In order to generate the graph, I would intuitively use Value = SumOfTotalOfEmployeeID, Axis=City, Series=Gender
Unfortunately that does not work because the result of the crosstab query yields individual city names for the column headings rather than having them grouped as a [City] field...and I can only put one item in the chart wizard for the axis (and not a whole bunch of different cities)
I have a workaround with the crosstab query...as follows
Row Heading = City ***add dummy row heading to have cities grouped as a single field to generate the graph***
Row Heading = Gender - Where males only
Column Heading = City
Value = Count of EmployeeID
Row Heading = Count of TotalOfEmployeeID
IDThis allows me to enter the following data in the graph wizard as follows
AXIS = City
DATA = SumOfTotalofEmployeeID
Series=Gender
which works well
But now unfortunately the tablular report is screwed up as I do not have a single line with all males in each city....my tabular report now has as many lines as I had cities where males are living.
Is there anyway to get both the graph and single-line tabular data in the report. I do not like the graph option of adding the datatable to the graph.
Thanks for your help.
Bruno
