Pull daily totals from a sub-report to a column in the main report

[Timmkatt]

CR 10...
I have a report which shows daily revenue totals by day and a subreport that shows refunds by day. The report and subreport data come from different tables. The report looks like this:

Mainline Daily Trips
Date Revenue Trips Passengers
10/1/2014 11,299.00 52 331

10/2/2014 10,878.00 52 322

Daily Refunds
10/1/2014 -145.00

10/2/2014 -218.00

Is there a way to pull the subreport daily refunds into a column in the main report so the report layout would look like this?

Mainline Daily Trips
Date Revenue Refunds Trips Passengers
10/1/2014 11,299.00 -145.00 52 331

10/2/2014 10,878.00 -218.00 52 322

Thank you in advance for any help you can offer.

 

[briangriffin]

This is an example of how a little bit of SQL can probably avoid the subreport altogether, speeding up the report and making it easier to write. If you're open to that approach, post the SQL from both the main report and the subreport and I'll try to make it work in a single query.

Under the Database menu you'll see Show SQL Query.

 

[Timmkatt]

Thanks here are the queries
Main Report:

SELECT "Trips"."ServiceType", "Trips"."EstTripStart", "Trips"."ID", "Trips"."Description", "Trips"."Passengers", "Trips"."AltPassengers", "Trips"."AltPassengers2", "Trips"."AltPassengers3", "Trips"."MaxPassengers", "Trips"."Direction", "Trips"."Driver", "Trips"."Status", "Trips"."Fare"
FROM "RES2SQL"."dbo"."Trips" "Trips"
WHERE ("Trips"."EstTripStart">={ts '2014-10-01 00:00:00'} AND "Trips"."EstTripStart"<{ts '2014-10-23 00:00:00'}) AND "Trips"."Driver"<>0 AND "Trips"."Status"<>N'N' AND "Trips"."ServiceType"=N'1'


Sub Report:

SELECT "Reservations"."ID", "Reservations"."WResID", "ReservationPayments"."PaymentTOD", "Reservations"."PaymentType", "ReservationPayments"."PaymentAmount", "ReservationPayments"."PaymentStatus", "CCTransactions"."TransactionType", "CCTransactions"."Status", "CCTransactions"."ApprovalCode", "ReservationPayments"."CCTransID", "CCTransactions"."ProcTransID", "Reservations"."ServiceType"
FROM ("RES2SQL"."dbo"."ReservationPayments" "ReservationPayments" INNER JOIN "RES2SQL"."dbo"."Reservations" "Reservations" ON "ReservationPayments"."ResID"="Reservations"."ID") INNER JOIN "RES2SQL"."dbo"."CCTransactions" "CCTransactions" ON "ReservationPayments"."ResID"="CCTransactions"."ResID"
WHERE ("ReservationPayments"."PaymentTOD">={ts '2014-10-01 00:00:00'} AND "ReservationPayments"."PaymentTOD"<{ts '2014-10-24 00:00:00'}) AND "CCTransactions"."TransactionType"=N'M' AND "ReservationPayments"."PaymentAmount"<0 AND "CCTransactions"."Status"=N'Y' AND ("Reservations"."ServiceType"=N'1' OR "Reservations"."ServiceType"=N'2')

 

[briangriffin]

There are a lot of fields in both queries - are they all displayed on the report? Is all the detail displayed too, or is this just a summary report by day? For instance, you're pulling in Driver - that would only be meaningful in the detail.

 

[Timmkatt]

Not all of the fields are displayed in the report and are used to filter the data. For example the driver field is used so the report does not count any trips that did not have a valid driver employee number (1 to whatever) assigned which means it did not run "Trips.Driver is not 0", "Reservations.Service Type" limits the report to each of our 4 Arizona markets and so on. The report basically displays as a summary by day but the end user has the option to drill down to get the detail. The spec on this report requires all of the information in the query's.

 

[briangriffin]

OK, it's going to look something like this (I may have left off a few fields that you actually need). Instead of linking tables in the Database Expert, you'll create a command object to feed the main report:

SELECT "Trips"."ServiceType", 
"Trips"."EstTripStart", 
"Trips"."ID", 
"Trips"."Description", 
"Trips"."Passengers", 
"Trips"."AltPassengers", 
"Trips"."AltPassengers2", 
"Trips"."AltPassengers3", 
"Trips"."MaxPassengers", 
"Trips"."Direction", 
"Trips"."Driver", 
"Trips"."Status", 
"Trips"."Fare",
[b]Refunds.RefundTotal[/b]
FROM "RES2SQL"."dbo"."Trips" "Trips" [b]left outer join

(select PaymentTOD,
sum(PaymentAmount) as RefundTotal

from ReservationPayments RP INNER JOIN "RES2SQL"."dbo"."CCTransactions" CCT
	on RP.ResID = CCT.ResID
 
where RP.PaymentTOD >={ts '2014-10-01 00:00:00'} [b] <-- Start Date Parameter[/b]
and RP.PaymentTOD < {ts '2014-10-24 00:00:00'}    [b]<-- End Date Parameter[/b]
and RP.PaymentAmount < 0
and CCT.Status = 'Y'

group by PaymentTOD) Refunds

on "RES2SQL"."dbo"."Trips".EstTripStart = Refunds.PaymentTOD
[/b]
WHERE ("Trips"."EstTripStart">={ts '2014-10-01 00:00:00'} 
AND "Trips"."EstTripStart"<{ts '2014-10-23 00:00:00'}) 
AND "Trips"."Driver"<>0 
AND "Trips"."Status"<>N'N' 
AND "Trips"."ServiceType"=N'1'

This will bring each day's refund total into each line of detail, so if you sum it for each day's group the total would be inflated. So instead use a min or max, it doesn't matter since the values are all the same for each day.

You still need the subreport for other reasons. You'll need to create the date prompts within the command object dialog, then they will appear in the parameter list as usual.

This may seem like a long way to go, but once you get the hang of it you'll save a lot of time. I almost never use subreports because invariably I can get the entire dataset into a single SQL query - you'll spend more time on the script, but a lot less time doing report design.