Solved

TQRExpr SUM expression returning 'zero' in QuickReport

Posted on 2003-11-20
21
2,119 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
  • 10
  • 9
21 Comments
 
LVL 22

Accepted Solution

by:
Ferruccio Accalai earned 300 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:LumpyElbow
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Another thing....i'd assign quickrep1.dataset instead of quickrep1.alldataset....
0
 

Author Comment

by:LumpyElbow
Comment Utility
Now the '0' disappears and nothing is printed
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 22

Expert Comment

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

Author Comment

by:LumpyElbow
Comment Utility
did that too
0
 
LVL 22

Expert Comment

by:Ferruccio Accalai
Comment Utility
also changed the qrgroup.master and expression?
0
 

Author Comment

by:LumpyElbow
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
yes i meant that....have you got an icq account?
0
 

Author Comment

by:LumpyElbow
Comment Utility
if  Windows messenger counts
0
 

Author Comment

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

Expert Comment

by:Ferruccio Accalai
Comment Utility
sorry but my W messenger don't work.....
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
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…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now