asked on

TQRExpr SUM expression returning 'zero' in QuickReport

Hi

I'm writing an invoicing engine that will allow the user to choose any number of their clients and then print an invoice for each. The back end is Access.

On the QReport Form I have a
- group Header (FooterBand prop links to Group Footer, Expression is dtsInvoice.Company so it groups by company)
- detail contains the rate for each line item and more
-Group footer: has a 'total' label and an QRExpr1 that is set to SUM(dtsInvoice.rate)

Here is my sql query

****
with dtsInvoice do
begin
Connection := conInvoice;
CommandText := 'SELECT Activity.description, Appointment.rate, Appointment.aptDate, ' +
'Appointment.aptTime, Contract.company, Contract.address, Contract.city, Contract.prov, ' +
'Contract.postal, Client.firstName, Client.lastName, CustomApptCheckbox.value, ' +
'CustomApptEditbox.value, CustomContract.controlCaption ' +
'FROM ((((Contract ' +
'INNER JOIN (Client ' +
'INNER JOIN (Activity ' +
'INNER JOIN Appointment ON Activity.pkidActivity = Appointment.fkidActivity) ' +
'ON Client.pkidClient = Appointment.fkidClient) ' +
'ON Contract.pkidContract = Appointment.fkidContract) ' +
'LEFT JOIN CustomAppointment ON Appointment.pkidAppointment = CustomAppointment.fkidAppointment) ' +
'LEFT JOIN CustomApptCheckbox ON CustomAppointment.pkidCustomAppt = CustomApptCheckbox.fkidCustomAppt) ' +
'LEFT JOIN CustomApptEditbox ON CustomAppointment.pkidCustomAppt = CustomApptEditbox.fkidCustomAppt) ' +
'LEFT JOIN CustomContract ON Contract.pkidContract = CustomContract.fkidContract ' +
'WHERE Contract.Company IN (' + sSQL + ')' +
'AND Appointment.aptDate BETWEEN #' + startDate + '# AND #' + endDate + '# ' +
'ORDER BY Contract.company';
Open;
txtDetailContract.DataField := 'company';
txtRate.DataField := 'rate';

end;
*****

here is the before print
******
procedure TfrmInvoice.QuickRep1BeforePrint(Sender: TCustomQuickRep;
var PrintReport: Boolean);
begin
QuickRep1.AllDataSets.Add(dtsInvoice);
QRExpr1.Master := dtsInvoice;
QRExpr1.Expression := 'SUM(dtsInvoice.rate)';
end;
********
I have played with the beforePrint setup (changed the master, used every combo I could think of in SUM(xxxxx) but it always prints '0' (It does group correctly though)

Any Takers?? Thanks

ASKER CERTIFIED SOLUTION

Ferruccio Accalai

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LumpyElbow

ASKER

But doing this prints '2' after each grouping??

procedure TfrmInvoice.QuickRep1BeforePrint(Sender: TCustomQuickRep;
var PrintReport: Boolean);
begin
QuickRep1.AllDataSets.Add(dtsInvoice);
QRExpr1.Master := dtsInvoice;
QRExpr1.Expression := '2';//'SUM(dtsInvoice.rate)';
end;

Ferruccio Accalai

What do you mean? ARe you tring that and it's happening so?

Btw i hade the same problem too a few time ago...
I had to assign differents datasets to the expression depending on some settings, so what i found was that assigning those in Before print the dataset property of tqrexpr became nil, just because it was nil on quickrep preview or print, so the Espression property (string) was always zero value....

I solved it assigning the TQRExpr properties before the QuickRep preview....

LumpyElbow

ASKER

I thought that was what you were getting at in your first post so I tried it. I still get zeros if I set the properties before the preview call. It seems to behave the same before the preview call or in the before print. This has flushed a week of my life

Ferruccio Accalai

and does this rate value really exist in the query result? (i know that's a stupid question, but maybe there's something wrong in grouping)...

LumpyElbow

ASKER

yeah it's real but I wasn't sure how much I had to qualify it. (dtsInvoice.rate vs rate)

Because I'm setting all my db connections via an INI file that is called dynamically I can't set the properties in the object inspector so I dropped a dummy dataset on the report and used the object inspector to set everything including the SUM expression, so I was sure I wasn't missing something really obvious. It still didn't work, maybe it is something obvious :(

Ferruccio Accalai

mmm....just re-reading the whole task....
set QRExpr1.Master := QuickRep1; the master should be quickrep1, not dtsInvoice...
QrExpr1.Expression = 'Sum(rate)'; //withou dstinvoice. it's just assigned in the quickrep1.dataset...

Ferruccio Accalai

Another thing....i'd assign quickrep1.dataset instead of quickrep1.alldataset....

LumpyElbow

ASKER

Now the '0' disappears and nothing is printed

Ferruccio Accalai

assign quickrep1.dataset instead of alldataset an tell me about it

LumpyElbow

ASKER

did that too

Ferruccio Accalai

also changed the qrgroup.master and expression?

LumpyElbow

ASKER

this is how it looks now (I also commented this out and did it before the preview call) prints nothing

procedure TfrmInvoice.QuickRep1BeforePrint(Sender: TCustomQuickRep;
var PrintReport: Boolean);
begin
QuickRep1.DataSet := dtsInvoice;
QRExpr1.Master := QuickRep1;
QRExpr1.Expression := 'SUM(rate)';
end;

Ferruccio Accalai

Of course because the group header still points to the dstinvoice as master...
add those declaration in before print for qrgroup too....
procedure TfrmInvoice.QuickRep1BeforePrint(Sender: TCustomQuickRep;
var PrintReport: Boolean);
begin
QuickRep1.DataSet := dtsInvoice;
QRExpr1.Master := QuickRep1;
QRExpr1.Expression := 'SUM(rate)';
qrgroup.master := Quickrep1;
qrgroup.expression := 'Company'

end;

LumpyElbow

ASKER

I'm a little lost on the last post. by qrgroup you mean my TQRGroup component (grpHeader)? I can't reference it in code but the 2 properties that you have added are set that way in the object inspector. Still no go

Ferruccio Accalai

yes i meant that....have you got an icq account?

LumpyElbow

ASKER

if Windows messenger counts

LumpyElbow

ASKER

my email is the_melonheads@hotmail.com if you want to jive

Ferruccio Accalai

sorry but my W messenger don't work.....