1199099 - How to define the color of a Side by Side Bar Chart by a third value

Side by Side Bar Charts are based on only two values, but it is possible to make them more informative using a third value and bar colors.

The side by side bar chart is showing the Value1 on change of  Value 2 and Crystal Reports allows to set bar colors automatically, specify different color for each Value 1 or to predefine it based on Value 2 summary. In all above mentioned options, color is almost just a visual effect, and does not carry much meaning.  

Create a Side by Side Bar Chart from a Stacked Bar Chart. The third value required for Stacked Bar Chart will be the Bar Color formula, it will define the color of each bar, but will not effect its size. The resulting chart will look like a Side by Side Bar Chart.

The following steps are based on the {Customer} and {Orders} tables of the Extreme Sample Database 11.5 for Crystal Reports XI Release 2.

1. Create a blank reports based on the above tables.

2. It is better to have only a few customers for the best sample visualization. Create records selection formula based on one country only:
 
{Customer.Country} = "Singapore"

Now we have records only for 6 Singaporean customers.

3. Insert a Group based on the {Customer.Customer Name} field.
 
4. Insert a summary of the {Orders.Order Amount} field based on the Group: 

Sum ({Orders.Order Amount}, {Customer.Customer Name}) 

5. Create the following Bar Color formula: 

If ((Sum ({Orders.Order Amount}, {Customer.Customer Name})/{Customer.Last Year's Sales})*100) < 50 then "Less than 50% of the last year's sales"

else

If ((Sum ({Orders.Order Amount}, {Customer.Customer Name})/{Customer.Last Year's Sales})*100) < 100 then "More than 50, but less than 100% of the last year's sales "

else

"More than 100% of the last year's sales"

6. Insert the chart into the Report Footer.

7. In the Chart expert, on the Type tab click Stacked Bar Chart.

8. On the Data tab of the Chart Expert select Advanced Layout. Add {Customer.Customer Name} to the On Change Of section, then add (@Bar Color) to the same section. Add the Sum of {Orders. Order Amount} to the Show values section.
 
9. On the Color Highlight tab of the Chart Expert, click New in the Item List section and select Value of @Bar Color, then select Is equal to in the next line and select More than 100% of the last year's sales in the last line.

10. In the Color format section, click Green.

11. Repeat steps 8 to 10 for Red and Yellow colors.

12. Click OK to close the Chart Expert and preview the report.

Now you can see for example that even if the Ministry of Sports bar is the second highest it is red because this year sales is less than 50% of last year's record.

Crystal Reports 10

The Chart Expert is different for Crystal Reports 10. There is no Highlight tab in Chart Expert, all settings are located in the Format Chart color section (uncheck the Automatically set chart options box within the Type tab of the Chart Expert, click the Options tab, click Format button). 

Bar, Chart, Color, Third value , 4608261 , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , How To

SAP Crystal Reports 10.0 ; SAP Crystal Reports XI ; SAP Crystal Reports XI R2