Announcements
 Crystal Reports Forum : General Information : Announcements
Message Icon Topic: Having a brain cramp - sorting months Post Reply Post New Topic
Author Message
dbodell
Groupie
Groupie
Avatar

Joined: 08 Nov 2011
Location: United States
Online Status: Offline
Posts: 53
Quote dbodell Replybullet Topic: Having a brain cramp - sorting months
    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.
Thanks,
D. Bodell
IP IP Logged
zanyar-Jalal
Newbie
Newbie
Avatar

Joined: 06 Sep 2012
Location: Iraq
Online Status: Offline
Posts: 12
Quote zanyar-Jalal Replybullet 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.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet 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.
 
-Dell
IP IP Logged
dbodell
Groupie
Groupie
Avatar

Joined: 08 Nov 2011
Location: United States
Online Status: Offline
Posts: 53
Quote dbodell Replybullet 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!
Thanks,
D. Bodell
IP IP Logged
mjohnsonDMG
Newbie
Newbie
Avatar

Joined: 09 Sep 2008
Location: United States
Online Status: Offline
Posts: 1
Quote mjohnsonDMG Replybullet 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"
Thanks
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.039 seconds.