FD Query

FD Query is the most commonly used data component in Articles. It runs a SQL SELECT statement against your database and returns the results to the report. You can use FD Query to fetch anything from a single value to thousands of rows across multiple tables.

FD Query requires an FD Database component to be present in the report. The FD Database defines the connection — FD Query uses it to know where to run the SQL.

Adding an FD Query to Your Report

Make sure your report already has an FD Database component configured and connected
Drag an FD Query component onto the designer surface
In the Object Inspector, set the Database property to your FD Database component
Click the SQL property and enter your SELECT statement
Set Active to True to test the query and load the fields

Once Active is True, the fields returned by the query are available to bind to report bands and objects.

Writing SQL

Enter your SQL in the SQL property. Articles accepts standard SQL SELECT statements. The exact SQL syntax depends on your database — refer to your database documentation for specifics.

A simple example:

SELECT
  Customers.CustomerID,
  Customers.CustomerName,
  Customers.Balance
FROM Customers
WHERE Customers.Balance > 0
ORDER BY Customers.CustomerName

You can write the SQL on one line or across multiple lines — Articles accepts both. Using multiple lines makes the SQL easier to read and maintain.

Properties

Database
The FD Database component this query uses to connect. You must set this before the query can run. If your report has only one FD Database, Articles may set this automatically when you add the FD Query.

SQL
The SQL SELECT statement to run. Click the property to open the SQL editor. See Writing SQL above.

Active
Set to True to open the query and fetch data. Set to False to close it. During report design, set Active to True to preview the data and make fields available for binding. When the report runs, Articles manages Active automatically.

ReadOnly
When True, the dataset does not allow edits. Default is True for reporting. Leave this True unless you have a specific reason to edit data from a report.

UniDirectional
When True, the dataset can only scroll forward through records. This uses less memory and can be faster for large result sets. Default is False. Set to True for large reports where you do not need to scroll backwards.

CachedUpdates
Enables FireDAC cached update mode. Default is False. Leave this False for standard reporting use.

Filter
A filter expression applied to the result set after the data is fetched. This filters rows in memory without changing the SQL. Useful for simple filtering that does not need a round-trip to the database.

Filtered
Set to True to activate the Filter expression. Set to False to show all rows regardless of the Filter value.

MacroCreate
Controls whether FireDAC automatically creates macro entries when it finds ! tokens in the SQL. Leave this at its default unless you are working with advanced macro scenarios.

MacroExpand
Controls whether FireDAC expands macros in the SQL. Leave this at its default. Articles manages macro expansion through its own system — see Using Macros.

Parameters

Parameters let you pass values into your SQL at runtime without changing the SQL text itself. A parameter is written as a colon followed by a name, for example :CustomerID.

Example SQL using a parameter:

SELECT
  Invoices.InvoiceNumber,
  Invoices.InvoiceDate,
  Invoices.Amount
FROM Invoices
WHERE Invoices.CustomerID = :CustomerID

When Articles runs the query, it replaces :CustomerID with the actual value before sending the SQL to the database. This is safer and more reliable than building the value into the SQL string directly.

To configure parameters, find the Params property in the Object Inspector. Each parameter has a Name, DataType, and Expression. The Expression is evaluated at runtime to get the value — it can be a report variable, a dialog control value, or a fixed value.

For full details see Using Parameters.

Macros

Macros are a more powerful substitution system that lets you swap out parts of the SQL itself at runtime — not just values, but table names, column lists, WHERE clauses, and more. A macro is written as an exclamation mark followed by a name, for example !TableName.

Example SQL using a macro:

SELECT * FROM !TableName WHERE Active = 1

At runtime Articles replaces !TableName with the value from the macro’s Expression before running the query. This lets one report work against different tables depending on a user selection.

Macros also support conditional blocks using {IF !MacroName} and {FI} tags. If the macro has a value the block is included, if not it is removed. This lets you build SQL that optionally includes WHERE clauses or JOIN statements.

For full details and examples see Using Macros.

Master Detail Setup

