Print Page | Close Window

Having a brain cramp - sorting months

Printed From: Crystal Reports Book
Category: General Information
Forum Name: Announcements
Forum Discription: Please check this section for the latest announcements from Crystal Reports Forum
Printed Date: 07 Aug 2020 at 8:44pm

Topic: Having a brain cramp - sorting months
Posted By: dbodell
Subject: Having a brain cramp - sorting months
Date Posted: 10 Sep 2012 at 6:48am
I have a chart for which the bottom axis is a period (YYYYMM) but I want to show the month name and year instead of the period. I have a formula in which I have put together the monthname and the year in a string but when I use that as the "on change of" axis, it comes out in alphabetical order instead of by month. I KNOW this is something I should be able to do easily and is a common problem but I can't think of it.

D. Bodell

Posted By: zanyar-Jalal
Date Posted: 12 Sep 2012 at 9:26pm
Dear dbodell:

to solve your problem create your query by sql script,
and from sql script you can change date format. I hope it will be helpful for you.

Posted By: hilfy
Date Posted: 14 Sep 2012 at 8:28am
Creating the query by SQL Script will not make any difference to the formatting of the dates and the order of the items in the chart.
The only way I've found to do this is tedious (and there may be a better way).  Basically you do the following:
1.  Run a preview of the report.
2.  In the preview of the chart, click on the individual date label.
3.  Right-click on the date label and select "Edit Axis Label"
4.  Change the label for that individual value.
5.  Repeat for all labels in the chart.
6.  Rerun the report for a different set of dates and do the same thing.
7.  Repeat for as many date ranges as you can.
One you've set the label for a date, Crystal should "remember" that value and it will appear correctly no matter where it is on the chart.  The problem is that this has to be done for ALL possible dates on the report so it may not be feasible for longer periods of time.
Another option would be to change your date format to YYYY-MM - the dash separates the month from the year.  It will give you the same sort and may be easier for the users to read and understand.

Proviti, Data & Analytics Practice -

Posted By: dbodell
Date Posted: 16 Sep 2012 at 8:49am
Actually it turned out to be far simpler than that. I am grouping on the period but I set the group name to a formula which is simply another field which has the monthname and year. So to summarize, I have a field called period, which is YYYYMM. This field was created in my SQL using a convert statement using format 112. Then in Crystal I created a formula called MonthYear and the formula is monthname(right({command.Period},2)) + "-" + totext(left({command.Period},4),0,""). 
Then I set group 1 to be my Period field. Then I went to "change group" and on the options tab I selected customize group name using a formula. My formula was simply my formula field MonthYear.
You should never have to manually change anything in a chart or report. That makes the chart or report virtually inflexible. And using the date format in my sql would not have changed anything about the way the data was displayed in my chart since I still had to sort by the year and month to get the chart in the proper sequence. But thanks for your help!

D. Bodell

Posted By: mjohnsonDMG
Date Posted: 07 Jul 2017 at 2:27am
I sorted my stored proc by datetime asc. I also have a text month (i.e. label format "Jan 2016") formula field for each datetime field on the record. In the line chart in the chart expert I use the formatted field but order that field in "original order". It kept the labels in the original datetime order so "jan 2017" came after "jan 2016... dec 2016"


Print Page | Close Window