Try creating a view that does not GROUP BY and achieve the grouping by placing totals in a break footer. Break on month. Use the WHERE clause in the PRINT command to restrict to whatever year youre looking at. Use UNION in the view definition to separate billed, paid and cancelled:
CREATE VIEW NewV (TranDate,IMonth,BilledAmt,PaidAmt,CanceledAmt) AS + SELECT InvDate,(IMON(InvDate)),InvAmt,0,0 FROM Table WHERE (it's a bill) + UNION + SELECT PayDate,(IMON(PayDate)),0,PAmt,0 FROM Table WHERE (it's a payment) + UNION + SELECT PayDate,(IMON(PayDate)),0,0,InvAmt FROM Table WHERE (it's a canceled) Set up a break on TranMonth. PRINT TheReport WHERE TranDate BETWEEN StartDate AND EndDate. TheReport has a break on IMonth. Regards, Stephen Markson ForenSys The Forensic Systems Group www.ForenSys.ca 416 512 6950 ________________________________________ From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Jan Barley Sent: October 9, 2008 1:44 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Group By Problems Buddy, Its actually more complicatd than that. I am trying to generate a report to show whats been billed, paid, and canceled by month. My problem is the billing amount uses one date (InvDate) and the paid and canceled uses another (PayDate). I want those figures to all go under the same month. I have set up 3 different views - DuesBill, DuesPaid, and DuesLost. DuesBill is (tmon(InvDate)) and (sum(InvAmt)) DuesPaid is (tmon(PayDate)) and (sum(PAamt)) DuesLost is (tmon(PayDate)) and (sum(InvAmt)) what I am trying to do is combine all three views into one so I can look at these figures by month. The new view would consist of: DuesMonth,BillAmt,PaidAmt,LostAmt every time I try to combine these views I keep getting the illegal column specification! Any thoughts are appreciated!!! Jan
