Board index » delphi » Calculated field in a grid

2005-07-17 05:26:01 AM
delphi60
Howdy people,
I've got an grid component attached to a datasource component attached
to an IBQuery component. The IBQuery.SQL looks something like this:
SELECT MYNAME, SUM(MYSALES) AS TOTALSALES
FROM MYTABLE
GROUP BY MYNAME
I want to add a calculated field column to the grid. The field would
show what percentage of the total sum of MYSALES was sold by each MYNAME
- something like TOTALMYSALES / SUM(TOTALSALES) * 100. Think I can work
out how to do this mindnumbingly simple task? Nope.
What I have been looking at is adding a new field to the IBQuery, but it
looks like the calculation won't work because I am using a field value
with an aggregate. Or possibly I am typing the expression in the wrong place.
Can anyone offer me an answer here?
D8, FB2 if that makes a difference...
Regards,
Laurie
 

You cannot get the grand total and the total by MYNAME in a single
query. Use a stored procedure that gets the grand total first then
executes your existing query in a FOR SELECT loop so you can perform
the calculation within the loop.
--
Bill Todd (TeamB)
 

Bill Todd writes:
another query which updated an edit field edtGROSSSALES, could I use
this field in to find the percentages (I'm referring to a non-SQL
solution here)?
I've never done a FOR SELECT loop, so <cough>I'm starting a new thread
for this...
Regards,
Laurie
 

It depends on what you mean by "edit field" and whether you want to
calculate the percentage in a SELECT or in code in the client
application using a calculated field.
--
Bill Todd (TeamB)
 

Hi Bill, thanks for sticking with me on this one
field contains the GROSSSALES figure, and I want to display in the Grid
a field displaying MYSALES / GROSSSALES * 100.
Regards,
---=L
 

Assuming the two query components are named SumQry and TotalQuery all
you need to do is add a calculated field to SumQry and add the
following to the OnCalcFields event handler.
SumQry.FieldByName('CalcFieldName').AsFloat :=
(SumQry.FieldByName('Total').AsFloat /
TotalQuery.FieldByName('GrandTotal').AsFloat) * 100;
--
Bill Todd (TeamB)
 

Bill Todd writes:
use the query values.
Thanks for this Bill,
---=L
 

When working with data aware controls always use the value in the field
object, not the control.
--
Bill Todd (TeamB)