Recent

Author Topic: Different records on master data band  (Read 5988 times)

Fai

  • New Member
  • *
  • Posts: 24
Different records on master data band
« on: May 31, 2015, 09:31:36 am »
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

  • Hero Member
  • *****
  • Posts: 5363
Re: Different records on master data band
« Reply #1 on: May 31, 2015, 09:39:47 am »
post the schema of the two or three tables that are used in the join along with info on the foreign keys.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

Fai

  • New Member
  • *
  • Posts: 24
Re: Different records on master data band
« Reply #2 on: May 31, 2015, 10:51:03 am »
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

GetMem

  • Hero Member
  • *****
  • Posts: 3506
Re: Different records on master data band
« Reply #3 on: May 31, 2015, 02:07:18 pm »
@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

  • New Member
  • *
  • Posts: 24
Re: Different records on master data band
« Reply #4 on: May 31, 2015, 03:44:10 pm »
Thanks GetMem!
But can you give specific example what should I write in Band.Visible:=...?
Quote
...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?
« Last Edit: May 31, 2015, 03:47:22 pm by loop »

GetMem

  • Hero Member
  • *****
  • Posts: 3506
Re: Different records on master data band
« Reply #5 on: May 31, 2015, 05:35:59 pm »
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;
« Last Edit: May 31, 2015, 05:47:28 pm by GetMem »

Fai

  • New Member
  • *
  • Posts: 24
Re: Different records on master data band
« Reply #6 on: June 01, 2015, 11:04:30 am »
Quote
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

  • Hero Member
  • *****
  • Posts: 5363
Re: Different records on master data band
« Reply #7 on: June 01, 2015, 07:06:30 pm »
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.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

paweld

  • Full Member
  • ***
  • Posts: 186
Re: Different records on master data band
« Reply #8 on: June 01, 2015, 10:39:56 pm »
sample in attachment. reqiured ZeosLib component and sqlite.dll.
Best regards
paweld

taazz

  • Hero Member
  • *****
  • Posts: 5363
Re: Different records on master data band
« Reply #9 on: June 02, 2015, 12:31:13 am »
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.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

paweld

  • Full Member
  • ***
  • Posts: 186
Re: Different records on master data band
« Reply #10 on: June 02, 2015, 09:59:34 am »
standard sqldb components. only sqlite library required
Best regards
paweld

taazz

  • Hero Member
  • *****
  • Posts: 5363
Re: Different records on master data band
« Reply #11 on: June 04, 2015, 10:56:51 pm »
I'm looking at the sample now but I can't find the db.dat file.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

Fai

  • New Member
  • *
  • Posts: 24
Re: Different records on master data band
« Reply #12 on: June 05, 2015, 12:39:04 am »
It should be created when you run the sample and click the connect button

taazz

  • Hero Member
  • *****
  • Posts: 5363
Re: Different records on master data band
« Reply #13 on: June 05, 2015, 01:20:16 am »
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  :P
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

taazz

  • Hero Member
  • *****
  • Posts: 5363
Re: Different records on master data band
« Reply #14 on: June 05, 2015, 02:56:43 am »
so you've been naughty with your events haven't you  ;D ? 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;
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64