I periodically hear from Adam Butt of APB reports (Norway), usually when he stumbles on a neat Crystal Reports trick. This month he wrote to share a method for controlling the format of dates in charts.

When you do a “group” chart, Crystal creates a bar for each group in the report. The label under the bar will be the same as the group name. If you group on a date (e.g. by day or by week) the corresponding date will appear under each bar. But unlike date fields on the report, there is no format option for dates within a chart. To change the date format you have to use the properties in the group expert. The options tab in the group expert allows you to create a customized formula for the group name. And with Totext you can determine exactly how the date should appear on the report, in the group tree and also in the chart labels of a group chart.

But if you do an advanced chart, which means the chart is grouped by a field that is not an existing report group, this option is not available. You are stuck with the default date format that CR chooses. And advanced charts are handy because you can use any field for grouping the chart without having to match the groups in the report.  Some users make the chart a  “numeric axis” chart which gives them some date options and formatting control, but haven’t had good results from these charts.

Fortunately, Adam found a clever way to get the flexibility of an advanced chart while still having the custom group name ability of a group chart. He uses a cross-tab and creates the chart from the cross-tab. Cross-tabs groupings are independent of the groups of the report (just like advanced charts). And once you create a row group or column group in a cross-tab they both have the same group name property that you find in the groups in the report. Once the chart is created the section containing the cross-tab can be suppressed so it doesn’t need to take up space on the report.

So thanks to Adam for sending in the tip. And if you need help getting CR to create the charts you want, give me a call.