Solved

Crystal Reports - Conditional Formatting based on Results

Posted on 2011-03-08
21
795 Views
Last Modified: 2012-05-11
I have three fields in my crystal report that I need to conditionally format.

Field #1 - LastDental (field type is String).

Per my clients request, please bold and increase font by 2 points (currently set to Arial 8 regular) when the LastDental < Today - 365 days or equals "Never".

Field #2 - HGBA1CValue (field type is String).

Per my clients request, please bold and increase font by 2 points (currently set to Arial 8 regular) when HGBA1CValue > 9 or equals "Never".

Field #3 - SMGDate (field type is String).

Per my clients request, please bold and increase font by 2 points (currently set to Arial 8 regular) when the SMGDate < Today - 365 days or equals "Never".
0
Comment
Question by:Jeff S
  • 9
  • 6
  • 5
  • +1
21 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 35071284
Basic method for font size
Behind the formula button for font size
If {YourField} > 9 then
   10
Else
    8

To bold - behind the formula button for style
If {YourField} > 9 then
    crBold
Else
   crRegular


Are the dates strings or is that the field you are displaying?
If strings what format do they have?

mlmcc
0
 
LVL 19

Expert Comment

by:GJParker
ID: 35071313
You can do this by right clicking on the respective fields -> go to the Font tab and add conditional formulas against each of the properties you want to change.

HTH
format.png
0
 
LVL 7

Author Comment

by:Jeff S
ID: 35071317
mlmcc -

The dates are Strings. They are displayed as MM/DD/YYYY. Does that help?
0
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
LVL 19

Expert Comment

by:GJParker
ID: 35071426
You shoudl be able to use this for the font size

Local NumberVar Y := Val(Split({@LastDental}, '/')[3]);
Local NumberVar M := Val(Split({@LastDental}, '/')[1]);
Local NumberVar D := Val(Split({@LastDental}, '/')[2]);

If Date(Y,M,D) < Today - 365 Or {@date} = 'Never' Then
    10
Else
    8

and swithc to crBold and crRegular for the other change

HTH
0
 
LVL 10

Expert Comment

by:gavsmith
ID: 35071473
So like mlmcc said but use:

If CDate({LastDental}) < DateAdd("d", -365, CurrentDate) or {LastDental} = "Never" then
   10
Else
    8

Do the same for SMGDate and you are best converting HGBA1CValue to a number before comparing as strings do compare differently:

If CDbl({HGBA1CValue}) > 9 or {HGBA1CValue} = "Never" then
   10
Else
    8

0
 
LVL 7

Author Comment

by:Jeff S
ID: 35072106
I used:

If CDate({rptApptsByUser;1.LastDental}) < DateAdd("d", -365, CurrentDate) or {rptApptsByUser;1.LastDental} = "Never" then
   10
Else
    8
 
and

If CDate({rptApptsByUser;1.LastDental}) < DateAdd("d", -365, CurrentDate) or {rptApptsByUser;1.LastDental} = "Never" then
   crBold
Else
    crRegular

and getting back an error at run time that indicates: "Bad Date Format String".
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35072444
To use them directly they must be in YYYY/MM/DD format.  Did you try the GJParker method?

mlmcc
0
 
LVL 7

Author Comment

by:Jeff S
ID: 35072588
GJParkers method gives me this error at runtime:

"A subscript must be between 1 and the size of the array"

I have no clue what this means.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35072627
What is the actual data in the field not how it is displayed?

mlmcc
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35072635
The error indicates
1.  You have some data that is in the wrong format or is null
2.  The dates are stored in a format and displayed as DD/MM/YYYY

mlmcc
0
 
LVL 19

Assisted Solution

by:GJParker
GJParker earned 250 total points
ID: 35072799
Try this, I forgot to handle where the date = 'Never'

Local NumberVar Y;
Local NumberVar M;
Local NumberVar D;

If {@LastDental} <> 'Never' Then
(
Local NumberVar Y := Val(Split({@LastDental}, '/')[3]);
Local NumberVar M := Val(Split({@LastDental}, '/')[1]);
Local NumberVar D := Val(Split({@LastDental}, '/')[2])
);
Else
(
Local NumberVar Y := 1900;
Local NumberVar M := 1;
Local NumberVar D := 1
);

If Date(Y,M,D) < Today - 365 Then
    10
Else
    8

0
 
LVL 7

Author Comment

by:Jeff S
ID: 35072875
I am getting a message that the indicates: "The remaining text does not appear to be part of the formula" and it highlights everything from the ELSE down to the 8.
0
 
LVL 19

Expert Comment

by:GJParker
ID: 35072929
remove the semi colon before the else
0
 
LVL 7

Author Comment

by:Jeff S
ID: 35073116
Getting so close ... getting this now. "The string is non-numeric"
0
 
LVL 19

Expert Comment

by:GJParker
ID: 35073642
i'm guessing that one of your dates isn"t in the correct format and one of you date par
ts cant be converted to a number.
0
 
LVL 7

Author Comment

by:Jeff S
ID: 35073768
I conditionally formatted them in SQL and all should be MM/DD/YYYY or "Never".
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35074951
Try ir this way

Local NumberVar Y;
Local NumberVar M;
Local NumberVar D;

If  InStr({@LastDental},'/') > 0 Then
(
Local NumberVar Y := Val(Split({@LastDental}, '/')[3]);
Local NumberVar M := Val(Split({@LastDental}, '/')[1]);
Local NumberVar D := Val(Split({@LastDental}, '/')[2])
);
Else
(
Local NumberVar Y := 1900;
Local NumberVar M := 1;
Local NumberVar D := 1
);

If Date(Y,M,D) < Today - 365 Then
    10
Else
    8


mlmcc
0
 
LVL 7

Author Comment

by:Jeff S
ID: 35075619
mlmcc,

I am back to "The string is non-numeric". I am stumped.
0
 
LVL 7

Author Comment

by:Jeff S
ID: 35075773
mlmcc and GJParker -

Please accept my apology. I had the conditional fomatting on the HGBA1CValue I asked for help on earlier and didn't realize I still had that on. I was going through and making changes and testing these one by one and found your coding was right. The issue is with the suggestion gavsmith provided earlier. I am getting the "The string is non-numeric" on this:

If CDbl({rptApptsByUser;1.HGBA1CValue}) > 9 or {rptApptsByUser;1.HGBA1CValue} = "Never" then
   10
Else
    8
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 250 total points
ID: 35078187
You are converting the value and it may be NEVER
Try this

If {rptApptsByUser;1.HGBA1CValue} = "Never" then
    10
Else If CDbl({rptApptsByUser;1.HGBA1CValue}) > 9  then
   10
Else
    8

mlmcc
0
 
LVL 7

Author Closing Comment

by:Jeff S
ID: 35078283
Split Points to be Fair. Thanks a million!!
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question