If then record selection with multiple conditions

1
votes

Using Crystal Reports 2011 to reference a View

My formula written as a "formula field"

@mySelection

IF {V_JOB.TASK} LIKE "*IN"
AND {V_JOB.CLOSED} = "Y" 
AND {V_JOB.DATE} >= {?FrDate} 
AND {V_JOB.DATE} <= {?ToDate} 
THEN {V_JOB.JOB} ELSE "FALSE"

My record selection written in the "Record Selection Formula"

{V_JOB.LMO} = 'L' AND
{@mySelection}

This View contains several relevant fields. To make my record selection of the view, I want to display all records that are equal to string in .JOB, when string in .TASK like "*IN" and field .DATE = ?myDateRange and field .CLOSED = 'Y'

so I wrote the equation to do exactly that, but the displayed records are row JOB only when TASK,DATE,CLOSED are true. But I have multiple rows of the same JOB where TASK,DATE,CLOSED is false that I also want to see.

So if there are 30 records for Job A and only 1 of those records has @mySelection is "true" then I want to select ALL 30 records even if the other 29 are "false". The way it is written it only displays the 1 true record and not the other 29.

Can anyone provide some assistance on what I'm doing wrong? Is there a "show all" command or perhaps I can save "true" JOBs in an array and then reference the array as my record selection?

3
mysqlif-statementcrystal-reportsselection
Maybe I have to scan the VIEW twice? If record #30 of Job A is true, it has already determined all other records of Job A are false and already ignored them. Do I need to do this with a sub-report? Output all true jobs and then reference the sub-report for which jobs to display? - Trevor
It's not optimal for performance, but you can pull in all records from the view into Crystal and then do a group suppression based on a summary of {@mySelection} - Ryan
If you want to see all 30 records (your example), then you need to remove the logic from the record-selection formula. What do you want to do if the logic is true? Highlight the row? In any case, using a formula field in the record-selection makes me cringe. - craig
I can write a query to do exactly what I want, but I don't know how to structure an inner select in Crystal Reports. Any assistance is greatly appreciated. SELECT "JOB" FROM "MYDB"."V_JOB" AS "V_JOB" WHERE "JOB" IN (SELECT "JOB" FROM "MYDB"."V_JOB" AS "V_JOB" WHERE "TASK" LIKE '%IN' AND "CLOSED" = 'Y' AND "DATE" >= {D '2013-09-01' } AND DATE" <= {D '2013-09-07' }) AND "LMO" = 'L' - Trevor

3 Answers

0
votes

Where abouts are you inputting your formula within crystal? It is very important.

You should be able to achieve what you want by using conditional suppression in the details section of your report. Something like this...

//Untested//
IF {V_JOB.TASK} LIKE "*IN"
AND {V_JOB.CLOSED} = "Y" 
AND {V_JOB.DATE} >= {?FrDate} 
AND {V_JOB.DATE} <= {?ToDate} 
THEN False ELSE True

This will suppress results where they do not equate to the above conditions.

I found your question a little unclear so this answer might not be perfect for your needs, but hopefully it's a shove in the right direction.

0
votes

I was able to accomplish this using a sub-report to record select with a parameter reference from the main report

{V_JOB.LMO} = 'L' and
{V_JOB.JOB} = {?Pm-V_JOB_OPERATIONS.JOB}

Then I created a main report and linking a parameter to Job.Job in the sub report and did a record selection for my criteria in the main report. 

{V_JOB.TASK} LIKE "*IN"
{V_JOB.CLOSED} = "Y" 
{V_JOB.DATE} >= {?FrDate} 
{V_JOB.DATE} <= {?ToDate}

I then put the sub-report in the details section but that created a duplicate record issue if there was more than one record for Job.Job that satisfied my selection. So to fix that I created a group for Job.Job, and put the sub-report in the group header and suppressed the detail.

I'm not sure this is the most efficient use of CPU power or the best way to program this in CR, but it gives me the correct results that I need, and relatively quickly for the size of the database.

-1
votes

Your Record Selection forumula should be something like this.

{V_JOB.LMO} = 'L' AND
{V_JOB.JOB}

Now you have all the records in your CR.

Use the below formula as a supression option for the field where you are displaying records.

@Supress

IF {V_JOB.TASK} LIKE "*IN"
AND {V_JOB.CLOSED} = "Y" 
AND {V_JOB.DATE} >= {?FrDate} 
AND {V_JOB.DATE} <= {?ToDate} 
THEN true ELSE false

If your condition is true then all records will be displayed else all records will be supressed.

I would suggest you to not to apply these type of conditions in Record selection formula.