Some thoughts and ideas for a better, useful fpReport

[MacWomble]

I try to get some reports running, I created with the fpreport-designer.
In standalone designer it works as I expected, but when I load the report from within my project, the data won't be found.
When I open my json-report from standalone designer, the data is shown and I can do prview and then export to csv.
 
What I wnt is:
1. using the designer out of my application to create new Layouts
2. Running reports with data-definition stored in the report file and export to pdf without designer.

What I have for now is:
1. csv-file containing the data
2. json-file of the report

and some source:

Code: Pascal  [Select][+][-]

1. procedure TReports.PrintLabels(S: TSelectedAdresses);
2. var
3.   rpt: TFPjsonReport;
4.   p: TFPReportCustomPage;
5.   DM: TCSVReportDataHandler;
6.   Designer: TFPReportDesigner;
7.   lReportData: TFPReportUserData;
8. begin
9.   PaperManager.RegisterStandardSizes;
10.  
11.   rpt := TFPjsonReport.Create(self);
12.   rpt.LoadFromFile('/home/klaus/Entwicklung/CTR-BOSS_2019/DATA/Labels/Etiketten.json');
13.   Designer := TFPReportDesigner.Create(Self);
14.  // I think, here I must connect to data-manager; but how?
15.   Designer.Report := rpt;
16.   Designer.Execute;  
17.  

With this code the report is shown, but the data definition will not be seen.

Also I use a method to get data from my DB-classes (Objectlists: S) and store the definition within the report-file.

Could someone give me some hints and help on this?

I think fpreport is a great tool, but lacks heavily on documentation ...

[af0815]

Look in the dir lazarus/components/fpreport/design/frmfpreportdesignermain.pp and search for

Code: Pascal  [Select][+][-]

1. procedure TFPReportDesignerForm.LoadDesignFromFile(const AFilename: string);
2.  

you will find a peace of code how the designer is loading the data

Code: Pascal  [Select][+][-]

1.     if rdoManageData in DesignOptions then
2.       begin
3.       DD:=lJSON.Get('DesignData',TJSONObject(Nil));
4.       if Assigned(DD) then
5.         FReportDesignData.DataDefinitions.LoadFromJSON(DD);
6.       // We must do this before the report is loaded, so the pages/bands can find their data
7.       Errs:=TStringList.Create;
8.       CreateReportDataSets(Errs);
9.       end;
10.  

FReportDesignData is created in FormCreate and used eg. Previewreport.

I hope this is a good starting point

[MacWomble]

Thanx af0815 I solved csv-report questions. All works fine with csv now.

But there are new questions now:

I'am experimenting with fpreport data source json, so I generated a simple json file with master-detail:

Code: Text  [Select][+][-]

1. {
2.   "Invoice" : [
3.     {
4.       "Nummer" : "RE-250",
5.       "Name" : "Peter Testmann",
6.       "Ort" : "70000 Teststadt",
7.       "Text1" : "Gemäß unseren ausgeführten Arbeiten berechnen wir wie folgt:",
8.       "DataItems" : [
9.         {
10.           "OrderId" : 1,
11.           "ItemHtml" : "This is item 1",
12.           "Preis" : "123,34"
13.         },
14.         {
15.           "OrderId" : 2,
16.           "ItemHtml" : "This is item 2",
17.           "Preis" : "43,99"
18.         },
19.         {
20.           "OrderId" : 3,
21.           "ItemHtml" : "This is item 3",
22.           "Preis" : "13,55"
23.         }
24.       ],
25.       "Fusstext" : "Dies ist der Fußtext"
26.     }
27.   ]
28. }  

I could not get the data work with fpreport. I cant get the DataItems.

How to do Master-Detail with json data in fpreport? I need to use the ReportDesigner!

[MacWomble]

After doing some tests and inspecting the source, I think there is no option in fpreport to do Master-Detail from json data, only from sql.
If this is true it's not a good news but a needed (wished) feature for near future.
Is anyone able to do this please?

[MacWomble]

It seems that json data did not work at all. I got an Access violation also with simple json data like in attachment.

in fpreport:

Code: Pascal  [Select][+][-]

1. procedure TFPReportLayouter.InitReportData(aMainData : TFPReportData);
2.  
3. Var
4.   I : Integer;
5.   oData : TFPReportData;
6.  
7. begin
8.   if Assigned(aMainData) and not aMainData.IsOpened then
9.     aMainData.Open;
10.   For I:=0 to Report.ReportData.Count-1 do
11.     begin
12.     oData:=Report.ReportData[i].Data;
13.     if Assigned(oData) and (oData<>aMainData) and (not odata.IsOpened) then
14.       oData.Open;  // The Error occures here !
15.     end;
16.   if Assigned(aMainData) then
17.     aMainData.First;
18.   if IsFirstPass then
19.     begin
20.     Report.InitializeExpressionVariables;
21.     Report.InitializeAggregates(True);
22.     end
23.   else
24.     Report.InitializeAggregates(False);
25. end;
26.  

