Grouping Revisited...

[bitsmith2k]

Hey guys,
I've been working on a legacy ticketing system, and added CR reporting to it. Everything was fine and dandy until the client through a loop at us which brings us to the information that I currently have.

I have the following information from 3 different tables:

TransID CurrencyType PaymentMethod Amount EventSite
------- ------------ ------------- ------ ---------
1 CAN CASH 17.20 SITE_A
2 CAN CASH 14.10 SITE_A
1 CAN CASH 17.20 SITE_B
3 US CASH 10.50 SITE_B

My output is this:
CAN CASH $48.50
US CASH $10.50

This is what I want the output to resemble:
CAN CASH $31.30
US CASH $10.50

I have allot of other information available to me in the tables, but this information is what I think is relevent.

Thanks in advance..

Mike

 

[bitsmith2k]

I should've added that I can't use a subreport to solve this, as this report is already used as a subreport.

I'm using CR 8.5 and SQL Server 7

 

[lyanch]

Depending on the rest of your data.. setting the database -> select distinct to checked might solve your problem.

Lisa

 

[bitsmith2k]

Thanks Lisa,

But because the EventSite's are different the rows come up as distinct

 

[lyanch]

Can you order by trans ID? then you could place a running total to evaluate on change of trans ID.

Lisa

 

[Ngolem]

I suppose the question is...on what basis are you eliminating some of the data?

From the limited data provided that isn't clear.

Is it because it is Transit ID = 1 and EventSite B?
Is it because it is in $CAN and EventSite B?

Why are you not grouping on Eventsite

EventSite A
Can Cash $31.30
EventSite B
Can Cash $17.20
US Cash $10.50

or is the data for site B Can just WRONG and should not be there???

Jim Broadbent