?
Solved

Formatting Dates

Posted on 2004-11-30
5
Medium Priority
?
652 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:TheAvenger
  • 2
  • 2
5 Comments
 
LVL 19

Accepted Solution

by:
GJParker earned 1200 total points
ID: 12704154
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
 
LVL 26

Assisted Solution

by:Kurt Reinhardt
Kurt Reinhardt earned 200 total points
ID: 12705483
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
 
LVL 28

Assisted Solution

by:bdreed35
bdreed35 earned 600 total points
ID: 12705566
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
 
LVL 19

Expert Comment

by:GJParker
ID: 12705693
Good point fellas, didn't think about the group sorting.

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

Gary
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 12705702
/Agree bdreed35

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

~Kurt
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses
Course of the Month16 days, 10 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question