Solved

nested if statements in cryastal 2008 selecction criteria

Posted on 2011-02-17
16
307 Views
Last Modified: 2012-05-11
I'm trying to create seletion criteria to have a report only show loans that have the folloing situation:

In both situations the closing date must be within a range chosen by a parameter field in additon

1 the field {custom_loanmain.ManualOptRefundFlag} = 1
or
2 the field {custom_loanmain.OptimumReimbDate} is either NULL or 1800,1,1 and
the field {CNB_OptimumCreditFee_View.paymentamount} <> 0.00

I can't seem to get the statement correct here is my latest attempt:

{custom_loanmain.Closed} = {?Closed Date Range} and
(((isnull({custom_loanmain.OptimumReimbDate}) or
{custom_loanmain.OptimumReimbDate} = DateTime (1800, 01, 01, 00, 00, 00)) and
{CNB_OptimumCreditFee_View.paymentamount} <> 0.00) or
{custom_loanmain.ManualOptRefundFlag} = 1)
0
Comment
Question by:ISBTECH
  • 7
  • 4
  • 3
16 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 34919569
It looks right.  WHat error are you seeing?

mlmcc
0
 

Author Comment

by:ISBTECH
ID: 34919618
Sorry I should have mentioned that, I'm not getting an error but I'm not getting the correct data either. I'm missing records that should appear based on the criteria.
0
 

Author Comment

by:ISBTECH
ID: 34919972
Here is the report with data.
Optimum-Refunds-Report.rpt
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 34920790
(
  (
    (
    isnull({custom_loanmain.OptimumReimbDate}) or
    {custom_loanmain.OptimumReimbDate} = DateTime (1800, 01, 01, 00, 00, 00)
    ) and
  {CNB_OptimumCreditFee_View.paymentamount} <> 0.00
  ) or
{custom_loanmain.ManualOptRefundFlag} = 1
)

Both of the tables referenced in the above selection criteria are left joined tables.  If you're expecting to see other records, this is almost certainly the problem.  Whenever you apply a filter to a left joined table it negates the left join and turns it into an inner join, thereby eliminating non-matching records you would otherwise expect to see.

You will either need to move those left joined tables to subreports where they can have their own criteria or re-write the report using SQL, most likely with either a SQL Command or database view.

~Kurt
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 34921182
I didn't really mean error in the sense of an error message but what was not working.

Agree with Kurt.

mlmcc
0
 

Author Comment

by:ISBTECH
ID: 34921200
I assume that isn't just left joins but any outer join?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 34921246
Basically yes.  The problem is if you are filtering on the table that could have NULL values because of missing records, Crystal doesn't include them because NULL doesn't equal anything.

TableA LEFT OUTER JOIN TableB  - filtering on table B
TableA RIGHT OUTER JOIN TableB  - filtering on table A
TableA FULL OUTER JOIN TableB  - filtering on table A or B

mlmcc


0
 

Author Comment

by:ISBTECH
ID: 34925862
But doesn't a left outer join mean it will select all records from the left hand table and any record from the right table that has a matches?  For example in this report the view CB_Loan_View will never be null for any loan.  It's a view but it includes the field {LoanMain.LoanRecordID} which does not allow nulls and every loan created will get that field if nothing else.  Then the view CNB_OptimumCreditFee_View is made up from a custom table which may very well not have any records for a particular loan, that is why I used a left join, shouldn't a left join mean that I take all records from CB_Loan_View and I get any records that do exist from CNB_OptimumCreditFee_View?
0
 

Author Comment

by:ISBTECH
ID: 34926562
Ok playing around I get it, the nulls can't be evaluated so it works like I had used a inner join in the first place.  Ok then if I do a subreport I can pull the data I need fine but how do I use the data from the sub report in the selection criteria?  I can link the reports and create a formula in the sub report to use in the main report and that works fine but if it's a WhilePrintingRecords formula it can't go in the selection criteria and if it's not it doesn't populate data...
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 34926619
Please review the following example:


TABLE A
-------
Loan123
Loan234
Loan345
Loan456
etc..

Table B
-------
Loan123
Loan345
etc...

Please note, Loan234 and Loan456 do not exist in Table B


Left OUter Join Scenario - all possible records from Table A are returned.  If there's
a match in Table B the columns from TAble B will be populated in your report.  If there isnt' a match, then NULL values are returned instead.

TABLE A		-->		Table B
--------------------------------------------
Loan123				Loan123
Loan234				NULL (Loan123 doesn't exist in this table)
Loan345				Loan345
Loan456				NULL (Loan345 doesn't exist in this table)
etc..				etc..

Inner Join scenario - only matching records will be returned:

TABLE A		-->		Table B
--------------------------------------------
Loan123				Loan123
Loan345				Loan345
etc..				etc..

Say, for example, there's a date field in table B against which you want to filter.  Your ideal criteria is "show me everything from Table A--whether there's a match from Table B or not--but only show be Table B records where the Date is in the last 30 days...

The problem is that if you specify a field in Table B has to have an explicit value then there's no possibility of it returning a NULL value, since a NULL represents an absence of data.  In other words, when you applied the filter against a column in Table B you've basically said - there HAS to be a match between A and B, thereby negating the outer join.

Open in new window

0
 

Author Comment

by:ISBTECH
ID: 34929089
I get it now, Thanks for both explanations.  One of the option Kurt gave was to use a sub report. How do I use a subreports data in the selection criteria though?  
0
 
LVL 26

Accepted Solution

by:
Kurt Reinhardt earned 500 total points
ID: 34929549
You don't - each subreport has it's own selection criteria and you link the subreport based on the same fields you would have joined from table to table.  One caveat to using a subreport, however, is the overhead it costs to process the report.  One subreport instance will be created for every place the subreport exists when the main report is run.  If, for example, you place a subreport in a group header, it will run once for each group.  If you place it in a detail section then it will run once for every detail records - if there are 10k records, you're going to run the subreport 10k times.

Personally, I think the best solution is to use SQL, although a subreport can do the job.  SQL is much more efficient at this sort of thing and it also means you'll have all of the data in a single data source instead of in multiple sources.

~Kurt
0
 

Author Comment

by:ISBTECH
ID: 35139144
sorry for the delay I did not see the last message and was waiting for a reply.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35163583
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

828 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