Formatting Dates

Posted on 2004-11-30
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?

Question by:TheAvenger
    LVL 19

    Accepted Solution

    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,"")


    LVL 26

    Assisted Solution

    by:Kurt Reinhardt
    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

    As a result, you really only have two options:

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




    A sample formula to use is:


    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.

    LVL 28

    Assisted Solution

    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({},"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.
    LVL 19

    Expert Comment

    Good point fellas, didn't think about the group sorting.

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

    LVL 26

    Expert Comment

    by:Kurt Reinhardt
    /Agree bdreed35

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


    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    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…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now