Conditional Formatting in Crystal Report at runtime

[Dan777]

In my VFP 8 app a report is created at runtime using the Crystal Report activex control. I've figured out how to do everything in code using the activex control except the conditional formatting of objects at runtime. For example, in the details section of a report I want the detail line to appear in a red font if the total sale is over a $1000 and in black if under. So, does anyone know how to conditionally format objects in a crystal report using the crystal activex control?

Thanks much for any and all assistance with this issue.
Dan

 

[Mike Lewis]

Dan,

First, you don't use the ActiveX control for this. The formatting must be done withing CR.

Open the report within CR. Select the field that you want to apply the conditional formatting to. Call up the Format Editor (from the right-click menu). Choose the colour option, and then choose the Formula button (next to the colour choices). This will open the Formula Editor.

Write a formula which tests the value. If it is less than your threshold figure (say, 1,000), return the colour code for black (which is 0); otherwise, return red (which is 255).

You can now run your report from VFP, and you will see the conditional formatting.

If you want to vary the formatting at run time (for example, vary the threshold figure), you will need to add a parameter to your report. To set the parameter value from your VFP program, you will need code something like the following:

loRep.ParameterFields(1).AddCurrentValue(1000)

I hope this makes sense. Come back if it needs clarifying.

Mike

Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[Dan777]

Thanks Mike for the response,

First, actually this doesn't have to be done within CR. In fact, in the crystal docs there's a sample of just how to do it with the activex control, but of course the code is in VB, and there's a number of huge differences between uusing crystal in VB and using crystal in VFP. Furthermore, the business requirements dictate that I have to use the activex control, and not the crystal IDE.

Secondly, I already know how to apply conditional formatting to objects in the crystal IDE, it's a matter of how to do it with the activex control. Ya know, it's like in the activex control there's the FormulaFieldDefinition object, but apparently that's not where Format Formulas go. Apparently and understandably there's a difference between formulas to be used for formatting and formulas used for other tasks.

So the question again is, where does code need to go to apply conditional formatting to an object at runtime; what crystal activex PEM needs to be utilized here.

 

[Dan777]

How does one specify a conditional formatting formula for a report object in the crystal activex control?

In my VFP 8 app a report is created at runtime using the Crystal Report activex control (vers 8.5). I need to know how to apply for conditional formatting formaul for an objects at runtime. For example, in the details section of a report I want the detail line to appear in a red font if the total sale is over a $1000 and in black if under. So, does anyone know how to conditionally format objects in a crystal report using the crystal activex control?

And due to the business requirements I do have to do this in the CR activex control in VFP, instead of the CR IDE. The frustrating thing is that I know this is possible, in fact there's a sample of how to do it in the crystal documentation, but low and behold, the sample is in VB code. And because of the way crystal integrates with VB, which is far different than in VFP, the samplpe doesn't do much good except show that it is possible.

Thanks much for any and all assistance with this issue.
Dan

 

[Mike Lewis]

Dan,

I don't understand. You say you cannot do it in the CR IDE. But, surely, you use the IDE to create the report and format it. For example, you apply fonts and styles in the IDE. The color formatting is exactly the same, except that it has a formula attached.

However, there is another way of doing it. You can programmatically drill down to the objects in question and apply the colour formatting from within VFP. For example, if the object is the first object in the detail band, you can set its color like this:

