If you have a chart in Crystal Reports with a date value for the X-Axis, you may have noticed that it is difficult to format / customize the X-Axis date values. For example, you may want the format to be January 2012 or Dec ’11, etc. If you change the X-Axis value to be a string conversion of the date, then you may not have the desired order in the chart depending on how you format the label.
Here’s a tip to help you customize the X-Axis label and also keep the correct date order in the chart…create a cross-tab based on a label formula, the summary that you want to show on the chart, and a date. You can then create a chart by right clicking on the cross-tab and choosing Insert Chart.
1) Click here to open the sample report…note that you can change the format of the X-Axis label by using the parmeter / prompt of the report. Also note that the order of the chart does not change.
2) Go to the Details section of the report. In Report Header C there is a cross-tab and the description of the cross-tab used to create the chart labels.
3) The cross-tab contains 3 values…the Row is the formula that is used for the date label formatting. Right now it uses the parameter / prompt to get a dynamic format from the end user. This will be used by the chart for the On Change of value. Note that this value is actually a string / text output.
4) The second value (the first summary) in the cross-tab is used for the Show Value…in this case it’s a sum of sales.
5) The third value (the second summary) is an important one as it is a max on the date field that is used to create the correct order of the chart. The cross-tab group expert uses the max of the date to create the sort on the cross-tab, which is later passed to the chart.
6) Note that a cross-tab chart can be created by right clicking on any cross-tab and choosing Insert Chart. There are a few drawbacks to using this method such as a cross-tab chart has limited options such as only 1 Show Value.