Solved

TQRExpr SUM expression returning 'zero' in QuickReport

Posted on 2003-11-20
21
2,296 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 23

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 23

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 23

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 23

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 23

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 23

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 23

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 23

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 23

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 23

Expert Comment

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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses
Course of the Month8 days, 9 hours left to enroll

617 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