chart based on results of a crosstab report

[chloe]

hi,
my problem is this...i've already finished a report based on a crosstab query. my report looks like this:

Site Name= Row Heading
Month= Column Heading
Number of records= Value

example:
January February March
Place1 100 200 300
Place2 120 0 220
Place3 290 178 182

in this report, i need to add a BAR CHART FOR EACH SITE NAME, where the months will be in the x-axis and the number of records will be in the y-axis.

how do i do this?
i've tried using the chart wizard but it requires a table or query AND NOT THE RESULTS OF A REPORT to be the source of data.i need to finish this report by tomorrow.please help! Any suggestion would be greatly appreciated.

[andi_g69]

I Assume you have a table like this:

tblTurnover
Place Month Quantity
Place1 Jan 100
Place1 Feb 100
Place1 Mar 200
Place2 Jan 111
Place2 Feb 171
Place2 Mar 321
Place3 Jan 222
Place3 Feb 732
Place3 Mar 967

Your report uses a cross tab as:
TRANSFORM Sum(tblTurnover.Quantity) AS [The Value]
SELECT tblTurnover.Place, Sum(tblTurnover.Quantity) AS [Total Of Quantity]
FROM tblTurnover
GROUP BY tblTurnover.Place
PIVOT tblTurnover.Month;

Build you chart on the raw data, e.g. with quantity on the x-axis, place and Month on y-Axis. I think it is best if you look at the above example, and check out what the wizard makes out of it. This should give you an idea how to do based on your data.

[chloe]

hi andi,
i forgot to mention that we use a parameterized query that asks the user to enter a date range. so we have a crosstab query and a report based on that.

our problem right now is that we need to create multiple charts on a page, one for each site name (which means that there's a chart to be created per row in the resulting report). there are 27 site names and we can only fit 10 charts per page, making it 3 pages all in all. How is this done? do u have any ideas? thanks!