Using User Defined Global Color Constant on Reports

I got assistance on EE concerning a question I had about conditionally changing the background color of a field on a report.  The answer I received was perfect.  It was:

Yes, you can change the backcolor:Right click on the field -> Format field -> BORDER tab -> X+2 next to BACKGROUND ->
   If {rptDetectOp.LongDescInd} = "C" then crBlue
   Else crWhite;

The available colors are:
crBlack             crMaroon
crGreen            crOlive
crNavy              crPurple
crTeal               crGray
crSilver             crRed
crLime              crYellow
crBlue               crFuchsia
crAqua              crWhite
crNoColor

I am setting up a formula similar to this on many of the reports.  I will probably have the formula in close to 100 fields on ten different reports.  I am concerned that my user can at times be wishy washy and may decide that they want the background to be crLime instead of crBlue.  If that occurs I would have to go in and change 100 formulas, bummer.  

What I would like to do is set up my own color constant and use it in the formula.  I would like to do something like:

MyColor = crBlue

then the formula in all of the reports would be:

   If {rptDetectOp.LongDescInd} = "C" then MyColor
   Else crWhite
 
If the user changes their mind I would only have to change the statement that set MyColor, all of the formulas would stay the same.

I am running my reports from a VB 6.0 app.  If I define the MyColor variable as a global constant in VB will Crystal have acces to it?  For example could I do:

Public Const MyColor As Long = vbCyan

If not, where could I declare and set this variable so all Crystal Reports would be aware of it?  I am kind of new to Crystal especially when it comes to declaring and using User Defined Variables in report generation.
LVL 1
mlcktmguyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GJParkerCommented:
You can't exactly do what you are asking as the constants CR uses are fixed and you can't create your own.

But if you use the RGB function instead of CR constants then the values for this function could be passed programatically at runtime i.e.

If {rptDetectOp.LongDescInd} = "C" then
   RGB (0,0,255)
Else
   RGB (255,255,255)

I would create 3 number parameters in the report and use these to pass the values to this formula i.e.

If {rptDetectOp.LongDescInd} = "C" then
   RGB ({?Red},{?Green},{?Blue})
Else
   crwhite


HTH

Gary
Mike McCrackenSenior ConsultantCommented:
What versiion of Crystal?

This sounds like something the repository of CR9 and later could be used for.  You would then only have to change the formula one time and it would propogate through all your reports.

mlmcc
mlcktmguyAuthor Commented:
I'm using version 11.0
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

GJParkerCommented:
As far as I am aware when updating a  formula held in the repository, this will not automatically update every report that uses that formula. I think you still have to manually open and update each report. I don't use it myself so i'm happy to be proved wrong if this isn't the case.

Did you manage to try my solution ?

Gary
Mike McCrackenSenior ConsultantCommented:
You may be right Gary.  I still have CR8 so haven't used the repository.

mlmcc
mlcktmguyAuthor Commented:
As I mentioned above I am fairly new to Crystal and not at all familiar with the repository.  Can you supply more information on the setup and usage of this feature?
Mike McCrackenSenior ConsultantCommented:
The repository is a feature added in CR9 to allow for TEMPLATE reports and the storage of common formulas, queries, etc.

I still use CR8 so I haven't any idea how to set it up.

mlmcc
GJParkerCommented:
micktmguy

Please could you give feedback on the formulas I posted as the solution.

Gary
mlcktmguyAuthor Commented:
Sorry, I wasn't ignoring your answer but I do not understand how to implement it.  The question mark notation that you used ({?Red},{?Green},{?Blue}) in new to me.  How do I load these values externally and pass them to the report so that this formula will be aware of them.
GJParkerCommented:
{?Red}, {?Green} and {?Blue} are parameter fields.

Create the parameter fields in teh report and then create the formula

If {rptDetectOp.LongDescInd} = "C" then
   RGB ({?Red},{?Green},{?Blue})
Else
   crwhite

Then you can pass the numbers for the color you require from your VB program using the code below

For i = 1 To Report.ParameterFields.Count
    If Report.ParameterFields(i).Name = "{?Red}" Then
        Report.ParameterFields(i).ClearCurrentValueAndRange
        Report.ParameterFields(i).AddCurrentValue (RedValue)
    ElseIf Report.ParameterFields(i).Name = "{?Blue}" Then
        Report.ParameterFields(i).ClearCurrentValueAndRange
        Report.ParameterFields(i).AddCurrentValue (BlueValue)
   ElseIf Report.ParameterFields(i).Name = "{?Green}" Then
        Report.ParameterFields(i).ClearCurrentValueAndRange
        Report.ParameterFields(i).AddCurrentValue (GreenValue)
    End If
Next

Doing it this way the colours are controlled by the VB program, you could store the values in a text file or DB and read from there, so that if the colours needed changing you would only need to change in the text file or db.

HTH

Gary

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.