SAP Knowledge Base Article - Public

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

Symptom

  • 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

Environment

Crystal Reports 2008 (All Patch Levels)

Reproducing the Issue

  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"

Cause

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

Resolution

The following work around helps in overcoming this situation

  1. Create a new formula called ‘Time’ from the ‘Field Explorer’ as follows: 
  2. 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";  

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

Keywords

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

Product

Crystal Reports 2008 V0 ; Crystal Reports 2008 V1