Urgent: Help on Group/Record Selection Criteria Reqd 2

[CPKB]

I have 3 tables (CUSTOMER,LOCATION,ORDERS). The links are from CUSTOMER to LOCATION (Equal join). LOCATION to ORDERS(Left Outer join). I need to group data in the order
Group 1-CustomerID
Group 2-Location
Group 3-Order
Now, ORDER table has a field called ORDER_DATE(nullable).
I need to print data out in the report so that I get details of all orders for a location, whose ORDER_DATE is = 'a given date'(input parameter field).
The issue is as follows:
1) If I specify the above criteria in the record selection formula, and a customer has no orders with the given date, then details of the entire customer, like customer and location are not printed out(group 1 and group 2), and if no customer has any order with the given date , I get an empty report.
I need to get the details of Group 1 and Group 2 out so that I can indicate the value of zero Group 3 records.

 

[Nuffsaid]

Hi,

Try linking your tables this way,

Orders to Location to Customer. All with equal joins. (assumeing you never have orders that are not associated with locations and locations that are not associated with customers).

Nuffsaid.

 

[IdoMillet]

Change the condition from:

ORDER_DATE is = 'a given date'(input parameter field).

to

IsNull(Order_Date) OR ORDER_DATE is = 'a given date'

Cheers,
- Ido

ixm7@psu.edu

 

[Nuffsaid]

Just another thought....

If you have NO orders for a specific date... nothing will appear on the report. So I may have "jumped the gun" with my previous post. You might want to try a right outer join from Orders to Locations. Sorry!

Nuffsaid.

 

[IdoMillet]

Nuffsaid,

The indicated objective was to show cases where a Customer-Location had no orders (with a zero).

If you join from orders to location you will not see locations without orders.

Hence, I still suggest CPKB uses what I proposed above.

If the query gets submitted directly as SQL,
an alternative is to use an "ON" rather than a "WHERE" clause for the date condition.

Cheers,
- Ido

ixm7@psu.edu

 

[Nuffsaid]

IdoMillet

I realized this after I submitted my post, hence my second post.

According to what I have read, my second post "should" work, but I can't seem to get it to work with my test data.
Any ideas????

Nuffsaid.

 

[Ngolem]

this item should be in the record selection formula for the outter join from Location to Orders

(isnull({orders.Order_date}) or
{orders.Order_date} = {?ParmDate} ) and

ALSO make sure that {?ParmDate} has the same data format as
{orders.Order_date}

this should do the trick

Jim

 

[CPKB]

I did try these options above...
Perhaps I will be more clear..
CUSTOMER TABLE (CUST_ID, NAME)
LOCATION TABLE(ID,LOCATION NAME, CUST_ID_FK)
ORDERS TABLE (ID,ORDER_AMT,ORDER_DATE,LOC_ID_FK)
ORDER_DATE can be null.

I need to group like this....
For All customers in the DB,
For ALL location for this customer
For all orders that were dated = Input_date
Further groups... follow...

a) Date Format is correct
b) I do not want orders with NULL date fields.
c) I tried links both the ways as suggested.
d) Some customers may not have any orders in the order table at all. However I still need to print the customer and location details out and indicate that there were no orders.
e) There are tables beneath the Order table which I need to further group by etc after this problem is sorted out.Hence starting from the ORDER Table may give problem..Also if no orders exist for a customer, I may still not get any record? The order table has nearly a 95,000 records.
Thanks for your replies, I hope you will respond to this mail..

 

[CPKB]

Your statment said "should be in the record selection formula for the outer join from Location to Orders",
How can I have a record selection formula specific to a link? Is the record selection formala not effective for the entire report? If not, could you please tell me how to set this up? Thanks

 

[Ngolem]

ok...now we are getting somewhere....

let me see if I have this correct

You want all records of customer and location returned...along with orders as long as those orders have a not null orderdate


I think that this would be easier to do if the Order portion of the report was put into a subreport linked to main report by Location.id and Order.id....then the order_Date would not interfer with the data in the main report

 

[IdoMillet]

CPKB,

Have you tried what I suggested above?

Again, just change your record selection
condition to allow null order dates:

IsNull(Order_Date) OR ORDER_DATE = 'your parameter'

You need this because all the cases where a Customer/Location combination doesn't have an order
would return nulls in the Order columns (as expected from an Outer Join).

Cheers,
- Ido
ixm7@psu.edu

 

[CPKB]

Hi Nuffsaid,IdoMillet and Ngolem,
The suggestion by Ngolem (to use a subreport) is giving me the required result.
Thanks a lot.
This is the first time I have discussed topics on this forum/site. All your suggestions were helpful and an eye-opener.
I look forward to viewing all your replies..
Merry Christmas and a Happy New Year!
God bless you all..
Thanks
CPKB