Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

TQRExpr SUM expression returning 'zero' in QuickReport

Posted on 2003-11-20
21
Medium Priority
?
2,408 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
19 Comments
 
LVL 23

Accepted Solution

by:
Ferruccio Accalai earned 1200 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
Technology Partners: 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 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

772 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