Formatting Dates

Hello Experts

I have a line chart in Crystal Reports 10. The horizontal axis shows the time in months. I would like to see the text in the horizontal axis in a specific format: Jan 2004, Feb 2004, etc. However CR makes it by default 01/01/2004, 02/01/2004 (or whatever the system default format is). I want to force the format to be what I need. Is this possible at all with CR 10? If yes, how?

Thanks.
TheAvenger
LVL 20
TheAvengerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GJParkerCommented:
It looks like the chart object uses the default formats set-up for CR and can't be formatted individually.

The work around will be to change the date to a string in the correct format and group on this formula instead of the date field.

create a formula like this

MonthName (Month ({MyTable.Date}),True) + " " + cstr(Year ({MyTable.Date}),0,"")

Gary

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
If you base the chart on a string formula, then the chart will be sorted in Text order, not Date order:

April 2004
August 2004
December 2004
etc...

As a result, you really only have two options:

1)  Rearrange  your formula so that the chart sorts in 'date' order, with results like:

2004/Q1
2004/Q2
etc...

2004/M1
2004/M2
etc...

2004/01
2004/02
etc...

A sample formula to use is:

ToText({Table.Date},"yyyy")+"/"+ToText({Table.Date},"MM")

2)  Right-click on every single date label in the chart and select 'Edit Axis Label'.  You can modify the label to be exactly what you want, which is fine for one-time only reports, but not good on reports that will be run on a recurring basis where the values will change.

~Kurt
0
bdreed35Commented:
The only problem with Gary's solution is that when it displays, it will be in alphabetical order rather than month order.

If you are are basing your report off of the grouping in your report, you can change the display of the group name field by doing this:

Right click the left border of the report where it says {Table.Date} (it maybe the formula that Gary suggested if you tried his solution) Group, and choose Change Group.
Make sure that you are grouping on the date field, and the period is monthly.
Next, click the Options tab.
Under options select the Customize option, then the radio button next to Use a formula, then click the X+2 next to that.
In the editor, add this as the formula:

    totext({table.date},"MMM yyyy")

Save and Close and Test.
The chart horizontal access will take on the changes that you made to the group name field, but still display in the correct order.

If you happen to be charting using the Advanced option in the Chart Expert, let me know and I can share with you how to do that.
0
GJParkerCommented:
Good point fellas, didn't think about the group sorting.

Brian - good idea much simpler to just customise the Group Name

Gary
0
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
/Agree bdreed35

If you base your report on a group and have summarized values this works best:)

~Kurt
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.