I have created a column chart from a cross-tab query. The columns of the crosstab are in the correct order for 'YYYY-MM'. However, the resulting chart re-arranges the x-axis out of the correct order. I've attempted setting the sort order with no success. Is there something I'm missing?
I used to_char([Date],'YYYY-MM') to create the column values. See attached PDF report
Hi,
I would tend to say that you should never sort on data transformed with a to_char(), excepted of course if you only have letters/words in your field.
Here you are talking about dates, even if you need to display the value in a particular format.
So please ensure that you are really sorting on a date value, and not on a to_char([date],'...').
May be it is already the case, and so I'm sorry, but maybe you could try to define the "Pre-sort" property of your DataItem containing the date value.
(sometimes it has saved me)
Hope it helps,
Vinc.
Thanks, Sunchaser. I appreciate your recommendations. I've worked a lot with list reports, but faily new with cross-tabs and charts.
Since my post, I was able to set the sort property for the date (x-axis). I did try to use the to_date function rather than the to_char function initially, but I could not get the chart x-axis to accept my format of 'YYYY-MM'. It continued to include the day and time format no matter how I attempted to define the format.
I normally use 2 columns.
1 for the display value and 1 for the sort value.
Build the chart as normal but when you select the sort option choose advanced sort and select the sort column.
This way you can sort on a date field but display a char field.
For example the sort field would be a full date, but the display field could be month(date).
Exact S2000,
I agree and that was what I tried to mean.
Sorry if I was not clear.