Parameters Show All Dates For Given Range

[DarkAngel]

Using SCR8

Trying to create a report that shows all the dates from TABLE1 for a given week (via parameter fields) even if there is no corresponding record in TABLE2. The following is what I have in the Select Expert when you show the formula.

{TABLE2.DEPT} = {?Department} and
{TABLE2.ITEM_NO} = {?Item Number} and
(if {?Color} <> 'ALL' then
{TABLE2.Color} = {?Color}
else
True) and
(if IsNull ({?Size}) then
{TABLE2.SIZE} = {?Size}
else
True) and
{TABLE1.DATE} in {?StartDate} to {?EndDate}

I have moved the Department and Item Number parameters to the end but got the same results. Perhaps the Select Expert is not the best place to do this? Should it be written into the SQL statement instead?

The report is based two tables with a left outer join from TABLE1.DATE to TABLE2.DATE.

 

[kenhamady]

You are nullifying your outer join by placing criteria on the outer table.

For example your first rule is:
{TABLE2.DEPT} = {?Department}

This can't be true on any record where there is no matching T2 record.

Is the T1 table simply a list of dates to ensure that every date exists in the report?

I also think that your If-Then criteria should have the True first rather than last. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[DarkAngel]

Hi Ken,

Thank you for your response. Yes, the T1 table is just a list of dates. Is there any other way to print every date for a given week, even if there is no matching T2 record?

DarkAngel

 

[kenhamady]

It is difficult if you need to put criteria on the second table.


Subreports would be one way. You could link a details subreport to each date. You could still print the date if the subreport returned no data. Depends on what the report needs to look like. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[DarkAngel]

Haven't worked with subreports yet, but there is no time like the present. I'll give it a whirl.

Thanks Ken!