loRep.Sections("D&quot,ReportObjects(1).ForeColor = RGB(255,0,0)

Does that help at all?

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[Mike Gagnon]

Mike

Small typo

loRep.Sections("D&quot,ReportObjects(1).ForeColor = RGB(255,0,0)

Should be
loRep.Sections("D&quot.ReportObjects(1).ForeColor = RGB(255,0,0)


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Mike Lewis]

Thanks, Mike. I wish my eyes were as good as yours.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[Mike Gagnon]

MikeLewis

No, not my eyes, but I try to test posted code when I have the chance and I had VFP opened at the time I saw your message and I tried your suggestion as I was also trying to drill dow to the field level for conditional formating, for a possible suggestion to Dan777.

But is there a way to use something like?

loRep.Sections("D&quot.ReportObjects(1).ForeColor = IIF(CONDITION = .T.,RGB(255,0,0),RGB(255,255,255))


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Mike Lewis]

Mike,

loRep.Sections("D&quot.ReportObjects(1).ForeColor = IIF(CONDITION = .T.,RGB(255,0,0),RGB(255,255,255))

Yep, that's exactly what I had in mind.

Dan, you can apply that code to each item in the detail band to achieve what you want.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[Dan777]

Hey Mike & Mike,

Thanks for your assistance and all with this issue. Unfortunately, I still don't think we're quite there yet.

loRep.Sections("D&quot.ReportObjects(1).ForeColor = IIF(CONDITION = .T.,RGB(255,0,0),RGB(255,255,255))

The thing about the above statement is that, while it is valid and seems like it'd do the trick, it doesn't.

The data I’m using is being pulled from a SQL Server to the CR via OLEDB. I set that up with a statement such as:

loReport.Database.AddOLEDBSource("Provider=SQLO-LEDB;SERVER=testsql;DATABASE=sales;UID=sa;PWD=xxx","YTDSales&quot

By doing that, and correct me if I'm wrong, the data goes straight from the SQL Server to the CR engine and VFP never sees it, right? And therefore that solution won't work, cause the data at that point isn't even read by VFP, right? And so it has to be a CR formula, not VFP, right, cause the CR engine needs to “look” at the data, not VFP, right?

In the CR IDE we can easily enter a formula for a report object to determine the forecolor it should be printed in. What I need to do is just that, but thru the CR activex control. The .ReportObjects(1).Forecolor property expects only an RGB value, a formula can't be entered there, right? I tried things like:

.ReportObjects(3).Forecolor = "If CurrentFieldValue > 1000 Then Red Else Black"

but with that all I got was the invalid parameter error generated by the CR COM object. There's gotta be somewhere to store a formula like that, right? Any ideas?

Once again, your assistance here is greatly appreciated!!!
Dan

 

[Mike Lewis]

Dan,

Yes, I guess you're right. If the data is coming from SQL Server, VFP won't see it, so the condition will never be executed.

And you certainly can't store the formula in the forecolor property, as you have found.

There is, in fact, a way of accessing the formulas programmatically. The Report object has a FormulaFields collection, which contains one member for each formula defined for the report. You can loop through it, like so:

FOR EACH loFormula IN loRep.FormulaFields
IF loFormula.name = <the name of your formula>
loFormula.text = &quot;<the actual formula>&quot;
ENDIF
ENDFOR

So, if you know the name of an existing formula, you can programmatically set its text (that is, the actual formula itself) to anything you want.

What I don't know off-hand is how to assign that formula to the ForeColor property of another object. I'll dig around in the Help to see if I can find out.

However, I am still not sure if all this is necessary. I still don't understand why you can't simply create the formula and assign it to the ForeColor property from within the CR IDE. I understand that you might not want to decide the threshold value or even the colour codes until run-time, but you can assign those values to parameters, which the formua can act on.

Or am missing something?

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[Dan777]

Hi Mike,

Yeah, I also was thinking about trying something like specifying the formula at the report level, doing somethiing like

whilereadingrecords
if SuchAndSuchValue > 1000 then
SuchObject.forecolor = red
else
SuchObject.forecolor = black
end if

The problem there of course as you mentioned is how to &quot;refer&quot; to &quot;SuchObject&quot; in CR. Ya can't say &quot;This.SuchObject.Forecolor&quot;, right, cause CR of course doesn't have &quot;This&quot;.

The reason I can't just put the formula in the object thru the CR IDE is because I'm not using the CR IDE at all with this report. The entire report is created at runtime. Basically I'm giving enabling the user to 'design their own report' without having to know CR, and without having to use the CR Embeddable Report Designer in the app, which avoids all the legal/licensing issues with CR. I've got it to where everything is done and working thru the CR activeX control, from connecting to the datasource and laying out the fields on the report, to adding lines and boxes. It's just this damn dynamic conditional formatting at runtime.

Now something else that's really frustrating is that there is a sample in the CR docs showing how to do it. But unfortunately the code is in VB. And because of the way the CR designer is integrated into VB, it's very simple there, and doesn't work that way in VFP. What they were saying to do is put code in the section_format event. That format event gets fired on every record printed. So that does sound like that'd do the trick. But then of course how do I access that event in VFP and put code in there? That section_format event isn't exposed in VFP as it is in VB because of the way CR is integrated into VB. But that event is there and visible when I look at the control in the VFP Object Browser. So to access it would I have do to something like create a custom class that IMPLEMENTS that CR activex control's interface or something? I know there's gotta be a way to do it, there's no way this can be done in VB but not in VFP, right? ...can't let VB show up VFP.


Thanks again for all your help with this!
Dan

 

[Mike Lewis]

Dan,

First, I would guess that don't want to set the forecolor properties directly in the formula. Rather, let the formula return the relevant RGB values, and assign those to the forecolor. However, I have to admit that I don't know how to do that.

If you are letting the user create a new report, that suggests you are calling the CR object's NewReport method. If so, you should be aware that that is one of the functions that requires a run-time licence and royalty payments (at least, that was the case with CR 8.x; I assume it still is with 9.0.).

You can get round that by creating an emtpy report in the CR IDE, and using that as a starting point for the new report that the user is creating.

Re your point about putting code in the Section_Format event: In VFP 8, you should be able to use BINDEVENT to do that. I haven't tried it, but it should be worth checking.

Good luck with this. Let us know if you solve the problems.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[Dan777]

Hey Mike,

Thanks for your words of wisdom. Yeah, I am using that .NewReport() funtion. I did think it was royalty-free, but I checked in the documentation after reading your words, and you're absolutely right, that method is not royalty free. However, the method .OpenReport is, and so as you suggested, I'll just put an empty report container out there and do everything to it except use that NewReport method. Ya know, sometimes these software lawyers go way beyond the call of duty and take splitting hairs to an entirely new realm.

I haven't used that BindEvents function either. However, in reading the docs on it, it seems that it's to be used for native VFP object event binding. For COM objects the function EventHandler() should be used. So I'm gonna check that one out some more.

Thanks again!
Dan

 

[TomChmiel]

Hello Dan777,
Could You be so nice and tell me where did You find a sample of dynamic conditional formatting CR at runtime in VB? I have the same problem as You...and I can't resolve it.
Thanks a lot for Your help
Tom

 

[colorinreport]

I am trying to print a color in Crystal report conditionally ... what is the syntax for defining the color ... could u give an example ... databasefld.forecolor = RBG(255) doesn't work in the formula when done in format editor ...
pl. send ur reply as soon as possible
thankx
sudha

 

[Mike Lewis]

Sudha,

You need to write a little formula, using the Crystal formula language.

The formula will look something like this:

if {order.amount} > {orders.max_amount} then rgb(255,0,0) else 0

In the Crystal format editor, go to the Fonts page, click the formula button next to the colour control, and enter the above formula.

By the way, for future reference, it is better to start a new thread for this type of question. The thread you appended your question to is quite old, and not everyone will see it. Also, could I suggest that you do not add &quot;pl. send ur reply as soon as possible&quot; to your question. We are all volunteers here, and we try to reply as soon as we can, but there are lots of other people also waiting for replies.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk