Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Formula based record selection Post Reply Post New Topic
Author Message
JasonLee07
Newbie
Newbie


Joined: 06 Jul 2010
Location: United States
Online Status: Offline
Posts: 6
Quote JasonLee07 Replybullet Topic: Formula based record selection
    Posted: 06 Jul 2010 at 4:21am
Hello All,

I have something I've ran across that I can't quite figure out.  This is a report related to student grades.  I have one formula for each grading period (6) total.  If the student receives a passing grade I return 1 else 0.  I have a separate formula which makes a determination of pass/fail based on the totaling.  I would like to only select records which, based on the calculation are fail.  However it does not seem I can add selection criteria based on a formula. i.e. {@pass}  = false.  One alternative seems is record suppression in the section experts but equally frustrating when blank selections won't suppress group headers.  I suppose they're not actually blank.

Would appreciate any thoughts on how to use (what I call) formula based record selection.


Crystal Reports XI r2  btw.



Edited by JasonLee07 - 06 Jul 2010 at 4:56am
Jason Lee
Developer
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 06 Jul 2010 at 5:02am

Formulas can be used in the record select unless they are using a function that happens after the the report is generated.

What is the @pass formula?
IP IP Logged
JasonLee07
Newbie
Newbie


Joined: 06 Jul 2010
Location: United States
Online Status: Offline
Posts: 6
Quote JasonLee07 Replybullet Posted: 07 Jul 2010 at 2:06am
Basically there are 6 grading periods ( 6 columns with grades) for each record.  I have 1 formula per grading period which returns a 1 for pass and 0 for fail.  The @pass formula simply totals the @gp1 @gp2 ....@gp6.  I've tried having the @pass formula return pass as a boolean and as a 1 or 0. 

Is suppression my only option?  I would assume now that formulas are evaluated after the record is evaluated as true (ie show the record in the report) by the record selection.

Thanks for any suggestions.
Jason Lee
Developer
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 07 Jul 2010 at 3:29am

Are all of the fields on the same row or are you totalling items from more than one row?

IP IP Logged
JasonLee07
Newbie
Newbie


Joined: 06 Jul 2010
Location: United States
Online Status: Offline
Posts: 6
Quote JasonLee07 Replybullet Posted: 07 Jul 2010 at 7:52am
This is a sql statement generated report. I am using grouping for sorting but the fields are on the same row.  I think I have them in a group heading but I could add them to the details and have the same result.
Jason Lee
Developer
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 07 Jul 2010 at 8:00am

Hmmm...

You should be able to just make a statement in select expert to handle the condition.
{@pass}=6 (or whatever you are considering as passed.
 
Please post your exact formula for @gp1
IP IP Logged
JasonLee07
Newbie
Newbie


Joined: 06 Jul 2010
Location: United States
Online Status: Offline
Posts: 6
Quote JasonLee07 Replybullet Posted: 07 Jul 2010 at 9:10am

Each GP has this calc:

if {Command.GP1_MARK} in ["A+","A-","A","B+","B-","B","C+","C-","C","D+","D-","D"] THEN   
1
else if isnull({Command.GP1_MARK}) then
0
else
0

The pass formula looks like this:

if {?pSemester} = "Year Long" then
    if {@gp1} + {@gp2} + {@gp3} + {@gp4} + {@gp5} + {@gp6} + {@final} + {@midterm} > 4 then
        1
    else
        0
else if {?pSemester} = "First" then
    if tonumber({@gp1} + {@gp2} + {@gp3}) + tonumber({@midterm}) >= 3 then
        1
    else if tonumber({@midterm}) = 1 and tonumber({@gp1} + {@gp2} + {@gp3}) > 0 then
        1
    else
        0
else if {?pSemester} = "Second" then
    if tonumber({@gp4} + {@gp5} + {@gp6}) + tonumber({@final}) >= 3 then
        1
    else if tonumber({@final}) = 1 and tonumber({@gp4} + {@gp5} + {@gp6}) > 0 then
        1
    else
        0
else
  
    1;

All the tonumber functions was just troubleshooting.


Edited by JasonLee07 - 07 Jul 2010 at 9:11am
Jason Lee
Developer
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 07 Jul 2010 at 9:31am

Pretty sure it is the use of the param in your pass formula. It is determined at runtime hence it cannot be used as a select criteria in a formula.

 
First you need to clean up the formulas with the NULL option. Crystal stops evaluating on the NULL so if you use it as a condition you have to use it first....
if isnull({Command.GP1_MARK}) then
0
else
if {Command.GP1_MARK} in ["A+","A-","A","B+","B-","B","C+","C-","C","D+","D-","D"] THEN   
1 else 0

Or you can change the setting on your formulas to 'Use default values for Nulls' which I would recommend then you do not have to worry about Nulls at all.
 
Get rid of your PASS formula and imbed the whole selection in the select expert something like this:
({?pSemester} = "Year Long" and {@gp1} + {@gp2} + {@gp3} + {@gp4} + {@gp5} + {@gp6} + {@final} + {@midterm} > 4)
or
({?pSemester} = "First" and 
   ({@gp1} + {@gp2} + {@gp3} + tonumber({@midterm}) >= 3) or (tonumber({@midterm}) = 1 and tonumber({@gp1} + {@gp2} + {@gp3}) > 0)
or
({?pSemester} = "Second" and tonumber({@gp4} + {@gp5} + {@gp6}) + tonumber({@final}) >= 3) or (tonumber({@final}) = 1 and tonumber({@gp4} + {@gp5} + {@gp6}) > 0)
)
 
 
or make a formula for each of the pass fails without the param and you can use those in the select expert
e.g. 
@YearLongPass as   
if {@gp1} + {@gp2} + {@gp3} + {@gp4} + {@gp5} + {@gp6} + {@final} + {@midterm} > 4 then TRUE
@SemesterFirstPass as
    if tonumber({@gp1} + {@gp2} + {@gp3}) + tonumber({@midterm}) >= 3 then TRUE
    else if tonumber({@midterm}) = 1 and tonumber({@gp1} + {@gp2} + {@gp3}) > 0 then TRUE
 
@SemesterSecondPass as
    if tonumber({@gp4} + {@gp5} + {@gp6}) + tonumber({@final}) >= 3 then TRUE
    else if tonumber({@final}) = 1 and tonumber({@gp4} + {@gp5} + {@gp6}) > 0 then TRUE
Then your select criteria would be:
({?pSemester} = "Year Long" and {@YearLongPass})
or
({?pSemester} = "First" and {@SemesterFirstPass})
or
({?pSemester} = "First" and {@SemesterSecondPass})
IP IP Logged
JasonLee07
Newbie
Newbie


Joined: 06 Jul 2010
Location: United States
Online Status: Offline
Posts: 6
Quote JasonLee07 Replybullet Posted: 07 Jul 2010 at 9:58am
worked like a charm. You hit the nail on the head with parameter being in the formula I wanted to use for selection criteria.

Thanks for keeping up with this today.
Jason Lee
Developer
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.