Solved

Crystal Reports - Conditional Formatting based on Results

Posted on 2011-03-08
21
796 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

726 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