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


LumpyElbowAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
The Expression property of the TQRExpr and TQRGroup components is read only the beginning of the report and can not be changed while the report is running.  You can use the OnPrint event to override the output of the TQRExpr component or set its Expression property before to call the Quickrep.preview or .print....

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LumpyElbowAuthor Commented:
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 AccalaiSenior developer, analyst and customer assistance Commented:
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....

CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

LumpyElbowAuthor Commented:
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 AccalaiSenior developer, analyst and customer assistance Commented:
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)...
LumpyElbowAuthor Commented:
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 AccalaiSenior developer, analyst and customer assistance Commented:
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 AccalaiSenior developer, analyst and customer assistance Commented:
Another thing....i'd assign quickrep1.dataset instead of quickrep1.alldataset....
LumpyElbowAuthor Commented:
Now the '0' disappears and nothing is printed
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
assign quickrep1.dataset instead of alldataset an tell me about it
LumpyElbowAuthor Commented:
did that too
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
also changed the qrgroup.master and expression?
LumpyElbowAuthor Commented:
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 AccalaiSenior developer, analyst and customer assistance Commented:
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;
LumpyElbowAuthor Commented:
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 AccalaiSenior developer, analyst and customer assistance Commented:
yes i meant that....have you got an icq account?
LumpyElbowAuthor Commented:
if  Windows messenger counts
LumpyElbowAuthor Commented:
my email is the_melonheads@hotmail.com if you want to jive
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
sorry but my W messenger don't work.....
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.