Recent

Author Topic: FortesReport and SQLite  (Read 13949 times)

JanRoza

  • Hero Member
  • *****
  • Posts: 538
    • http://www.silentwings.nl
FortesReport and SQLite
« on: April 05, 2010, 12:09:56 am »
Can anyone provide a clear example of how to display data from a SQLite table in a detailband?
After a bit of experimenting and studying the demos I succeeded in finding out how FortesReport works and I now have a header filled with some data from one SQLite table and a detailband which should show data from another SQLite table which is created in the unit that calls the preview.
Unfortunately all I see in the preview are the header and footer on the report, the detail part stays blank.
I have no clue of what is wrong so maybe if I see an working example from someone I can figger it out.
Can anyone provide me with one?

FortesReport is a great product, but is in high need of good, english and complete documentation.

 :(
Jan
 
OS: Windows 10 (64 bit) / Ubuntu 19.04 (64 bit)
Laz: Lazarus 2.0.4 FPC 3.0.4 i386-win32-win32/win64

fredycc

  • Sr. Member
  • ****
  • Posts: 265
Re: FortesReport and SQLite
« Reply #1 on: April 05, 2010, 04:21:10 am »
FortesReport is good but documentation is poor, check lazreport is great and easy.

tatamata

  • Hero Member
  • *****
  • Posts: 693
    • ZMSQL - SQL enhanced in-memory database
Re: FortesReport and SQLite
« Reply #2 on: April 05, 2010, 10:35:42 am »
Where did you find Lazarus version of FortesReport?
I can see only Delphy and Kylix versions:
http://www.fortesreport.com.br/index.en.htm

JD

  • Hero Member
  • *****
  • Posts: 1758
Re: FortesReport and SQLite
« Reply #3 on: April 05, 2010, 11:59:42 am »
Where did you find Lazarus version of FortesReport?
I can see only Delphy and Kylix versions:
http://www.fortesreport.com.br/index.en.htm


This thread talks about the Lazarus version of Fortes Report

http://www.lazarus.freepascal.org/index.php/topic,8917.0.html

« Last Edit: April 05, 2010, 12:02:45 pm by JD »
Windows (10, 7) - Lazarus 2.0RC3/FPC 3.2, NewPascal, Delphi

Indy 10.6 series; mORMot; Zeos 7.2.1; SQLite, Firebird, PostgreSQL & MariaDB; VirtualTreeView 5.5.3 R1

JD

  • Hero Member
  • *****
  • Posts: 1758
Re: FortesReport and SQLite
« Reply #4 on: April 05, 2010, 12:02:06 pm »
Can anyone provide a clear example of how to display data from a SQLite table in a detailband?
After a bit of experimenting and studying the demos I succeeded in finding out how FortesReport works and I now have a header filled with some data from one SQLite table and a detailband which should show data from another SQLite table which is created in the unit that calls the preview.
Unfortunately all I see in the preview are the header and footer on the report, the detail part stays blank.
I have no clue of what is wrong so maybe if I see an working example from someone I can figger it out.
Can anyone provide me with one?

FortesReport is a great product, but is in high need of good, english and complete documentation.

 :(
Jan
 

You'll find a development version of Fortes Report for Lazarus here with demos

http://sourceforge.net/projects/fortes4lazarus/develop
Windows (10, 7) - Lazarus 2.0RC3/FPC 3.2, NewPascal, Delphi

Indy 10.6 series; mORMot; Zeos 7.2.1; SQLite, Firebird, PostgreSQL & MariaDB; VirtualTreeView 5.5.3 R1

JanRoza

  • Hero Member
  • *****
  • Posts: 538
    • http://www.silentwings.nl
Re: FortesReport and SQLite
« Reply #5 on: April 05, 2010, 01:49:38 pm »
That's the version I have installed and I have tried out all demos but still cannot get a detailband to show content of my SQLite database.

One more thing I noticed is that even the demos give me a SIGSEV error the moment I really want to print the report.

Another thing I forgot to mention is that I'm working with Lazarus 0.9.28.3 and FPC 2.40 dated 20 March 2010.

I think FortesReport is so much more than LazReport (compliments to the Brazilians!), the only thing missing is good documentation. So I still hope some of you can provide a good working example of FortesReport with SQLite records in the detail band.

OS: Windows 10 (64 bit) / Ubuntu 19.04 (64 bit)
Laz: Lazarus 2.0.4 FPC 3.0.4 i386-win32-win32/win64

LuizAmérico

  • Sr. Member
  • ****
  • Posts: 458
Re: FortesReport and SQLite
« Reply #6 on: April 05, 2010, 11:15:59 pm »
I've uploaded to svn a sqlite version of the basic demo

JanRoza

  • Hero Member
  • *****
  • Posts: 538
    • http://www.silentwings.nl
Re: FortesReport and SQLite
« Reply #7 on: April 06, 2010, 07:10:03 am »
Thanks Luiz,

I'll try it out this evening when I'm home.

OS: Windows 10 (64 bit) / Ubuntu 19.04 (64 bit)
Laz: Lazarus 2.0.4 FPC 3.0.4 i386-win32-win32/win64

JanRoza

  • Hero Member
  • *****
  • Posts: 538
    • http://www.silentwings.nl
Re: FortesReport and SQLite
« Reply #8 on: April 07, 2010, 07:08:01 am »
I ran the SQLite demo and that runs fine, but my report in my own app still will not run.
The only really difference between my report and the one Luiz provided is that in my report two tables are used: one table (which has only one record) is used only for filling some fields in the report header. The other table with many records is only used in the detail band. The SQLite example report uses just one table. Another difference is that my report has no grouping as it should be a simple list of all detail records.
Could these differences explain why my report doesn't run?

I really hope we can solve this as the more I see of FortesReport the more I like it, it has more potential than LazReport already.

Luiz, maybe my sources need your eagle eyes again to spot my mistake?
OS: Windows 10 (64 bit) / Ubuntu 19.04 (64 bit)
Laz: Lazarus 2.0.4 FPC 3.0.4 i386-win32-win32/win64

JD

  • Hero Member
  • *****
  • Posts: 1758
Re: FortesReport and SQLite
« Reply #9 on: April 07, 2010, 07:16:20 am »
I ran the SQLite demo and that runs fine, but my report in my own app still will not run.
The only really difference between my report and the one Luiz provided is that in my report two tables are used: one table (which has only one record) is used only for filling some fields in the report header. The other table with many records is only used in the detail band. The SQLite example report uses just one table. Another difference is that my report has no grouping as it should be a simple list of all detail records.
Could these differences explain why my report doesn't run?

I don't know if that is the reason why your report does not run BUT I have a suggestion. Since one table is just used for filling some fields in the report header (which I assume does not change from page to page), why don't you replace it with a query? That may do the trick.

I have a report I created (using Fortes) that uses a table in the detail band as well as the results of a stored procedure in the same detail band! The stored procedure is called from a query for every new page, the results change from page to page & it works fine.

Windows (10, 7) - Lazarus 2.0RC3/FPC 3.2, NewPascal, Delphi

Indy 10.6 series; mORMot; Zeos 7.2.1; SQLite, Firebird, PostgreSQL & MariaDB; VirtualTreeView 5.5.3 R1

JanRoza

  • Hero Member
  • *****
  • Posts: 538
    • http://www.silentwings.nl
Re: FortesReport and SQLite
« Reply #10 on: April 07, 2010, 09:18:41 am »
You mean a query in the report?
Stored procedures, I didn't know SQLite had that capability.
Can you give an explain in more detail how you used the query for your report header?
OS: Windows 10 (64 bit) / Ubuntu 19.04 (64 bit)
Laz: Lazarus 2.0.4 FPC 3.0.4 i386-win32-win32/win64

LuizAmérico

  • Sr. Member
  • ****
  • Posts: 458
Re: FortesReport and SQLite
« Reply #11 on: April 07, 2010, 10:36:14 am »
Quote
The SQLite example report uses just one table.

Did you look at NFDesign unit. It uses four datasets.

DatasetNF is attached to the main report
DatasetINF is used in the subdetail band

Try replacing these datasets by yours

The other two datasets are for two lookup values

JD

  • Hero Member
  • *****
  • Posts: 1758
Re: FortesReport and SQLite
« Reply #12 on: April 07, 2010, 11:42:42 am »
You mean a query in the report?
Stored procedures, I didn't know SQLite had that capability.
Can you give an explain in more detail how you used the query for your report header?

I'm not sure SQLite supports stored procedures. I was just citing an example.

Let's consider a hypothetical example. If the report header contains say Customer name while the report detail contains Orders, use a query to obtain the customer name from the customer table & save it to a report variable like TRLLabel AND then use databound report variables like TRLDBText in the detail section to display the contents of the Orders table.

The two tables must be linked on a CustNo private key.

To repeat this for many pages, use two loops

a) an outer loop that retrieves the Customer names
b) an inner loop that fills the detail band

