Different records on master data band

[Fai]

Hello,
I created a report with Lazreport. As you can see in the attached screenshot, I only need the first record of the payment amount (circled red). But because the query is a join between payment table and the tax table (deduction column) so the payment table automatically has the same record as the tax table.
I put the data on Master Data band.
Is there any way to solve this with Lazreport?

Thanks,

[taazz]

post the schema of the two or three tables that are used in the join along with info on the foreign keys.

[Fai]

attached is the tables and this is the query :

Code: [Select]

select * from payment left join paymenttype on payment.fpaytype=paymenttype.idpaymenttype
left join taxtbl on payment.ftaxcode=taxtbl.idtax where
idpayment='XXXXX0001';
btw, thanks for the quick response

[balazsszekely]

@loop
frRepor has an OnBeginBand event. You can do something like this:

Code: [Select]

  if Band.Typ = btMasterData then
  begin
    Band.Visible := (decide here if the current record is from payment or taxtbl)
  end; 


[Fai]

Thanks GetMem!
But can you give specific example what should I write in Band.Visible:=...?

...decide here if the current record is from payment or taxtbl

Because my dataset is a join query from 3 tables, how can I choose only one table?
Is it possible frReport using more than one frDBDataset at a time?

[balazsszekely]

First of all that double left join will be very slow on large tables, secondly it's not clear to me what are you after.
Anyway if you want to differentiate between payment and tax table add a integer field (TType for example) to both table. (TType = 0 on payment table, TType = 1 on tax table).

Code: [Select]

procedure TForm1.frReport1BeginBand(Band: TfrBand);
begin
  if Band.Typ = btMasterData then
  begin
    Band.Visible := frDataset.Dataset.FieldByName('TType').AsInteger = 1;
  end; 
end;


[Fai]

it's not clear to me what are you after

As in the attached picture on my first post, the left column is the payment/transaction, there should be only one record/transaction. One payment/transaction can be object to several taxes cut (right column). But because I use join query to call the report, the payment/left columns can have more than 1 record, following the records from tax table. That's the problem.
Due to project deadline, I've made a 'dirty' workaround : change the report layout.
But if you have a nicer workaround by not changing the layout, I will appreciate it very much.
Thanks

[taazz]

there are a couple of things you can do,
1) Do a master detail report instead of a single query object.
2) Add code to change the printed text or color based on repetition of the data.
or any other creative solution you might thing of.

If you provide a small sample with the data I'll create two samples one for each option and post back.

[paweld]

sample in attachment. reqiured ZeosLib component and sqlite.dll.

[taazz]

sample in attachment. reqiured ZeosLib component and sqlite.dll.

downloaded I will take a closer look in the next couple of days and report back my progress. I hope I have zeos installed they were a pain to install last time I tried.

[paweld]

standard sqldb components. only sqlite library required

[taazz]

I'm looking at the sample now but I can't find the db.dat file.

[Fai]

It should be created when you run the sample and click the connect button

[taazz]

It should be created when you run the sample and click the connect button

Well I'm requesting official help missed the prominent huge button title "Create table and add data". its so huge it bends light all around it and my eyes missed it 

[taazz]

so you've been naughty with your events haven't you  ? Well not naughty enough though here is an onNext event that will jump over all of the rest tax deductions making sure that only the first one is printed.

Code: [Select]

procedure TForm1.frDBDataSet1Next(Sender: TObject);
begin
  if not SameText(SQLQuery2.FieldByName('fpaytype').AsString, lastpaytype) then
  begin
    lastpaytype:=SQLQuery2.FieldByName('fpaytype').AsString;
    showpay:=True;
  end
  else repeat
    SQLQuery2.Next;
  until (not SameText(SQLQuery2.FieldByName('fpaytype').AsString, lastpaytype)) or (SQLQuery2.EOF);
end;