w424637
Posts: 2
|
| Posted: 03/24/2009, 2:33 AM |
|
Hi
I have a simple query which produces a small table like
Company Sales Profit ROI
ROI is a calculated field in the query (Profit / Sales)
Problem is when I do a GrandTotalROI the only options I have are to Sum or average the ROI column - both wrong.
What I need is the GrandTotalROI to be a calculated field based on the GrandTotals of Profit and Sales.
Can I do this.
Now I could write a union query and bring in the data as a Grandtotal line correctly calculated. Trouble is it shows it then as part of my data table and not on the GrandTotal line.
please help.
Simon
|
 |
 |
paulolg
Posts: 7
|
| Posted: 03/24/2009, 5:45 AM |
|
Hi Simon,
Can you show me your sql please? Oracle. sybase, sqlserver..???
regards
paulolg
|
|
|
|
w424637
Posts: 2
|
| Posted: 03/24/2009, 6:59 AM |
|
SELECT racedata.TrackName, Sum(racedata.Bet) AS SumOfBet, Sum(racedata.Return) AS SumOfReturn, Sum([return])/Sum([bet]) AS ROI
FROM racedata
GROUP BY racedata.TrackName;
Using MSAccess
Now this produces a table
4 cols 2 rows. ROI calculated correctly.
If I put this into a report though and want a GrandTotal I have the issue that the ROI column should be the GrandTotalReturn/GrandTotalBet.
Currently I can Sum it - wrong or Avg it - wrong again - it needs to recalculate itself at the Grandtotal level.
Hope this explains the issue more.
Now I could add a union query in:
UNION
SELECT 'Total' as TrackName, Sum(racedata.Bet) AS SumOfBet, Sum(racedata.Return) AS SumOfReturn, Sum([return])/Sum([bet]) AS ROI
FROM racedata
This correctly includes the ROI calculation.
Trouble is in a report now I have three rows and it isnt defined / formatted as a total row on my final report.
help really appreciated.
I've read about BeforeShow codes etc but not clear on how these work.
thanks
Simon
|
|
|
|
|
