qreport question

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

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

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.

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

Hi vpr_ali,

OK, let me few time....

Igor.

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.

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

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

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.

Thanks again for your help =>
regards
--vpr