Mixing Different Types of Detail Information in Same Report

[mhepner]

Hello,
First I'd like to thank everyone who has helped me in the past. This is a great site for how-to information. I am creating a Customer Activity Report where I am displaying information from three tables: Customer, Inbound (Ticket), and Payment (for Customer Prepayments). The fields I want to display are Customer ID and Starting Balance from the Customer Table, a Payment Amount and Date from the Payment Table, and a Transaction Amount and Transaction Date from the Inbound Table. Displaying Payment Information then Inbound Information for a customer I have no trouble with but the Customer wants me to intersperce the two different types of detail lines by date. So, for example you might have:

Customer Tkt TktDte Amt PmtDte PmtAmt
Customer 1:
11/14/03 $10,000
1 11/15/03 $100.00
2 11/16/03 $500.00
11/17/03 $15,000
etc.

I'm not sure how to mix the two detail lines together. Any help would be appreciated. Thanks.

 

[IdoMillet]

Use a UNION statement to combine Payments and Tickets into a single result set. Something like:
------------------------------------------------------
SELECT Customer_ID, Payment Amount, Date, "Payment" as Trans_Type FROM Payment
UNION ALL
SELECT Customer_ID, Ticket Amount, Date, "Ticket" FROM Tickets;
------------------------------------------------------

You can then sort the result set by date within Customer ID
and use the new "Trans_Type" column to indicate which ones are Tickets and which ones are Payments.

Cheers,
- Ido


CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[synapsevampire]

Not sure how you determines that these tables couldn't just be joined and grouped, Ido?

It seems the issue is grouping by pmtdte, but only showing details within for tktdte >= pmtdte

I think that this might require a subquery on the database side, or subreport on the Crystal side.

On way might be to use a main report returning only the pmtdte data grouped (on server side) and summarized, while the subreport would return the relevant rows based on a date range parm passed at that date group level.

Otherwise I think that you'll need 2 subreports to determine the next ptedte to cap the subreport rows returned.

-k

 

[IdoMillet]

Kai,

The requirement is to sort the detail by transaction date so that Payment transactions and Ticket transactions are interweaved. I believe a subreport approach is not going to achieve this while a UNION can.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com