How to show data at Fast Report in 3*3 grid format?

Question

I want to display data from table (column :- ID) at FastReport in Grid format as shown below. Suppose there are 22 records in table then it would display same in 3 * 3 grid at FastReport in following manner.

I am using subreport at main page inside MasterData1 band. At subreport MasterData band is as follows

MasterData1 band

TfrxReportPage1 - Columns 2

Subreport - MasterData2 properties Columns 3 , RowCount 9

But when I previewed fast report it is just repeating same data in each grid on page as follows

I am using frxDBDataSet1 to display data (Number Of Records 9). There are in total 28 records so it is expected to show four 3*3 grids on page with 27 IDs. but rather it is repeating first 9 ID's in each 3*3 grid as shown above.

IF I set Number Of Records 0 at frxDBDataSet1 then it would show something like as below It is not forming 3 * 3 grid but shows data continuou

Romeu

Any Delphi - FastReport expert here? – Romeu

Since many people filter on main tags you should add them, here delphi. For example, I would not have seen the question without your comment on another post from me. – Asphodel

I had to delete my answer, with the newer version in my office the behavior was completely different, sorry. – Asphodel

IS there any way to change the range of frxDBDataset1 dynamically i.e like after printing first 9 records in first grid, for second grid is it possible to change range of frxDBDataset1 from 10 to 18 etc.. – Romeu

If the now deleted answer from me should work for your version of fast reports (except repeating the numbers) you might just have left RowCount=9, instead of 0? – Asphodel

If MasterDataBand2 rowcount set to 0 then it would print every single block on new page i.e Line10 , Line11, Line12 etc.. each on the new page. – Romeu

that's why I deleted my answer, works fine with V.:4.10.1, but not V.:4.12.14. I tried different other approaches, but did not find a solution yet :-(. – Asphodel

I am using FastReport 4.13, it seems like there limited features at Fast Report. I am upto something like this, it is printing 10 to 18 if frxDBDataset1.RecNo = 0 then begin for i := 0 to 8 do frxDBDataset1.Next; end; Is there any way to extract variable(i.e id) value from the frxDBDataSet1 so that we can move frxDBDataset1.next that many times depending on the last ID. – Romeu

Can it be done with frxUserDataset ? – Romeu

You need two side by side subreports on your MasterData, each connected to a dataset. The first dataset would hold records 101~118, and the second one would hold 119~132. You should think of a mechanism to split data every 9 records on each subreport though... – Thickset

Even side by side subreport is used with two different dataset each dataset would print 2 grids in vertical line. Here problem is that after printing first grid with first 9 records dataset moves back to first record again and print same first 9 records in next grid. – Romeu

Answer 1

Public variable declarations

  public
    i: integer;
    myid: Array Of Integer;
    mydesc: Array Of String;
    k: Integer;
    rowcount: Integer;

Code at FormCreate event

begin
    i := 0;
    k := 0;
    UniTable1.SQL.Text := 'Select * from userplays';
    UniTable1.Execute;
    rowcount := UniTable1.RecordCount;
    SetLength(myid, rowcount);
    SetLength(mydesc, rowcount);

      while not UniTable1.Eof do
      begin
         myid[k] := UniTable1.FieldByName('id').Value;
         mydesc[k] := UniTable1.FieldByName('description').Value;
         UniTable1.Next;
         inc(k);
      end;
end.

Code at OnGetValue event of the frxReport

var
  j: Integer;

begin
 j := i div 2;
 if j < rowcount then
   begin

     if (VarName = 'ID1') then
       Value :=  myid[j];

     if (VarName = 'DESC1') then
       Value :=  mydesc[j];

   end
 inc(i);

Above ID1 and DESC1 are two variable declared at frxReport memo, value assigned to it at OnGetValue event with the help of global Array myid & mydesc. Global arrays myid & mydesc are filled with database field values at FormCreate event.

Romeu

Answer 2

EDIT since the previous* answer does not work with newer versions