Call the Fortes report unit from inside the inner loop & make it display ONLY the current dataset in the inner loop.

So at any point in time when the report is called, the report variables will contain the current customer number & his orders.

In my code below, the line
Code: [Select]
   // Query the stored procedure 'MEMBER_TOTALS'
    frmPrintMember.RLlblTotal.Caption
       := CountMember('MEMBER_TOTALS', strBegin, strEnd);

is where I query another table or a stored procedure to get a value I need for my report. My report unit contains no code at all. All the work is done in the procedure below which prepares & then displays the report

Code: [Select]
{----------------------------------------------------------------------
 
                      Procedure for the Member report

-----------------------------------------------------------------------}
procedure TfrmMember.PrintPreviewClick(Sender: TObject);
var
  strBegin, strEnd, strNow: string;
begin
  // Set the default year of the 'Year' spin button to the current year
  strNow := DateToStr(Now);     // Convert the current datetime value to a string
  strBegin := '01/01/' + Copy(strNow, 7, Length(strNow)) + '';
  strEnd := '31/12/' + Copy(strNow, 7, Length(strNow)) + '';

  // Create the print Member form, show it modally & finally destroy it
  frmPrintMember := TfrmPrintMember.Create(Self);
  try
    // Get the values for the lookup fields
    frmPrintMember.RLlblCountry.Caption := dblkupCountry.Caption;
    frmPrintMember.RLlblPartner.Caption := dblkupPartner.Caption;

    // Query the stored procedure 'MEMBER_TOTALS'
    frmPrintMember.RLlblTotal.Caption
       := CountMember('MEMBER_TOTALS', strBegin, strEnd);

    // Display the report on the screen showing the current record
    with dmDataMod.dsMember do
    begin
      frmPrintMember.RLReport.Preview();
      frmPrintMember.RLReport.ClosePreview;
    end;
  finally
    FreeAndNil(frmPrintMember);
  end;
