FortesReport and SQLite
lazarus / 16-Databases

----------------------------------------------------------------------
JanRoza · 2010-04-04 22:09

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

----------------------------------------------------------------------
fredycc · 2010-04-05 02:21

FortesReport is good but documentation is poor, check lazreport is
great and easy.

----------------------------------------------------------------------
tatamata · 2010-04-05 08:35

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 · 2010-04-05 09:59

Quote from: tatamata on April 05, 2010, 10:35:42 amWhere 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

----------------------------------------------------------------------
JD · 2010-04-05 10:02

Quote from: JanRoza on April 05, 2010, 12:09:56 amCan 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

----------------------------------------------------------------------
JanRoza · 2010-04-05 11:49

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.

----------------------------------------------------------------------
LuizAmérico · 2010-04-05 21:15

I've uploaded to svn a sqlite version of the basic demo

----------------------------------------------------------------------
JanRoza · 2010-04-06 05:10

Thanks Luiz,

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

----------------------------------------------------------------------
JanRoza · 2010-04-07 05:08

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?

----------------------------------------------------------------------
JD · 2010-04-07 05:16

Quote from: JanRoza on April 07, 2010, 07:08:01 amI 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.

----------------------------------------------------------------------
JanRoza · 2010-04-07 07:18

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?

----------------------------------------------------------------------
LuizAmérico · 2010-04-07 08:36

QuoteThe 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 · 2010-04-07 09:42

Quote from: JanRoza on April 07, 2010, 09:18:41 amYou 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;

----------------------------------------------------------------------
JanRoza · 2010-04-08 06:12

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 

Learned a lot again those last few days!

----------------------------------------------------------------------
JD · 2010-04-08 08:10

Quote from: JanRoza on April 08, 2010, 08:12:01 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 

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.

----------------------------------------------------------------------
vfclists · 2010-04-14 11:14

If the documentation is on a website Google Translate might help.

Quote from: JanRoza on April 05, 2010, 12:09:56 am>
>snipped
>
FortesReport is a great product, but is in high need of good, english
and complete documentation.


Jan