Multiple 'details' fields on a report

[martindell]

OK, I know I'm being dumb but I'm new to Crystal and really stuck - I'd appreciate any advice on the following.

I'm creating a report to issue a statement of account to a customer. It needs to detail items ordered and payments received (often multiple instances of both) and work out whether there is a balance owed or overpayment (hah!)

I'm querying three tables, orders, items and payments, where rows on the items and payments tables relate to a unique order id row. Basically, payments and items know which order id they belong to so I'm reporting on items and payments that belong to a particular order id.

I can't get a report to work that displays all the payments AND all the items - either of them but not both at the same time. This is a subreport in itself otherwise I'd use a subreport to do this.

I've done all the help files and examples I can find but am getting nowhere.

Thanks in advance
Martin

 

[fredp1]

I Hope that also helps Martin... becareful if you need to filter on the RHS table as you will 'miss' records.

The method that LBASS showed works... but you may get a performance hit. The Subreport method might just be as slow if not slower, depending on the section its added to the report(the details section will be the slowest).

I've been talking to CR tech support today and it seems that achieve complex queries with subquires and stuff that runs fast, is via a Stored Proc. If I find some more info I'll post some details on the other thread that I started.

http://www.tek-tips.com/viewthread.cfm?SQID=852317&SPID=767&page=1

Thanks to KATY for hanging in there and thanks for your responses LBASS.

Cheers,
Fred

 

[Katy44]

I'll bear that in mind... I have never run a report off a SP before but I do have a lot of slow running reports with subqueries in them, that might speed them up a bit.

Thanks!

 

[pticarl]

Fredp,

On an unrelated look at the SQL Server Online Book I ran across this entry in the 'View Hints' area.

There may be predicates involving only one of the joined tables in the ON clause. Such predicates also may be in the WHERE clause in the query. Although the placement of such predicates does not make a difference in the case of INNER joins, they may cause a different result if OUTER joins are involved. This is because the predicates in the ON clause are applied to the table prior to the join, while the WHERE clause is semantically applied on the result of the join.

Not sure if this helps or not but seemed to apply here, but if that is the case it makes me wonder why evaluating for isnull of the RHS field does not work?

 

[Katy44]

I got myself tied up in knots over that - but lbass's example explains it - the solution DOES bring back the LHS values where the RHS is null, but doesn't bring back LHS values where the RHS isn't null BUT doesn't fit the criteria. But as lbass said you solve this by not using criteria at all, but a formula to display the RHS values as required.
...
I think...

 

[lyanch]

Hmmm... all my tests show that *if* crystal sends all the selection formula to the server, that this works.

Test:

Original SQL:

SELECT coms_order_item.order_item_id, coms_status_order_item.order_item_id, coms_status_order_item.activate_complete_date
FROM ODBPS.dbo.coms_order_item coms_order_item, ODBPS.dbo.coms_status_order_item coms_status_order_item
WHERE (coms_order_item.order_item_id*=coms_status_order_item.order_item_id) AND (coms_order_item.order_item_id>=130000 AND coms_order_item.order_item_id<=131000)

selection formula: {coms_order_item.order_item_id} in 130000 to 131000

Records returned: 937

Step one: criteria on outer side:

SQL:


SELECT coms_order_item.order_item_id, coms_status_order_item.order_item_id, coms_status_order_item.accounting_complete_date, coms_status_order_item.activate_complete_date
FROM ODBPS.dbo.coms_order_item coms_order_item, ODBPS.dbo.coms_status_order_item coms_status_order_item
WHERE (coms_order_item.order_item_id*=coms_status_order_item.order_item_id) AND (coms_order_item.order_item_id>=130000 AND coms_order_item.order_item_id<=131000) AND coms_status_order_item.activate_complete_date>={ts '2004-06-03 00:00:00'}

selection formula: {coms_order_item.order_item_id} in 130000 to 131000
and
{coms_status_order_item.activate_complete_date} > Date(2004,06,02)

records returned: crystal narrowed to 21 (its still 937 in regular SQL window)

Step three: add the isnull

SQL:

SELECT coms_order_item.order_item_id, coms_status_order_item.order_item_id, coms_status_order_item.activate_complete_date, coms_status_order_item.order_item_status_id
FROM ODBPS.dbo.coms_order_item coms_order_item, ODBPS.dbo.coms_status_order_item coms_status_order_item
WHERE (coms_order_item.order_item_id*=coms_status_order_item.order_item_id) AND (coms_order_item.order_item_id>=130000 AND coms_order_item.order_item_id<=131000) AND (coms_status_order_item.order_item_status_id IS NULL OR coms_status_order_item.activate_complete_date>={ts '2004-06-03 00:00:00'})

Selection formula: {coms_order_item.order_item_id} in 130000 to 131000
and


(isnull({coms_status_order_item.order_item_status_id}) or {coms_status_order_item.activate_complete_date} > Date(2004,06,02))

REcords returned: 937, RHS records returned: 21

*NOTE* you have to put the isnull against a RHS field that will NEVER be null if a record exists, otherwise you will add null field records to your selection.

In sybase I have to use the ODBC driver to do this now, because the native driver doesn't send "ors" to the database correctly since I switched to 9.

Lisa

 

[lyanch]

All..

I take it back.. well sort of.. This only works for Sybase.. I tried Oracle, SQL Server and postgresSQL. But the logic is the same, the part that fails is:

*If* the original SQL before adding the isnull OR addition, returned the right dataset outside crystal..(this is from another thread discussing the same thing) which is shown by this line:

records returned: crystal narrowed to 21 (its still 937 in regular SQL window)



They all return the same thing that crystal does. Sybase is the only one that always returns everything on the LHS no matter what the criteria on the RHS is..

When the *if* above is true, it is crystal that is removing the records, not the database..

Lisa

 

[fredp1]

Lisa, Thanks for testing and clear that up.
It should explain why some threads suggest that by adding the ISNULL to the RHS, the record selection formula would return all the records on LHS.

It has confussed me and a few other people.

Could you clear up one more thing...

With Sybase, your example show that the "JOIN" is done by the WHERE clause using *= syntax.
SQL Server Books On Line calls this the "Legacy" syntax. SQL Server would do the join using the FROM clause and the LEFT OUTER JOIN syntax. This is using ANSI SQL0-92 syntax.

Which syntax did Oracle and Progress use?
Thnaks again.
Fred

 

[lyanch]

For oracle:

"TABLE"."ITEM_ID" = "TABLE2"."ITEM_ID" (+)


For postgresSQL (not progress)

{oj "qcit_session_complete_event" "qcit_session_complete_event" LEFT OUTER JOIN "qc_session" "qc_session" ON "qcit_session_complete_event"."session_id"="qc_session"."session_id"}