Connecting a report to data from FastReport in Lazarus

In the previous article about working with charts and graphs, we were already familiar with business graphics and barcodes. Now it is time to complicate the task and work with different data sources. Most reports are typically based on data from databases. To access this data Lazarus provides powerful mechanisms which are used by FastReport. 

Data access components

This refers to the components in the DataAccess tab, which can act as data sources for the report. Any TDataSet successor component can be used for this purpose.

In addition to accessing data defined in the project, FastReport allows you to create new components in run-time. Our company's principles for creating data access components are very similar to those used in the Lazarus environment - we impose a component on the form and configure its properties in the object inspector. Functionality of run-time connections is a little more limited in the choice of database formats, so at the time of writing this article, only DBF and SqLite3 can be created in run-time.

This refers to the components in the DataAccess tab that use the TfrxDBDataSet connector component from the FastReportn tab to connect a table or other data source to the band. This component acts as an intermediary between the data source and the FastReport core. The component is responsible for navigating through records and accessing fields. This allows you to avoid tying the FastReport core to any data access library.

FastReport can work simultaneously with BDE and any other library, or retrieve data at all from a source not associated with the database, such as an array or file.

The TfrxDataSet component is designed to work with data sources, and its versatility should be noted. It can use all connections inherited from TDataSet. And this is BDE, CVS and the vast majority of other libraries! To work with other data sources (array, file, etc.) the TfrxUserDataSet component is used.

To link a TfrxDBDataSet component to a data source, one of the options must be configured:

• The DataSet property, which links directly to a table or query.
• The DataSource property, which connects to the TDataSource component.

Both methods of connection are equivalent, just the first does without the TDataSource component.

In order to make the component and its associated data available in the report, you need to explicitly specify which data sources are used in the report. In the FastReport designer, select the menu item "Report/Data..." and in the window that appears, check the boxes next to the required sources.

Description of components in the FastReport designer

The TfrxDBFTable component is designed to organize access to a .dbf database table and has the following properties:

• FilePath specifies the path to the database folder.
• TableName assigns the name of the database. By selecting a database, you will also change the path.
• FieldAliases allows you to specify custom field names.
• Filter contains an expression to filter the records.
• Filtered determines whether to apply a filter.
• IndexFieldNames stores the names of the fields which form the index.
• IndexName defines the name of the secondary index.
• MasterFields includes the fields associated with the master dataset.
• Master is the same as the master dataset.
• UserName sets the Alias (user name) of the dataset.

The component's property assignments are the same as the TDbf properties in Lazarus. To connect the component to the database table, just fill in the TableName property. Open the table by setting Active := True.

FieldAliases property editor allows you to select the fields which will be available when accessing the table, and set custom name for each field and for the whole table.

The MasterFields property editor is used to create master-detail relationships between two tables. To link two tables with a master-detail relationship in a slave table, specify the master table in the Master property and invoke the MasterFields property editor. If the table has secondary indexes to be used, first configure the IndexName property.

This editor allows you to visually link the master and detail fields of datasets. When sets are linked by a master-detail relationship, the contents of the detail set are filtered as you navigate through the master set, so that it contains only records that are relevant to the current master set record.

To link set fields, select the field from the list on the left (detail set), then the field from the list on the right (master set), and click "Add" button. This will move the field mapping to the lower list. To clear the bottom list, use the "Clear" button. It is important to note that the fields to be linked must be of the same type and be key.

But in Lazarus itself these properties do not work with selections (Master-Detail sorting and filtering) for TDbf components, and since we use this component, it doesn't work for us either.

The TfrxLazSqliteQuery component is designed to make SQL queries to the database using SqLite3 and has the following properties:

• Database specifies the database connection name (default is always "sqlLite").
• FieldAliases allows you to specify custom field names.
• Filter stores an expression to filter the records.
• Filtered determines whether to apply a filter.
• Master is the same as Master dataset.
• Params displays a list of query parameters.
• SQL includes the query text.
• UserName sets the alias (user name) of the dataset.
• IgnoreDupParams - if true then names of query parameters will not be duplicated in the parameter editor.

The SQL property has its own editor for filling the SQL query: 

The Params property also has its own editor. It is available if the query text contains parameters.

A parameter can be of two types: the one assigned from the master data set and the other one with a specific value, where the value can be a constant, a reference to a variable or an object property. 

In case the parameter is taken from the master dataset, TfrxLazSqliteQuery.Master property must be configured. The dataset must contain a field with the same name as the parameter. It is not necessary to specify parameter type and value.

TfrxLazSqliteDataBase component is used to connect to SqLite3 database. Its purpose is similar to the TSQLite3Connection and TSqlTransaction components, and it has the following properties:

• Connected - when True is active, the connection is activated.
• DatabaseName allows you to select a name for the database.
• LoginPrompt defines whether the user must be asked for a password when connecting to the database. If LoginPrompt = False, then user name and password must be specified in the connection string.

Building reports with database access

Consider building a simple report that contains data access components, where we will use the LDemo demonstration database as an example.

To begin, let's create a project, with which we will conduct our experiments. Make a new project in Lazarus and place on the form components TfrxReport, TfrxDesigner, TfrxDialogControls, TfrxDBDataset, TDbf.

Configure the connection to the database. To do that, find the TableName property of Dbf1 and in the dialog box, choose the database file - anything with a .dbf extension will work. Then set frxDBDataset1 property DataSet to "Dbf1".

Then add the button to the form and enter the following code in OnClick:

1
2
3
4

procedure TForm1.Button1Click(Sender: TObject);
begin
 frxReport1.DesignReport;
end;

Remember to allow threads for the Linux project. This was described in the installation article. After that, compile and run the project. Nothing else is required to create the end-user report designer.

When you click on Design, the designer opens with an empty report. Consider building simple reports in this environment. By the way, at this point you already know how to connect databases to FR from Lazarus.

A simple "List" type report

We will now output data from one database table already using FR at runtime. Do the following steps to build the report:

1. Click the "New Report" button on the Designer toolbar. FastReport will create an empty report containing pages "Code", "Data", "Page1".
2. Switch to the "Data" page and put the "DBF Table" component on the page:

3. Select the database to be connected. To do this, set the TableName property in the Object Inspector by selecting the customer.dbf file from the demo project.
4. To connect the Level 1 Data bank to the table, double-click on it and select our table in the window that will open. Then drag and drop the desired fields from the Data Tree window onto the report sheet. Our report will then look something like this:

To view the resulting report, click on the "Preview" button on the toolbar. 

Useful data handling options

The Data tab isn't just for data access components. Using the Text and Drawing objects, you can place explanatory labels and draw simple diagrams, as shown in the figure below: