John 1. Create a VIEW - assuming the common factor of the 3 rows in your sample is that they are all the same ITEM number:
CREATE VIEW ItemTotals (Item,TotCost) AS SELECT Item,SUM(TotCost) FROM tablename (I suspect you'll also need another column in here to get the right totals since you only want totals for this itme on this transaction or something like that) 2. Either include this view in 2nd view on which the report is based so you can get at the item totals, or do a lookup variable in the report. 3. In the report create these variables: vCount = ((IFNULL(.vCount,0,.vCount)) + 1) vQty = (IFEQ(.vCount,1,QTY,.vQty)) 4. Create a breakpoint oin the report on the ITEM value 5. Reset the above two variables at the break. 6. Create expression for the average: vAverage = (TotCost / .vQty) David Blocker [EMAIL PROTECTED] 781-784-1919 Fax: 781-784-1860 Cell: 339-206-0261 ----- Original Message ----- From: "John Docherty" <[EMAIL PROTECTED]> To: "RBG7-L Mailing List" <[EMAIL PROTECTED]> Sent: Monday, August 30, 2004 9:16 PM Subject: [RBG7-L] - RE: [RBG7-L] Calculating Variables Using Sums in Reports > Thanks for the help with the vertical lines - however another question: In a > report I wish to calculate the average cost for an item which is based on > the sum of the cost of a series of subitems and the quantity for the first > subitem. As the SUM etc commands no longer seem to be accessible (having > been replaced by DBCALC) any advice on how to do this would be appreciated. > The data is as follows:- > > QTY subitem COST > 5 a $50 > 10 b $100 > 15 c $10 > > Total cost: $160, and the average cost I want to show is > $160/5 > > All that is to appear in the report are the details associated with the > first subitem (a) (QTY and a description), the total cost including all > subitems and the average cost based on the quantity for the first subitem. > While I have no problem generating the total cost using the appropriate > break headers I cannot see how I can calculate the average. (This used to be > possible in DOS where the 'sum of' was an accessible variable, but > apparently no longer.) > > Thank you. > > Regards, > > John Docherty > >