[af0815]

Thanx af0815 I solved csv-report questions. All works fine with csv now.

For the records -> https://github.com/afriess/fpReportSamples in 02_SimpleReportCSV

Code: Pascal  [Select][+][-]

1. procedure TFrmSimpleReportLCL.CreateDemoReport(FN: string);
2. var
3.   fs : TFileStream;
4.   AllJSON, DesignDataJSON: TJSONObject;
5. begin
6.   // Clear old reports
7.   CleanUp;
8.  
9.   fs:= TFileStream.Create(FN, fmOpenRead or fmShareDenyNone);
10.   try
11.     // ReportData AND DesignData
12.     AllJSON:= TJSONObject(GetJSON(fs));
13.  
14.     // Extract DesignData only
15.     DesignDataJSON:= AllJSON.Get('DesignData',TJSONObject(nil));
16.  
17.     // Create the Data for the report before the report
18.     lReportData:= TFPReportDataManager.Create(self);
19.     lReportData.LoadFromJSON(DesignDataJSON);
20.  
21.     // Read the report
22.     FReport:=TFPJSONReport.Create(Self);
23.     FReport.DataManager:= lReportData;
24.     FReport.LoadFromJSON(AllJSON);
25.   finally
26.     AllJSON.Free;
27.     fs.Free;
28.   end;
29. end;
30.  

[MacWomble]

@af0815:
data in csv works as expected,
data in json works not.

This is a problem within the ReportDesigner (or anywhere in fpreport):
Open Designer: New Report, Report - Report Data

First problem:
Set source to a simple json file

Then after this place some data fields on data abnd and press preview ... Access Violation 

Second problem:
Set Source to json file with master-detail data, only master is shown. 

All this should be done and saved with (only) the ReportDesigner without any coding to get a working and user editable report on a simple way.   

What I have for now is only csv (for simpe lists) (attachment):
The needed report ist simply done with the ReportDesigner and saved as json.
From my program I generate and save my data to csv, than I call Reports.CSVtoPDFReport and I got my pdf file - great! )
(You have to change the report path near line 80)

I thought to do a master-detail report I can not use csv so I tried a json-datafile instead.
But this will not work ...

[af0815]

First Problem: It looks like the information where the data is, is not consistent stored and lost between design and preview

Second: The Dataset of JSON have IMHO no master-detail capability. How did you generate the JSON data with master-detail in Lazarus ?!

BTW: You are not working with Lazarus, so i am not able to use the files and i am not willing to support CT here (and convert the files back).

[MacWomble]

You are right, the field information will be lost in ReportDesign ... we talked about.

I generated my json file by hand, but it's no problem to do this by code in Lazarus.
Yes, Json supports master detail, but the fpreport dataset (tested in ReportDesign) did not.

I attached (changed) the last source to Lazarus now.

Again, I know fpreport is in an early state and there are many things which should be done.
Also fpreport in first hand is not developed for lazarus but for fpgui and web apps.
But it has a great chance to become 'the Reporttool' for FPC/Laz.
Mainly there are two things, which should be done for now:

- Full support for nested Json data from file or stream.

This is essential, so one can do nearly all type of reports for Lazarus as there are simple lists up to invoices and such nested reports, without coding the report all by hand in the unit.

- Also it would be great to have support for image trnsparency, so a logo could be used (tested with png).
Much better: support for svg!

As my knowledge is not as good, I hope someone is willing to realize this in near future.

[af0815]

I generated my json file by hand, but it's no problem to do this by code in Lazarus.
Yes, Json supports master detail, but the fpreport dataset (tested in ReportDesign) did not.

Have you sample code to to it in Lazarus (without fpreport - and maybe with LazReport) ?!

[MacWomble]

I generated my json file by hand, but it's no problem to do this by code in Lazarus.
Yes, Json supports master detail, but the fpreport dataset (tested in ReportDesign) did not.

Have you sample code to to it in Lazarus (without fpreport - and maybe with LazReport) ?!

No, no code yet. It's simply json, generated in xed.

[MacWomble]

Tested now with JSONView (Its part of Lazarus) and it seems OK

Code: Text  [Select][+][-]

1. {
2.   "Invoice" : [
3.     {
4.       "Nummer" : "RE-250",
5.       "Name" : "Peter Testmann",
6.       "Ort" : "70000 Teststadt",
7.       "Text1" : "Gemäß unseren ausgeführten Arbeiten berechnen wir wie folgt:",
8.       "DataItems" : [
9.         {
10.           "OrderId" : 1,
11.           "ItemHtml" : "This is item 1",
12.           "Preis" : "123,34"
13.         },
14.         {
15.           "OrderId" : 2,
16.           "ItemHtml" : "This is item 2",
17.           "Preis" : "43,99"
18.         },
19.         {
20.           "OrderId" : 3,
21.           "ItemHtml" : "This is item 3",
22.           "Preis" : "13,55"
23.         }
24.       ],
25.       "Fusstext" : "Dies ist der Fußtext"
26.     }
27.   ]
28. }  