Set rowcount to 9 for your masterdata band of the subreport.
In your mainreport, copy the masterdata band containing the subreport and insert it twice.
Put a headerband between the masterbands with property StartNewPage set to true.
Add OnBeforePrint events to the second and the third subreport to change the filter for the dataset.

procedure Subreport2OnBeforePrint(Sender: TfrxComponent);
begin
  TfrxDBDataSet(Report.GetDataset('frxDBDataset1')).Dataset.Filter := 'ID>9';  
  TfrxDBDataSet(Report.GetDataset('frxDBDataset1')).Dataset.Filtered := true;                                          
end;

procedure Subreport3OnBeforePrint(Sender: TfrxComponent);
begin
  TfrxDBDataSet(Report.GetDataset('frxDBDataset1')).Dataset.Filter := 'ID>18';  
  TfrxDBDataSet(Report.GetDataset('frxDBDataset1')).Dataset.Filtered := true;  
end;

(*) for older report versions, you can use an other approach, which stopped working between the versions 4.10.01 and 4.12.14.

Add an OnAfterPrint event to your memo on the subreport. Set the property StartNewPage to true after printing 9 "lines", Rowcount of the Masterband has to set to 1.

procedure YourMemoFromTheSubReportToRepeat9TimesPerPageOnAfterPrint(Sender: TfrxComponent);
begin
    MasterDataBandFromSubReport.StartNewPage := <Line#>  mod 9 = 0  
end;

Asphodel

@ bummi, it is printing 3*3 grid on new page with same lines i.e Line 1 to Line 9. Actually I want to print four 3*3 grid on single page with not repetition of records i.e means after printing Line 1 to Line 9 in first grid it will print Line 10 to Line 18 in second grid, Line 19 to Lione 27 in 3rd Grid, Line 28 to Line 36 in 4th Grid on first page..... Line 37 to 45 on first grid of second page etc.. so on..It would print continuous record as they are in database table. – Romeu

After printing first 9 records in first grid it is moving frxDBDataSet1 back to first record again and printing same first 9 records in next grid..Thus way it is printing same first 9 records in all grids by ignoring records after count 9...How to avoid this repetition so it would not move frxDBDataset to first record after printing records in first grid? – Romeu

1) Your first solution with 3 subreports works. But it work only when there are only less than 27 record in the table and we know the number. IS there anything like this can be done dynamically in case if we not aware about how many records are there in table. 2) Other issue I am getting Undeclared Identifier - TfrxDBDataset error. – Romeu

I added one more procedure as it was automatically setting filter ID > 18 at UniTable from which I am pulling data at my frxReport. So I added procedure Page1OnBeforePrint(Sender: TfrxComponent); begin TfrxDBDataSet(Report.GetDataset('frxDBDataset1')).Dataset.Filtered := false; end; This works fine when previewed directly from frxReport but when ran Delphi project it is fetching error Undeclared Identifier : TfrxDBDataset – Romeu

Sounds like you are missing frxDBSet in the uses clause of your unit. This will automtically be added if you add a frxDBDataset to your form or datamodule. – Asphodel

frxDBset is there in uses clause. frxDBDataset component is already there on the form. When added frxDBDataset to the uses clause it gives error Cannot resolve unit name frxDBDataset. When used frxDBDataset at XE3 source code it is not fetching any error. The error is mainly with TfrxDBDataset which is used at script code of .fr3 file. – Romeu

Can we use procedure like procedure Subreport1OnBeforePrint(Sender: TfrxComponent); in Delphi XE3 rather than .fr3 script code – Romeu

We are getting away from the original topic and should stop chatting here. Take a look at the events of the frxReport component, yes this can be done. – Asphodel

bummi, It is done finally with the help of global variable(public) declared at xe3 and custom variables at fast report memo, incremented public variable eqach time ongetvalue event of the frxReport. Before that stored value of the particular field of the table into an array and extracting same with the help of global variable from array. – Romeu

@NinadAvasare Thanks for keeping me informed, since you got it working you should add an own answer and accept it. – Asphodel

Do you use DBAdvGrid component ? – Romeu