[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Crystal Reports - Conditional Formatting based on Results

Posted on 2011-03-08
21
Medium Priority
?
808 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 101

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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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 101

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 101

Expert Comment

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

mlmcc
0
 
LVL 101

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 1000 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 101

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 101

Accepted Solution

by:
mlmcc earned 1000 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

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Question has a verified solution.

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

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. …
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 …
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Free Data Recovery software is an advanced solution from Kernel Tools to recover data and files such as documents, emails, database, media and pictures, etc. It supports recovery from physical & logical drive after a hard disk crash, accidental/inte…
Suggested Courses

612 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