1214967 - How to create a Light Indicator Bar in Crystal Reports using RTF?

• Is it possible to create a graphic that dynamically displays a horizontal series of lights, from red to yellow to green in Crystal Reports?

• SAP Crystal Reports 2011
• SAP Crystal Reports 2013
• SAP Crystal Reports 2016
• SAP Crystal Reports 2020

• This can be done using a formula and formatting that use Rich Text Format (RTF) text interpretation. The following formula creates an indicator bar that has seven lights, changing in color from red to yellow to green.
     
  NOTE: The following formula uses the Customer and Orders tables from the sample Microsft Access database, Xtreme.mdb.
     

1. Insert a group on the {Customer.Country} field. Insert a second group on the {Customer.Order Date} field. On the 'Insert Group' dialog box, from the 'The section will be printed:' drop-down box, click 'For each month'.
      
2. On the 'Insert' menu, click 'Summary'. From the 'Choose the field to summarize' drop-down box, click 'Customer.Last Year's Sales'. From the 'Summary location' drop-down box, click 'Group #2: Orders.Order Date - A'. Move the Summary field to Group Header #2.
     
3. Create a formula with the following syntax:
    
   // Input Value used for the Light Indicator.
   NumberVar nbrInput:= <INSERT YOUR SUMMARY VALUE FIELD HERE>;
      
   // Range for the seven lights indicator.
   NumberVar nbrUpper:= 300000;    // Upper limit. Green.
   NumberVar nbrLower:= 100000;    // Lower Limit. Red
     
   // Determines which of the seven lights will be activated.
   NumberVar nbrLt:= (nbrUpper-nbrLower)/5;
   NumberVar nbrCol;
   If nbrInput <= nbrLower Then nbrCol:= 1 Else
   If nbrInput > nbrLower And nbrInput <= nbrLower + nbrLt Then nbrCol:= 2 Else
   If nbrInput > nbrLower + nbrLt   And nbrInput <= nbrLower + nbrLt*2 Then nbrCol:= 3 Else
   If nbrInput > nbrLower + nbrLt*2 And nbrInput <= nbrLower + nbrLt*3 Then nbrCol:= 4 Else
   If nbrInput > nbrLower + nbrLt*3 And nbrInput <= nbrLower + nbrLt*4 Then nbrCol:= 5 Else
   If nbrInput > nbrLower + nbrLt*4 And nbrInput <= nbrLower + nbrLt*5 Then nbrCol:= 6 Else
   If nbrInput > nbrLower + nbrLt*5 Then nbrCol:= 7;
     
   // RTF code to create the Seven Lights Indicator
   "{\rtf1\ansi\ansicpg1252\deff0\deflang4105{\fonttbl{\f0\fnil\fcharset0 Lucida Console;\f1\fnil\fcharset2 Wingdings;}}"  +
   "{\colortbl ;\red0\green255\blue0;\red85\green255\blue0;\red170\green255\blue0;\red255\green255\blue0;\red255\green170" +
   "\blue0;\red255\green85\blue0;\red255\green0\blue0;\red192\green192\blue192;}\viewkind4\uc1\pard \f1\fs30\cf7\'ae\"+
   (if nbrCol > 1 then "cf6\'ae\" else "cf8\'ae\") + (if nbrCol > 2 then "cf5\'ae\" else "cf8\'ae\") +
   (if nbrCol > 3 then "cf4\'ae\" else "cf8\'ae\") + (if nbrCol > 4 then "cf3\'ae\" else "cf8\'ae\") +
   (if nbrCol > 5 then "cf2\'ae\" else "cf8\'ae\") + (if nbrCol > 6 then "cf1\'ae\" else "cf8\'ae\") + "par}\f0}";
        
       
4. Insert the formula in Group Header # 2.
     
5. Right-click the formula and then click 'Format Field'. On the 'Paragraph' tab, click 'RTF Text' from the 'Text Interpretation' drop-down box.
       
6. When the report is previewed, you will see an indicator bar with seven lights. The number of lights lit up depends on where in the range from $100,000 to $300,000 the sum of the 'Last Year's Sales' field for that month lies.
   
   

• Note: Crystal Reports allows up to 64k characters in formula output, which allows with RTF Text Interpretation to create this indicator.

INDICATOR BAR FORMULA RTF TEXT LIGHTS LIGHT BOARD COLOR COLOUR COLORED Crystal Reports Light board graphic RTF Text Interpretation Formatting formulas , c2011872 , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , How To

SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016 ; SAP Crystal Reports 2020