Returning Fields When There is No Data in One of the Tables 1

[jmiskey]

I have a Check Register report that returns checks written for a specific client on a certain date. It runs off of 5 tables/queries which are linked together.

The problem is, when there no data it returns nothing at all. We would like it to at least return the company name and check date (for the header so we can see who it was for). Here are the queries and tables:

A - Company Info Query
B - Check Register Table
C - Check/Member Cross Reference Query
D - Member Info Table
E - Plan Info Table

Here is the relationships between them (note the arrows denote Left Outer Joins):

D
/
A -> B -> C
\
E

I think the problem is that there are Record Selectors. There is company ID, which resides in table A, but then there is Check Date which resides in table B. My theory is that since there is no matching check date, it returns no data, even though A and B have a Left Outer Join relationship between them.

Does anyone know a way around this? I thought about using Print Conditionals instead of Record Selectors, but am worried that might mess up my Totals (though the records aren't displayed, would they be included in my Totals)?

Thanks.

 

[lynchg]

Left outer joins will return all of the data from table A(dependant upon any filtering of the Table A data in your Where clause), and only the data from Table B that exists based on your joining field(s), likewise from B to C and so on. So if there aren't any records in B for a certain person then you won't be able to pull in any records from C for that person.

I would make the joins from C to D and E left joins as well. Right now your joins are requiring that there be corresponding data in D and E records for any C record to appear.

 

[jmiskey]

But that is precisely the problem. I understand how Left Outer joins are supposed to work, but it isn't working out the way you or I expected.

For kicks, I made them all left outer joins. The record selector is filtering the records from table A down to just one company. I then have other filters in Table B to select a specific date (for which no records exist for the company we selected from Table A).

So, we would assume that no data would be returned from Tables B, C, D or E. Because of the Left Outer Joins, we expect to get that single client we filtered from Table A. However, that is not showing up on the report (in fact, nothing is).

It is almost as if the Record Selectors are overriding the Left Outer join relationships, and treating it more like an Inner Join (which I confirmed, it is not).

On a side note, I did test out removing the Record Selectors and using Print Conditionals instead, but just as I expected, the suppressed rows are included in my totals (which I obviously do not want). So that probably doesn't appear to be a good alternative.

 

[lbass]

Selecting on an outer table will effectively "undo" a left join. Try removing the selection criteria except on Table A. Then use running totals that have an evaluation formula that excludes the unwanted rows, or use a conditional formula that builds in the criteria for the records you want to summarize, and then insert summaries on that.

-LB

 

[jmiskey]

Thanks LB. Thanks confirms what I expected and explains the behavior.

I see that if I remove the criteria from the imbedded tables, and use Print Conditionals, then use the same logic in the Running Totals, then it will work out.

The only problem is that this version of Crystal Reports is built-in the software program that we are using, and the conditions they enter into the Criteria Form go directly to Record Selector. I'll have to see if there is another way I can get it to pass the parameters into the Report without putting it directly into the Selection Criteria, so I can use those parameters in Print Conditionals and Running Totals instead.

 

[lbass]

I've never heard of "Print Conditionals". If you are able to modify the report, go to report->selection formula->record and remove the clause containing the parameter related to the second table, and instead use it in your running total or conditional formula. In other words, the parameter would be used in one of these instead of in the selection criteria, and therefore should still pass successfully to the report from the form.

-LB

 

[jmiskey]

Sorry, I am mixing lingo from other report programs (with "Print Conditionals"). Really, what I mean is suppressing the printing of the Detail section with a formula.

The problem is more due to how Crystal Reports interacts with this software package we are using. Crystal Reports has been built-in to be the reporting program for this software. This software has a report section where you select the report, and enter the criteria you want on a form/screen. The criteria is then automatically passed to the report as Record Selectors.

Furthermore, this software program and reports is available on the Web so that our clients can run their reports for any time period they want. So we really can't remove the criteria and hard-code anything.

If we could just run the reports from stand-alone Crystal Reports, we could do exactly what you described, but since they must be run from this software package (i.e. client requests from the web), it is a bit trickier than that.

I might be able to remove the criteria from the criteria form and instead invoke a parameter pop-up. Maybe then I can control where the value entered goes (in Suppression and Running Total formulas instead of directly to Record Selection criteria).