More on controlling colors from formulas

Thursday 23 February 2012 @ 6:44 am

I recently wrote about a post about controlling the color of multiple objects from one formula.   One of my newsletter readers took the idea and ran with it and designed a custom function for his repository:

Just got the ‘Underground News’. The tip on creating formulas for set colors is fantastic – never occurred to me either. Opens up all kinds of possibilities – kind of CSS for Crystal Reports. So now I can

  • Create a ‘suite’ of my company colors and apply them in my reports
  • Create a Custom Function the argument  is a key assigned to each of my colors.
  • Store this Custom Function in my Repository so I can use it in other reports


1. Create a Report Custom Function called ‘MyCompanyColors’, with a single argument (I’ve used a number – in this case I would need a key to remind me which number stands for which color)

2. Build the Function so that it returns a colour for each argument

Function  (NumberVar col)
If col = 1 Then RGB(255,140,140) Else
If col = 2 Then RGB(168,255,190) Else
If col = 3 Then RGB(255,215,174) ...

3. Call my Custom Function from any Conditional Format expression, pass it the right key number, and get the right color.

If {Orders_Invoices_NoRenewals.LineItemTotal} < 5000
Then MyCompanyColors(1) Else
If {Orders_Invoices_NoRenewals.LineItemTotal} >= 5000
And {Orders_Invoices_NoRenewals.LineItemTotal} < 10000
Then MyCompanyColors(3)
Else  MyCompanyColors(2)

The beauty is that the report colors are now standardized and thus consistent across all reports, they can be applied easily, and if there ever is a change, one edit in one formula then ‘cascades’ down to everywhere the color has been used. Simple!


I don’t have many customers who use a repository, but it is still a neat idea.  And you can implement a similar idea without the custom function and still have a fairly simple way of maintaining color consistency across reports.  So thanks to David White,  a Crystal Developer in the UK, for sharing his idea.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)

2 Responses to 'More on controlling colors from formulas'

  1. Ori Matalon - February 29th, 2012 at 7:34 am

    To standardize colors for multiple reports, you can do the following:

    1. Generate a Sub Report with the following formula to define “Red”:
    Shared NumberVar RedR := 255;
    Shared NumberVar RedG := 0;
    Shared NumberVar RedB := 0;

    2. Add this Sub Report to any report that needs this information.

    3. Format any object in the report with this formula:
    Shared NumberVar RedR;
    Shared NumberVar RedG;
    Shared NumberVar RedB;

    Color (RedR, RedG, RedB)


  2. Ken Hamady - February 29th, 2012 at 9:15 am

    The assumption is that you have the subreports all set to ‘reimport when opening’. That way you could change the source subreport and all the reports using that subreport would update automatically.

    Another option would be to create a color table in your database with one record in it. You could add this table to all reports (one record tables don’t even need to be linked) and then read the value from that table. You could store several color values in that one record if you have several fields.

    A third option would be to use one of the formula dlls that allows your formulas to read a text file (See my LINKS page). You could store the color values in a text file and then update the text file as needed.

    Thanks, I think I will write another post on this topic.

Leave a Reply

Recrystallize Pro