We help IT Professionals succeed at work.

qreport question

vpr_ali
vpr_ali asked
on
Hi, anyone knows how could I wrap up the result in qreport into summary ?

for example currently the result  in qreport is shown as:
03/12/99 fhj
14/12/99 jjjd
02/01/00 abc
03/01/00 def
31/01/00 cddf
02/02/00 hhh
07/02/00 hjfd
and so on

but I instead I want to summarise and look like this:

December       50 clients
January 2000    5 clients
February 2000  10 clients
and so on

and it must be user define from - to month
(which I know this could be done under sql)

regards
--vpr
Comment
Watch Question

Commented:
What Database are you using? The reason I ask is that this will most likely need to be done by querying the database and using the Count statement based on the month's in question and possibly other criteria.

A slmple example would be like this

SELECT Count(TheField) FROM TheTable WHERE TheDateField BETWEEN BeginDate AND EndDate

There are quite a few variations on this such as using paramenters for the Dates and/or Doing a GroupBy or possibly the Month statement. So it would be helpful if you let us know how youf data is laid out in the table(s) and what database you are using.


The Crazy One

Author

Commented:
Database that I'm using is access 97, I'm running the interface under delphi 5, which I use ODBC and BDE to set the link.

SELECT Count(TheField) FROM TheTable WHERE TheDateField BETWEEN BeginDate AND EndDate

My understanding with this query,it will come out with 1 result queried, result will be like:

client
-----
65

65 records found

not exactly as I wanted,where I wanted in a version like this:

December       50 clients
January 2000    5 clients
February 2000  10 clients
and so on

any ideas ?
oh and by the way, how do i do it in qreport, in terms of which component do i use ? TQRDbtext, TQRSysData ???

regards,
--vpr
Igor UL7AAjrSenior developer

Commented:
Hi vpr_ali,

you have to group data by year and months, something like this:

select count(Field1) from table_name
group by year_field, month_field

In case your date is in one field you can fillout report programmatically. Take a look at OnNeedData event in QR.

----
Igor.

Author

Commented:
Hi ITugay, if it not much of a trouble, can I ask you to do me a favour by creating a simple basic program with that qreport.

All I need to know is just how to group it into months, years, from a simple db.The db would probably contain simple information eg:
name, address, date, phone

so as shown in the example above, how would I be able from to select certain dates (I figured that out already), and
showing the result in qreport by months, year view.

regards
--vpr
Igor UL7AAjrSenior developer

Commented:
Hi vpr_ali,

OK, let me few time....

Igor.
Igor UL7AAjrSenior developer

Commented:
Hi vpr_ali,

here is it.

New application.
Drop TQuickReport onto Form
Drop TQRSubDetail
Drop 3 QRLabels on QRSubdetail
Drop TQuery, this sample is for ORDERS table in DBDEMOS database. It shipped with Delphi.
Query1.SQL = 'select * from ORDERS order by SALEDATE'
Query1.Active = true
Drop TSpeedButton on form.
Assign all events as shown bellow.
Run application and click button.

you will see something like this:

1988 jan 7
1988 feb 14
1988 mar 6
...

last number is count of records for shown year and month.

----
Igor.

type
  TForm1 = class(TForm)
    Query1: TQuery;
    QuickRep1: TQuickRep;
    QRSubDetail1: TQRSubDetail;
    QRLabel1: TQRLabel;
    QRLabel2: TQRLabel;
    QRLabel3: TQRLabel;
    SpeedButton1: TSpeedButton;
    procedure QRSubDetail1NeedData(Sender: TObject; var MoreData: Boolean);
    procedure QRLabel1Print(sender: TObject; var Value: String);
    procedure SpeedButton1Click(Sender: TObject);
    procedure QRLabel2Print(sender: TObject; var Value: String);
    procedure QRLabel3Print(sender: TObject; var Value: String);
  private
    { Private declarations }
  public
    LastMonth: Integer;
    LastYear: Integer;
    RecCount: Integer;
  end;

var
  Form1: TForm1;

implementation

{$R *.DFM}

procedure TForm1.QRSubDetail1NeedData(Sender: TObject;
  var MoreData: Boolean);
var
  Y,M,D: Word;
begin
  DecodeDate(Query1.FieldByName('SALEDATE').AsDateTime, Y, M, D);
  LastYear := Y;
  LastMonth := M;
  RecCount := 0;

  while (Y = LastYear) and (M = LastMonth) and not Query1.Eof do
  begin
    inc(RecCount);
    Query1.Next;
    DecodeDate(Query1.FieldByName('SALEDATE').AsDateTime, Y, M, D);
  end;

  MoreData := not Query1.EOF;
end;

procedure TForm1.QRLabel1Print(sender: TObject; var Value: String);
begin
  Value := IntToStr(LastYear);
end;

procedure TForm1.QRLabel2Print(sender: TObject; var Value: String);
begin
  Value := FormatDateTime('mmm',EncodeDate(1,LastMonth,1));
end;

procedure TForm1.QRLabel3Print(sender: TObject; var Value: String);
begin
  Value := IntToStr(RecCount);
end;

procedure TForm1.SpeedButton1Click(Sender: TObject);
begin
  QuickRep1.PreviewModal;
end;



end.

Author

Commented:
Hi ITugay, your code do work fine and good, but I've got another problem, on the code:
 
procedure TForm1.QRSubDetail1NeedData(Sender: TObject;
 var MoreData: Boolean);

--
the situation is when the data is not found: i.e there is no detail found from the specified from to date from user, result that came out is

1899 Dec 0

can I some how like don't show this result ?

regards
--vpr
Senior developer
Commented:
hi vpr,

just modify your QRSubDetail1NeedData procedure. Here is repeat - until block.

procedure TForm1.QRSubDetail1NeedData(Sender: TObject;
  var MoreData: Boolean);
var
  Y,M,D: Word;
begin
  repeat
    DecodeDate(Query1.FieldByName('SALEDATE').AsDateTime, Y, M, D);
    LastYear := Y;
    LastMonth := M;
    RecCount := 0;
    while (Y = LastYear) and (M = LastMonth) and not Query1.Eof do
    begin
      inc(RecCount);
      Query1.Next;
      DecodeDate(Query1.FieldByName('SALEDATE').AsDateTime, Y, M, D);
    end;
  until Query1.Eof or (RecCount > 0);

  MoreData := not Query1.EOF;
end;

Author

Commented:
Thanks ITugay, you're a big help, by the way do you know anything about TDbChart ? in terms in how to use it? I've tried to look from help but it all sounds jargon to me, I would really appreciate if you could help me again in this, I'll make it worth while to you

I am also trying to display the results into chart and it must be able to be printed out on paper

regards
--vpr
Igor UL7AAjrSenior developer

Commented:
hi vpr,

I never used TDBChart for this purposes. I'm not sure that there is best "control" to show charts then MS-Excel :-)
When I need to draw some charts then I export prepared data to Excel (directly or via clipboard). You can see some samples in PAQ of this topic area.

----
Igor.

Author

Commented:
Thanks again for your help =>
regards
--vpr

Explore More ContentExplore courses, solutions, and other research materials related to this topic.