[MacWomble]

Also this should work (Master-Detail-Subdetail). But its not as complex as it could be in some other cases...

Code: Text  [Select][+][-]

1. {
2.   "Record" : [
3.     {
4.       "MasterField5" : "MasterData 1",
5.       "MasterField3" : "MasterData 2",
6.       "MasterField4" : "MasterData 3",
7.       "MasterField2" : "MasterData 4",
8.       "MasterField1" : "MasterData 5",
9.       "Detail" : [
10.         {
11.           "DetailField1.1" : "DetailData 1.1",
12.           "DetailField1.2" : "DetailData 1.2",
13.           "DetailField1.3" : "DetailData 1.3",
14.           "SubDetail" : [
15.             {
16.               "SubDetailField1.1.1" : "SubDetailData 1.1.1",
17.               "SubDetailField1.1.2" : "SubDetailData 1.1.2"
18.             },
19.             {
20.               "SubDetailField1.2.1" : "SubDetailData 1.2.1",
21.               "SubDetailField1.2.2" : "SubDetailData 1.2.2"
22.             },
23.             {
24.               "SubDetailField1.3.1" : "SubDetailData 1.3.1",
25.               "SubDetailField1.3.2" : "SubDetailData 1.3.2"
26.             }
27.           ]
28.         },
29.         {
30.           "DetailField2.1" : "DetailData 2.1",
31.           "DetailField2.2" : "DetailData 2.2",
32.           "DetailField2.3" : "DetailData 2.3",
33.           "SubDetail" : [
34.             {
35.               "SubDetailField2.1.1" : "SubDetailData 2.1.1",
36.               "SubDetailField2.1.2" : "SubDetailData 2.1.2"
37.             },
38.             {
39.               "SubDetailField2.2.1" : "SubDetailData 2.2.1",
40.               "SubDetailField2.2.2" : "SubDetailData 2.2.2"
41.             },
42.             {
43.               "SubDetailField2.3.1" : "SubDetailData 2.3.1",
44.               "SubDetailField2.3.2" : "SubDetailData 2.3.2"
45.             }
46.           ]
47.         }
48.       ]
49.     }
50.   ]
51. }        

[af0815]

the dataset component can not handle master detail. only one array of data is possible. the underlaying dataset cannot handle this.

it is possible to design in the json designer, but not useable in fpreport or TJSONdataset.

[MacWomble]

When fpreport mainly was developed for web, json should be fully supported with such structures and
I think it is and should be possible when ... for example:

After parsing the json the parts of the json could be transfered to dynamic BufDatasets or similar Objects.

so   "MasterField1" : "MasterData 1",
      "MasterField2" : "MasterData 2",
      "MasterField3" : "MasterData 3",
      "MasterField4" : "MasterData 4",
      "MasterField5" : "MasterData 5",
should read into BufMaster with ID 1

          "DetailField1.1" : "DetailData 1.1",
          "DetailField1.2" : "DetailData 1.2",
          "DetailField1.3" : "DetailData 1.3",
should read int BufDetail with ID1 and MasterID1

              "SubDetailField1.1.1" : "SubDetailData 1.1.1",
              "SubDetailField1.1.2" : "SubDetailData 1.1.2"

              "SubDetailField1.2.1" : "SubDetailData 1.2.1",
              "SubDetailField1.2.2" : "SubDetailData 1.2.2"

              "SubDetailField1.3.1" : "SubDetailData 1.3.1",
              "SubDetailField1.3.2" : "SubDetailData 1.3.2"
Should read into BufSub with ID1,2,3 and MasterID1 and DetailID1          ]

          "DetailField2.1" : "DetailData 2.1",
          "DetailField2.2" : "DetailData 2.2",
          "DetailField2.3" : "DetailData 2.3",
should read into BufDetail with ID2 and MasterID1
           
              "SubDetailField2.1.1" : "SubDetailData 2.1.1",
              "SubDetailField2.1.2" : "SubDetailData 2.1.2"

              "SubDetailField2.2.1" : "SubDetailData 2.2.1",
              "SubDetailField2.2.2" : "SubDetailData 2.2.2"

              "SubDetailField2.3.1" : "SubDetailData 2.3.1",
              "SubDetailField2.3.2" : "SubDetailData 2.3.2"
Should read into BufSub with ID1,2,3 and MasterID1 and DetailID2     

and so on ...

You have then (in this sample) three datasources BufMaster, BufDetail and BufSub