No aggregate on chart??

A

AJ

I have the following sql code that I grabbed from the sql view:
TRANSFORM Max(ChartQuery.amount) AS MaxOfamount
SELECT ChartQuery.period
FROM ChartQuery
GROUP BY ChartQuery.period
PIVOT ChartQuery.store;

My question is that I do not want to use an aggregate for the amount? If the
amounts ranges from 0 - 100000 then that is what I would like displayed on
the left (y) of the chart.
Can this be done, it seems simple enough?
HELP!
 
R

Rob Parker

I put off responding to this because I don't really understand the question;
seems that no-one else did either, from the lack of replies.

Since you have a crosstab query, you must have some some aggregate function
in the TRANSFORM clause - there is no escaping this. If you don't want an
aggregate, you'll need to use a simple select query as the rowsource for
your chart - but that's likely to make it tricky to display data for both
store and period in the same chart ;-)

Or are you talking about the label that appears against the left (y) axis?
Do you want this to say "0 - 100000", rather than whatever it's displaying
at present? If so, either set an alias for the field in your query, or
simply change the y-axis manually in the chart in design mode.

Or do you want to set the maximum value of the y-axis to 10000? You can do
that with code (in the Current event of a from, or the Format event of the
section of a report where the chart is located) such as this:
Me.Controls("NameOfChartControl").Axes(2).MaximumScale = 100000

Sorry I can't be more helpful; post again describing exactly what it is
you're wanting to do if none of this helps.

Rob
 
Top