end;
 
« Last Edit: April 07, 2010, 11:58:42 am by JD »
Windows (10, 7) - Lazarus 2.0RC3/FPC 3.2, NewPascal, Delphi

Indy 10.6 series; mORMot; Zeos 7.2.1; SQLite, Firebird, PostgreSQL & MariaDB; VirtualTreeView 5.5.3 R1

JanRoza

  • Hero Member
  • *****
  • Posts: 538
    • http://www.silentwings.nl
Re: FortesReport and SQLite
« Reply #13 on: April 08, 2010, 08:12:01 am »
I finally got it working.
It turns out the original report made was okay but I had forgotten to fill in a datasource value in the report properties. I did have a datasource entry with all the individual fields in the detail band but to get the report to loop through all records and display them in the detail band you need to enter a data source at report level as well.
Furthermore I replaced the detail band with a detailgrid band.
At last success  ;D

Learned a lot again those last few days!
 
OS: Windows 10 (64 bit) / Ubuntu 19.04 (64 bit)
Laz: Lazarus 2.0.4 FPC 3.0.4 i386-win32-win32/win64

JD

  • Hero Member
  • *****
  • Posts: 1758
Re: FortesReport and SQLite
« Reply #14 on: April 08, 2010, 10:10:59 am »
.... to get the report to loop through all records and display them in the detail band you need to enter a data source at report level as well.
Furthermore I replaced the detail band with a detailgrid band.
At last success  ;D

Learned a lot again those last few days!

I'm happy to hear you finally got it to work. I hope you enjoy using Fortes Report.  :D
Windows (10, 7) - Lazarus 2.0RC3/FPC 3.2, NewPascal, Delphi

Indy 10.6 series; mORMot; Zeos 7.2.1; SQLite, Firebird, PostgreSQL & MariaDB; VirtualTreeView 5.5.3 R1