1289876 - Pie chart legend shows wrong total when data contains negative values in Crystal Reports

SAP Knowledge Base Article - Public

1289876 - Pie chart legend shows wrong total when data contains negative values in Crystal Reports

Symptom

  • The pie chart legend shows the negative values in bracket but the total shown is wrong
  • Negative values are considered as positive numbers when the sum of values is calculated

Reproducing the Issue

  1. Open the Crystal Reports Designer.
  2. Create a report based on the data which contains some negative fields.
  3. Create a pie chart and select the negative field in Show Values
  4. Preview the report

The chart shows slices only for the positive values which is acceptable as negative slices can not be shown. In the legend, values are shown for each value and negative values are shown in bracket. The total in the legend shows a wrong value as it sums up all the values considering them as positive even if some of them are negative.

Crystal Reports XI Release 1

Crystal Reports XI Release 2

Crystal Reports 2008

Cause

Some of the values in the field used in ‘Show value(s)’ are negative and pie chart does not support negative values.

Resolution

Pie charts are not designed to represent the data with negative values as negative slices can not be represented graphically as opposed to negative bars can be shown below the zero line in bar chart. So Pie chart legend can not make difference between a positive value and a negative value while totaling. It is not advisable to use Pie charts with negative values.

Workaround 1:

  1. Fetch only positive values in the report by using a record selection formula like

If {tablename. Fieldname} > 0 then true else false;

{tablename. Fieldname} is the field used in the chart.

This will remove negative values from the chart and legend will show the sum for positive values only which is correct.

Workaround 2:

  1. Create a formula to hold positive values only for the particular database field.
  2. On Field Explorer right click on Formula Fields and click on New.
  3. Give a name to formula and click OK to open the formula editor.
  4. Write the following code to it
  5. numbervar a;

    if {tablename. Fieldname} > 0 then a:= {tablename. Fieldname} else 0;

  6. Click Save and Close to close the formula editor.
  7. Open the Chart Expert and go to Data tab
  8. Select the field containing negative value (on which we have created a formula above) and remove it from Show value(s).
  9. Select the formula from the Available fields and insert it into the Show value(s).
  10. Click OK to close the Chart Expert.

Now legend shows 0 for the negative values and the total shown is correct for the remaining positive values.

See Also

Keywords

Sum, percentage, CR XIR1, CR XIR2 , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , Problem

Product

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