For the past few days I have been helping a customer develop a GANTT style chart in Crystal Reports.  The challenge was that Crystal’s normal Gantt chart doesn’t allow multiple bar segments on the same row.  So I was showing them how to create a GANTT chart using formulas and specially formatted summary fields. This method requires formatting dozens of small fields with the same color condition.

I selected an initial color but was trying to think of the most efficient way to change the color in all the condition formulas on the fly. Before I had an answer the customer showed me a method I had not seen before.  He created a single formula that returned the name of one of the CR colors, and then referenced that formula wherever we needed the color choice.  By changing that one formula value, the color would change in dozens of other objects.

Now I have written previous articles about using ‘feeder’ formulas like this and this idea had even crossed my mind. But for some reason I was convinced that the only formulas that could return a color value as a result were formatting conditions. Obviously I had never actually tried it. So when I saw that you could return this type of value in a regular formula field I wondered what would happen if I placed this formula on the report. As a test I created a formula field that contained just the color function CRAqua (not in qoutes), then I placed it on the preview page of the report.  What I saw was a large number.  And when I put that number into a font color condition, the font turned Aqua, the same as if I had used the name CRAqua.

So I learned two things.
1) All of the formatting constants are really just aliases for numbers, and you can use the numbers instead.
2) You can assign a formatting constant in one formula and then use that formula to control value in formatting formulas.

It is always a good day when I learn something useful from a client or a student, so thanks to Suresh Gursahaney of MicroAutomation in Centerville, VA .

And if you need help creating a GANTT style chart, please give me  a call.