Solved

nested if statements in cryastal 2008 selecction criteria

Posted on 2011-02-17
16
309 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
16 Comments
 
LVL 101

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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
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 101

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 101

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 101

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
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.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

623 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