Solved

Crystal Reports - Conditional Formatting based on Results

Posted on 2011-03-08
21
792 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

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. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now