Solved

nested if statements in cryastal 2008 selecction criteria

Posted on 2011-02-17
16
304 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
Comment Utility
It looks right.  WHat error are you seeing?

mlmcc
0
 

Author Comment

by:ISBTECH
Comment Utility
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
Comment Utility
Here is the report with data.
Optimum-Refunds-Report.rpt
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
Comment Utility
(
  (
    (
    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
Comment Utility
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
Comment Utility
I assume that isn't just left joins but any outer join?
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:ISBTECH
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
sorry for the delay I did not see the last message and was waiting for a reply.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this article I will describe the Copy Database Wizard 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.
This video discusses moving either the default database or any database to a new volume.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now