Master Detail allows two FD Query components to be linked so that when the master query moves to a new row, the detail query automatically re-runs and returns only the rows that belong to that master row.

A common example is a check report where the master query fetches the check and the detail query fetches the invoices paid by that check. As the report moves through each check, the detail query automatically returns only the invoices for that check.

To set up Master Detail you need a parameter in the detail query’s SQL that matches the linking field. For example if you are linking on PostOrder:

Master query SQL:

SELECT
  Checks.PostOrder,
  Checks.Reference,
  Checks.TrxName,
  Checks.Amount
FROM Checks

Detail query SQL:

SELECT
  ChecksPaid.Reference,
  ChecksPaid.InvoiceDate,
  ChecksPaid.DueDate,
  ChecksPaid.InvoiceAmount,
  ChecksPaid.AmountPaid
FROM ChecksPaid
WHERE ChecksPaid.PostOrder = :PostOrder

Then on the detail FD Query set these properties:

Master — set to the DataSource of your master FD Query
MasterFields — set to PostOrder=PostOrder (master field name = detail parameter name)

When the report runs, every time the master query advances to a new check, the detail query automatically re-runs with the new PostOrder value and returns only that check’s invoices.

For a full walkthrough with band setup see Master Detail Setup and Example — Master Detail Check Report.

Events

FD Query exposes several events you can handle in the report’s PascalScript to respond to dataset activity.

BeforeOpen
Fires just before the query opens and fetches data. Use this to set parameter values programmatically, modify the SQL at the last moment, or perform any setup that needs to happen before data is fetched.

AfterOpen
Fires after the query has opened and data is available. Use this to read the row count, reposition the cursor, or trigger other actions that depend on the data being loaded.

BeforeScroll
Fires before the cursor moves to a new row. Rarely needed in reports but available for advanced scenarios.

AfterScroll
Fires after the cursor moves to a new row. Use this to react to the current row changing — for example to update a running total or trigger a calculation.

OnFilterRecord
Fires for each row when Filtered is True. Use this to apply custom filtering logic in script when a simple Filter expression is not enough. Set the Accept parameter to False to exclude a row.

To attach a handler, click the event property in the Object Inspector and type the name of your script procedure. Articles will create the procedure stub in the script editor for you.

Example BeforeOpen handler that sets a parameter from a report variable:

procedure FDQuery1BeforeOpen(DataSet: TDataSet);
begin
  FDQuery1.Params.ParamByName('StartDate').AsDateTime := StrToDate(StartDateEdit.Text);
end;

Saving Query Results to a File

FD Query can save its current result set to a file from PascalScript. This is useful for exporting data or creating a snapshot of the results for later use.

Four formats are supported:

SaveAsJSON — saves in FireDAC JSON format
SaveAsXML — saves in FireDAC XML format
SaveAsBinary — saves in FireDAC binary format
SaveAsCSV — saves as a comma-separated text file

Example from PascalScript:

FDQuery1.SaveAsCSV('C:\Reports\Export\customers.csv');
FDQuery1.SaveAsJSON('C:\Reports\Export\customers.json');

The query must be open (Active = True) before calling any save method. For CSV import and export details see CSV Import and Export. For JSON, XML and Binary formats see Saving Data to Files.

Common Problems

The query opens but shows no data
Check your WHERE clause. If you are using a parameter, make sure the parameter has a value set. An unset parameter may match nothing or cause an error depending on your database.

“Parameter not found” error when the report runs
The SQL contains a :ParameterName token but no matching parameter is defined. Open the Params property and check that a parameter with that exact name exists.

The detail query shows all rows instead of filtering by the master
Check two things. First, make sure the detail SQL has a WHERE clause with a parameter that matches the link field, for example WHERE PostOrder = :PostOrder. Second, make sure the MasterFields property is set correctly using the format MasterFieldName=ParameterName.

Macro values are not being substituted in the SQL
Check that the macro Expression is set in the Macros property. If the expression is a literal SQL value rather than a script expression, you may need to set IgnoreExprErr to True in the report’s Engine Options. See Using Macros for details.

Articles Report Writer