|
<< Click to display table of contents >> ReportWorkshop Overview | Data Queries |
Data query is a text string containing a query.
A report generation mainly consists of executions of data queries and applying them to the corresponding part of the report template document.
Data queries may be associated with:
•the whole report (TRVReportDocObject.DataQuery)
•a group of table rows (TRVRowGenerationCustomRule.DataQuery)
•a table cell (TRVReportTableCellData.DataQuery)
•Query() function in expressions
•(there are also data queries for building columns of cross-tab reports, but we skip them here for simplicity)
A content of a report template is nested in each other: any table is inserted in the root document, cells are inserted in a table, another table may be inserted in a cell. Thus the root document's query (if defined) is a parent query for table row queries (if they exist), they are, in their order, parents for cell queries (if they are defined), cell queries are parents for queries of rows of tables inserted in these cells, and so on.
A report generator starts to execute a root query; the corresponding content is replicated as many times as many records are returned when executing this query. In each copy of this content, child queries are executed, and so on.
The complete hierarchy of queries is the following:
1.the root document's query
2.queries of row generation rules of report tables
3.queries of row generation sub-rules of report tables, then sub-rules of these sub-rules, and so on
4.queries of cells of report tables
The levels 2-4 are repeated for each nested table.
Additionally, Query() function may be inserted in any text, and Query() functions may be nested.
In all levels of this hierarchy, child objects are inserted in parent objects. The level 2 is an exception: rules and sub-rules are applied to the same table, parent rules are applied to larger table fragment than their child rules.
As it was said, data queries can be associated with nested objects. A master-detail relationships may be established between parent and child objects.
Example 1: SQL
Strings of data queries may contain fields referring to results of execution of parent queries. Thus you can implement master-detail or banded reports.
For example, the root data query may be:
select id, master_name from mastertable
This report may include a table with a row generation rule having the data query:
select * from detailtable where master_id={id}
In this example, {id} will be replaced to the value of "id" field from mastertable.
This is an universal method that can be used not only in SQL, but in all types of queries.
Example 2: nested datasets
If the master table has a field representing a nested dataset (TDataSetField), you can refer to this field using a data query like:
field:details
where 'field' is a reserved prefix, 'details' is a dataset field name.
The syntax of such data queries is explained below.
Example 3: master-detail relationship between existing TDataSet components
Let we have two components inherited from TDataSet, DataSet1 and DataSet2, and a master-detail relationship is established between them.
Let we use TRVReportDBDataProvider component, and it has two items in DataSets collection: ('mastertable', DataSet1) and ('detailtable', DataSet2).
Now the report template can use data queries
mastertable
and
detailtable
to use data from these datasets.
Data queries are processed by query processors. A syntax of data queries depends on the query processors which you plan to use in your application.
The main way to create query processors for data queries is assigning a data provider component to the report generator's DataProvider property. Report Workshop includes several data providers that allow processing data queries by retrieving records from database tables. So the most typical example of a data query is SQL SELECT statement.
Another way is registering "standard" query processors for execution of queries that start from some prefix. This prefix may contain English characters, '-', '_', and must be finished with ':'.
As an example, Report Workshop includes a query processor for executing queries like 'calendar:days of month 3 of 2016'. See the topic about extending Report Workshop.
One prefix is reserved: 'field'. It allows using a nested dataset as a query processor.
The syntax of this query is:
field:<full data field name>
where <full data field name> is defined in the same way as in data fields.
All '{' characters having the following '}' are treated as field codes. To prevent it, write '{{' instead of '{'.
This type of escaping is not convenient if you use JSON queries, because they may contain many '{' and '}' characters.
If your data query does not contain fields, you can use TRVReportGenerator.EscapeDataQuery to prepare queries.