SAP Knowledge Base Article - Public

1549799 - Sort order of the x-axis labels on the chart change when inserting a number field converted to text

Symptom

  • Sort order of the x-axis labels on the chart change when inserting a number field converted to text
  • How to remove decimal numbers from the x-axis label and also make sure that the sort order of the x-axis doesn’t change?

Environment

  • Crystal Reports XI R2 (All Patch levels)
  • Crystal Reports 2008 (All Patch levels)

Reproducing the Issue

  1. Create a Crystal Report against the Xtreme Sample Database
  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 ‘Orders.Order Amount’ and move it to the ‘On Change of’ area
  5. Select ‘Customer.Customer ID’ 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 displays the Order Amounts in sorted order, however we want to remove the decimal places and also make sure that the sort order doesn’t change

Cause

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

Resolution

The following work around helps in overcoming this situation

  1. Create a new formula called ‘Orders’ from the ‘Field Explorer’ as follows: 
  2. totext({Orders.Order Amount},0);

  3. Go to ‘Record Sort Expert’ (Report-> Record Sort Expert) and move the ‘Orders.Order Amount’ field to the ‘Sort Fields’ area. Make sure the ‘Sort Direction’ is ‘Ascending’
  4. Now , go to ‘Chart Expert’ again
  5. In the ‘Data’ tab move the formula - ‘Orders’ to the ‘On Change of’ area
  6. Click on ‘Order’ button and from the drop-down select ‘in original order’
  7. Click ‘Ok’ and refresh the chart
  8. Now, the x-axis labels of the chart show up without the decimal numbers and are sorted correctly

Keywords

x-axis, sort order , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , Problem

Product

Crystal Reports 2008 V0 ; Crystal Reports 2008 V1 ; SAP Crystal Reports XI R2