Report Editor - Sub Report : Map Fields Dialog Help

Using the Map Fields Dialog, you can set correspondence between records, returned by the query of the main report, and records, returned by the query of the subreport.

• MainReport query fields – Pop-up menu that contains field names from query of the main report.
• SubReport query fields – Pop-up menu that contains field names from query of the subreport.

When map fields is declared, reports engine will substitute records in the subreport, that satisfy the condition specified in the map fields, for the current record in the main report. That is, in the subreport will be shown records for which satisfying the condition of equality values of the fields from map fields, for the current record.

Suppose you have a database with tables Customers and Orders:

As example, we will create a report, that will contain a list of customers, and list of orders for each customer.

• Create a query for report with list of orders.

       SELECT * FROM Orders

• Create a query for report with list of customers.

       SELECT * FROM Customers

• Create a report, that will print the list of orders (it will be a subreport).

• Create a report, that will print the list of customers (it will be a main report).

Now you need to bind data in subreport with data in main report using, the map of fields. For this - select property map_fields in the properties inspector (make mouse click on edit button for the property, to open the dialog with map of fields). To bind data in the subreport and main report:

• Set the first item of MainReport query fields to ID.
• Set the first item of SubReport query fields to Customer_Ptr.

As shown on the image below:

That's all! Data is binded. To check go to the preview mode of the report:

The subreport prints records that corresponds to the map of fields.

The reports engine generates a content of a report consistently, record by record. A data for the main report is taken from the source query. A data for the subreport is taken from the specifc query, that generated by reports engine from the source query of the subreport, and from the map of fields, that defines a relations between data in the reports. The reports engine adds to the query of the subreport the condition of the selection, that defined by the map fields.

Than, the query for the subreport will have next structure:

SELECT * FROM (SELECT * FROM Orders) AS foo WHERE "Customer_Ptr"=?

In the query instead of ?, the database engine substitutes the current value of a field of the query of main report, that mapped to the associated field of the subreport query. In this example it will be the value of ID field.