1561248 - How to customize the time format on x-axis of a bar chart in a Crystal Report

• How to customize the time format on x-axis of a bar chart to display "hh:mm am/pm"
• How to remove the ‘seconds’ portion from the time format on the x-axis to show am/pm instead

Crystal Reports 2008 (All Patch Levels)

1. Create a Crystal Report against any database that has a datetime/time field
2. Insert a chart on the Report Header (Insert->Chart)
3. Right click the chart and go to ‘Chart Expert’
4. In the ‘Data’ tab select the datetime/time field and move it to the ‘On Change of’ area
5. Select the field which you want to show on the y-axis and move it to the ‘Show value(s)’ area
6. Click ‘Ok’ and refresh the report
7. Observe that the x-axis of the chart either displays the date if it is a ‘datetime’ field or the time in the format "hh:mm:ss" if it is a time field
8. We would like to modify the x-axis by removing the ‘seconds’ portion from the time and display it in a customized format like "hh:mm am/pm"

This is ‘By design’. Crystal Reports will display the database field in any of the axes ‘as it is’

The following work around helps in overcoming this situation

1. Create a new formula called ‘Time’ from the ‘Field Explorer’ as follows: 

if (hour({Time_Field})>12)

then totext({Time_Field }, "hh:mm") + "pm"

else if (hour({Time_Field })=12) and (minute({Time_Field })>0)

then totext({Time_Field }, "hh:mm") + "pm"

else if(hour({Time_Field })=12) and (minute({Time_Field })=0) and (second({Time_Field })>0)

then totext({Time_Field }, "hh:mm") + "pm"

else

totext({Time_Field }, "hh:mm") + "am"; 　

2. Save the above formula
3. Now , go to ‘Chart Expert’ again
4. In the ‘Data’ tab move the above formula - ‘Time’ to the ‘On Change of’ area
5. Click ‘Ok’ and refresh the report. Now the chart shows up with the customized x-axis

Customize x-axis, bar chart, Crystal Reports , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , How To