Left outer joint not showing correctly

[tektipsMem]

Hi guys,

I am using Crystal Repoort XI. I have two tables joint by left outer joint, enforced from and joint type "=".

Here's what shows in SQL

SELECT `UN2005`.`Date / Time`, `Meters`.`Site`, `Meters`.`Unit`, `Meters`.`Date returned`, `UN2005`.`Meter Serial`, `UN2005`.`Result`, `Meters`.`Serial number`
FROM `Meters` `Meters` LEFT OUTER JOIN `UN2005` `UN2005` ON `Meters`.`Serial number`=`UN2005`.`Meter Serial`
WHERE `Meters`.`Date returned` IS NULL AND (`UNKA2005`.`Date / Time`>=#2005-08-01 00:00:00# AND `UNKA2005`.`Date / Time`<#2005-08-31 00:00:01#)
ORDER BY `Meters`.`Site`, `Meters`.`Unit`, `UNKA2005`.`Date / Time` DESC

I want the report to show :

Meter table serial number
UN2005 table meter serial result date/time
123 1.1 01/08/05
234 1.2 12/08/05
235 2.3 31/08/05
345
678

But it only shows all the serial number with results and date and did not show serial 345 and 678 (in detail section). Oh, I also group the site and units in header but I don't think it matters.

An anyone help and see what did I do wrong?

Thanks
TTM

 

[Madawc]

If you link table A to table B using a left-outer, but then put a condition on table B, it treats it as if it was an equals join, refused to show table A entries without a table B entry.

This is part of Crystal's general habit of stopping whenever it gets a null value. When the table B entry is absent, all of its fields are null.

It ought to be possible to get round this by including a test for null value along with the table-B condition. But I've never been able to get this to work.

An easier alternative is to remove the table B test and suppress the data you do not want. You don't say exactly what you are doing: possible methods are a lower-order group with details suppressed, or a logical test to blank fields that you do not want.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[tektipsMem]

Madawc,

Thanks for speedy reply.

The Meter table (Table A) has a few hundred records with unique serial number and the UN2005(Table B) has a few thousand records. Serial number in table B is not distinct. There could be multiple meters of same seial number to give different "results". I do need to filter a date range to monitor semi-annually (or quaterly) if some meters serial had not been used to generate a "result".

What would you suggest to get around the null handling by Crystal? I am not a programmer, so if it involves code writing, you have to show me and explain a little bit more in detail.

Thanks in advance.
TTM

 

[lbass]

If you want ALL meters to appear, then remove the date criteria from the record selection formula. Then create a formula in the formula expert:

//{@Result}
if isnull({UN2005.Date / Time}) or
not({UN2005.Date / Time} in datetime(2005,08,01,00,00,00) to datetime(2005,08,31,23,23,59)) then
0 else
{UN2005.Result}

Similarly, create a formula for the datefield:
//{@RelevantPeriod}:
if isnull({UN2005.Date / Time}) or
not({UN2005.Date / Time} in datetime(2005,08,01,00,00,00) to datetime(2005,08,31,23,23,59)) then
datetime(0,0,0,0,0,0) else
{UN2005.Date / Time}

If there should only be one result per meter for the time period, then insert a group on the meter number and insert maximums on the above formulas and then suppress the detail section.

-LB

 

[tektipsMem]

IBass,

Made some progress. modified your formula to read 1 for null (or no value) because I need to count the number of times where there is no value with the meter. I also suppress detail on meter number. Report looks choppy though because I also suppress the group in Unit as I only want to show the Units which do not have any result with the meter and do not know how to insert maximum on the meter serial, plese help.

I do need to have flexibility to choose the date range, is there anyway to do that as a parameter?

TTM

 

[tektipsMem]

LB

Found thread 767-1151915 "Printing a Null set of records" applied to my situation. I still applied the formula you indicated in my thread. Report looks good and part of my problem is solved.

Here is the complicated part: Meter table contains all serial number (unique). UN2005 table contains date/time, result, meter serial (not unique and not all of the serial as in Meter table). Each month is a different UN2005 table (let's call them Jan05, Feb05, Mar05). Each month may have different meter seial that do not have "result" (that's the null value and any time result is null, date/time will be null as well).

Simply put:

Meter.
Serial number Jan05 Feb05 Mar05 Total cnt
001 1 1 2
002 1 1
003 1 1
004 1 1
005 1 1

The number under Month05 are number of times where meter serial has NO results, it is always 1 within that month. I am only interested in meter serial that has no results.

Question: What is the best way to link these tables together to achieve the count as above? How do I put a date range parameter and not messing up showing the null records ?

Please help.
TTM

 

[Madawc]

With a left-outer, you need to read all of the data and suppress the records you don't want.

When the tests are not straightforward, it's a good idea to first write the tests as 'boolians', formulas without an if ... then .. and which return 'true' or 'false'. If they return neither you have hit a 'null'. Otherwise you can see what's happening and adjust if the test isn't working.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

You should have explained the whole report initially, because the solution is different than my earlier suggestion. If there is a separate table per month and each meter serial number has one record per table or a null, then all you have to do is a left join from the meter table to each monthly table. Then create one formula per month:

//{@Jan05}:
if isnull({Jan05.result}) then 1 //etc.

You would then insert a maximum on each formula and suppress the detail section. For the row total, use a formula like:

maximum({@Jan05}, {table.meterno}) + maximum({@Feb05},{table.meterno}) + //etc.

Since the dates relate to different tables, I don't think using a parameter makes much sense, although I suppose you could conditionally suppress certain columns based on a parameter, e.g.,:

//for August column:
not({Aug05.date} in {?daterange})

For the final column, you could use a formula like:
(
if {Jan05.date} in {?daterange} then
maximum({@Jan05}, {table.meterno})
) +
(
if {Feb05.date} in {?daterange} then
maximum({@Feb05}, {table.meterno})
)

Another approach would to create a command that would use a union all to put all the date fields into one column, then add a text identifier for the month, and then use a left join from the meter table to the command. Then you could use formulas as in my earlier suggestion. But I would try the new suggestion first.

-LB

 

[tektipsMem]

Thanks Madawc and LB. I have been trying LB's suggestion. Don't quite understand "insert a maximum on each formula". If I set maximum({@Jan05})=1, then all serial gave 1. Then I did maximum({@Jan05}, {table.meternumber}) and put in the serial number group footer, suppress detail. Somehow, the group serial number gave 0.

Looks like:

Serial
number Jan Feb
1 0 0
2 1 0
3 0 0
4 0 1

How do I get rid of the rows with the zeros.

I know I should have explain the full report initially, but I am new in CR and still trying to figure out the best report layout.

Is it possible to have the total count column as a subtotal of a group Room and total of another group Building? i.e. group Building > Room > serial number.

I have not try the daterange part yet, but will do that before attempting the left join command suggestion, which I am sure I will need help.

Thanks for your patience.
TTM

 

[lbass]

By inserting a maximum, I meant for you to right click on the formula {@Jan05}->insert summary->maximum at the group level. The reason for this is that at the detail level, the {@Jan05} formula will return 1 multiple times because of the multiple tables being used, and you only want to count the meter once if it has null results.

You can insert as many groups as you want and then insert summaries at each of the group levels (again by right clicking->insert summary). No need for summary formulas.

Also, note that you must use the left outer join first or nothing will work. You can't really immplement partial solutions like you are trying to do.

-LB

 

[tektipsMem]

LB

Followed exactly to insert max, but resulting report still shows 0 for serial numbers that has results, like the table I showed last time. How do I get a report which shows ONLY the serial (and count) with no results ?

TTM

 

[lbass]

I'm not sure what you are saying. Are you saying the results are incorrect or are you saying that you want to eliminate rows where there are all zeros? If the latter, then go to the section expert->highlight group section where summaries are located->x+2 and enter:

maximum({@Jan05}, {table.meterno}) + maximum({@Feb05},{table.meterno}) + //etc
= 0

In other words, use the total formula for the row to suppress the row if the total is zero.

If this isn't what you meant, you need to explain in more detail and provide sample data, preferably showing both detail and group level data.

-LB

 

[tektipsMem]

Thanks very much for your patience. Yes, I meant suppressing rows with zeros. The report is taking shape.

All ten tables together have about 10,000 records. However, it takes a long time to generate the report and the record count is in the order of 5,000,000. Why?

You suggested conditionally suppress months not in date range. Where do I put this formula?

//for August column:
not({Aug05.date} in {?daterange})

I have groups in this heirachy: site>unit>serial no. I have check off "keep together" for all groups, yet serial numbers still get split into different pages; they are not kept together under unit. i.e. I am getting

page 1:
Unit A serial 1
2
3
page 2:
serial 4
5 etc.

I want either Unit and serial in same page or

page 1:
Unit A serial 1
2
3
Page 2:
Unit A serial 4
5 etc.

Any suggestions?

TTM

 

[lbass]

//for August column:
not({Aug05.date} in {?daterange})

Right click on the maximum of the August formula in the group section where you are displaying the 0's and 1's->format field->common->suppress->x+2 and enter the formula there.

For the grouping issue, go to report->group expert->highlight the unit group->options->options tab->check "Keep group together". You could also check "Repeat group header on each page, in case the group is too large to fit on one page.

Regarding the number of records, it is a result of the number of tables and resulting row inflation. You could try using a union query in a command to "merge" the ten monthly tables. This would reduce the number of records. And then you would follow the suggestions in my first post. To do a union all, you would go to database->database expert->add command and enter something like:

SELECT `Meters`.`Serial number`,`Meters`.`Site`, `Meters`.`Unit`, `Meters`.`Date returned`, `Jan05`.`Meter Serial`, `Jan05`.`Result`, `Jan05`.`Date / Time`,'Jan05' as Month
FROM `Meters` `Meters` LEFT OUTER JOIN `Jan05` `Jan05` ON `Meters`.`Serial number`=`Jan05`.`Meter Serial`
UNION All
SELECT `Meters`.`Serial number`,`Meters`.`Site`, `Meters`.`Unit`, `Meters`.`Date returned`, `Feb05`.`Meter Serial`, `Feb05`.`Result`, `Feb05`.`Date / Time`,'Feb05' as Month
FROM `Meters` `Meters` LEFT OUTER JOIN `Feb05` `Feb05` ON `Meters`.`Serial number`=`Feb05`.`Meter Serial`
UNION All
SELECT `Meters`.`Serial number`,`Meters`.`Site`, `Meters`.`Unit`, `Meters`.`Date returned`, `Mar05`.`Meter Serial`, `Mar05`.`Result`, `Mar05`.`Date / Time`, 'Mar05' as Month
FROM `Meters` `Meters` LEFT OUTER JOIN `Mar05` `Mar05` ON `Meters`.`Serial number`=`Mar05`.`Meter Serial`
//etc.

A union would return only distinct records, but takes longer than a union all. I'm not sure which would work best here.

-LB

 

[tektipsMem]

LB,

Sorry to get back this late, thank you very much for your help. SQL Union all suggestions works fine. Thanks again.

TTM