Crystal Reports makes it very easy to change the format of date fields and number fields.  So it is frustrating that we don’t have direct formatting abilities inside charts.  If you make a bar chart that has one bar for each month you find that there is no way of directly setting the format for the date values that appear under each bar. (One week after writing this post I discovered a hidden feature in Crystal, which means this is only partly true.  I posted an update with the details but this article is still useful in some situations.)

So I decided to take a slow period this week and figure out the options we have here.  I created a report with a rolling, “last 12 month” bar chart.  Then I added a  requirement that the bars should be labeled:

“Feb-10”, “Mar-10”, etc.

Here is what I found:

1) One thing that does NOT work is changing the default format for dates in File > Options.  Dates in charts do not respond to this setting.

2) My second idea was to write a formula that returns the date as a string like the one above and then use that as my ‘on change of’ field in the chart.  The formula would look like this:

Totext ({Orders.Order Date}, 'MMM-yy')

The formula works fine, but since it has the month name first the monthly bars won’t be chronological.  You could try using “specified order”  but this is not practical with rolling months, since the months change automatically.

3) A workable solution for Group Charts:
A group chart is where you group the report itself by month and take the bars directly from the report’s groups and subtotals.  If your chart Is a group chart then the formula above will help.  But instead of grouping on this formula you group on your original date field with a break for each month.  Then you go into the options for that group, and on the “Options” tab you check “Customize Group Name Field”.  Use the lower “formula” option and click the X-2 button on the right to go into the formula editor.

Here you either write the formula above, or if you have already written it as a formula field, you simply double click that formula field and save it as the formula here.  This method allows the chart to display the correct format without  changing the chronological order.

4) Non-Group Charts:
In some cases you can’t use a Group Chart, because the report needs to be grouped on another field.  You can still use the method described in item 3 by adding a hidden cross-tab to the report.  Add this cross-tab in a new subsection and use your original date field as the row or column field.  Set the cross-tab options for this field to break for each month.  Cross-tab row and column fields give you the same option to customize the group name.  Follow the same steps as above.  Then add your summary field to the Cross-tab.

Once the Cross-tab shows the correct 12 values you can create a chart that is based on the Cross-tab (the chart expert has a cross-tab option).  The order of the bars will come from the date field but the labels for the bars will come from the customized group name.   Once the chart is working you can suppress the cross-tab.  But don’t delete the cross-tab or the chart will vanish with it.