Microsoft
Microsoft
Hi Tom,
In some cases you can achieve the results you want by bypassing some of the automatic machinery and just do what you want manually, using a query.
For example if I have a table called CurrencyData, with fields CurrencyAxis and Values, that hold a currency value to be used on the x-axis, and a corresponding value on the y-axis respectively, I can create a query like this:
SELECT Format([CurrencyAxis],"Currency") AS Expr1, CurrencyData.Values
FROM CurrencyData
ORDER BY CurrencyData.CurrencyAxis;
This will generate values formatted as currency to use on the x-axis, but will sort them by the actual currency amount (rather than lexically).
Then when you generate the chart, choose Expr1 as the 'axis' field, and Values as the 'values' field, and then change the aggregate on Values from Sum to None. If you now look at the 'Transformed Row Source' property, you will see it has "SELECT [Expr1], [Values] FROM [Query1]", this allows you to get the formatting and sorting you want using the query and the chart just uses the data as you've supplied it.
If you do need aggregates using this approach, you would just do them yourself in the query, rather than relying on the chart to do them for you.
This might still not be exactly what you want, since the chart is now just treating the x-axis values as text, it doesn't adjust the data points based on the value, it just treats them as a sequence (so if you have $10, $100, $110, $200, the space between $10 and $100 will be the same as the space between $100 and $110, rather than having the former be spaced further apart as they would if they were treated as numeric values).
We will certainly consider the request to add formatting for the x-axis values to a future release.
Thanks for the feedback.
Shane Groff
Access Engineering