Solved

TQRExpr SUM expression returning 'zero' in QuickReport

Posted on 2003-11-20
21
2,218 Views
Last Modified: 2007-11-27
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


0
Comment
Question by:LumpyElbow
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 9
21 Comments
 
LVL 22

Accepted Solution

by:
Ferruccio Accalai earned 300 total points
ID: 9817190
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....
0
 

Author Comment

by:LumpyElbow
ID: 9821003
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;
0
 
LVL 22

Expert Comment

by:Ferruccio Accalai
ID: 9821093
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....

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:LumpyElbow
ID: 9821391
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
0
 
LVL 22

Expert Comment

by:Ferruccio Accalai
ID: 9821428
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)...
0
 

Author Comment

by:LumpyElbow
ID: 9821502
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 :(
0
 
LVL 22

Expert Comment

by:Ferruccio Accalai
ID: 9821565
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...

0
 
LVL 22

Expert Comment

by:Ferruccio Accalai
ID: 9821583
Another thing....i'd assign quickrep1.dataset instead of quickrep1.alldataset....
0
 

Author Comment

by:LumpyElbow
ID: 9821584
Now the '0' disappears and nothing is printed
0
 
LVL 22

Expert Comment

by:Ferruccio Accalai
ID: 9821590
assign quickrep1.dataset instead of alldataset an tell me about it
0
 

Author Comment

by:LumpyElbow
ID: 9821598
did that too
0
 
LVL 22

Expert Comment

by:Ferruccio Accalai
ID: 9821621
also changed the qrgroup.master and expression?
0
 

Author Comment

by:LumpyElbow
ID: 9821628
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;
0
 
LVL 22

Expert Comment

by:Ferruccio Accalai
ID: 9821645
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;
0
 

Author Comment

by:LumpyElbow
ID: 9821695
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
0
 
LVL 22

Expert Comment

by:Ferruccio Accalai
ID: 9821716
yes i meant that....have you got an icq account?
0
 

Author Comment

by:LumpyElbow
ID: 9821724
if  Windows messenger counts
0
 

Author Comment

by:LumpyElbow
ID: 9821775
my email is the_melonheads@hotmail.com if you want to jive
0
 
LVL 22

Expert Comment

by:Ferruccio Accalai
ID: 9821863
sorry but my W messenger don't work.....
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Delphi: Connect to running MS Outlook 4 162
SUM 2 INTEGER ARRAYS INTO 1 10 111
JAudiorecorder record freezing the app 29 83
how to resize animated Gif image in delphi ? 1 45
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question