Index...
Centrallix Documentation
|
report/query
query :: A definition of a SQL query to use elsewhere in the report
Metadata:
| type: | report/query |
| visual: | no |
| container: | no |
Overview:
The query is used to define a SQL statement that will be used to obtain data from the ObjectSystem. It in and of itself does not actually run the query, but must be invoked by a table or form elsewhere in the report.
Queries can be linked to each other in order to depend on each others' data results.
Queries can also contain aggregate definitions, which are similar to 'COMPUTE BY' statements in other SQL languages, and calculate aggregate values as a the report progresses. These aggregates are normally used to provide summary data at the end of the query or at the end of a part of the query.
Usage:
Queries can only be contained within a 'system/report'. They can only contain 'report/aggregate' components.
Properties:
| Property | Type | Description |
|---|
| sql |
string |
A SQL statement. It can be a plain string, or an expression if the SQL is to be constructed dynamically. The expressions inside the SQL statement (such as inside the WHERE clause or the SELECT item list) can reference parameters of the report using :this:parametername, and can reference values from other queries using :queryname:fieldname. See "SQL Language" for more details on Centrallix SQL. Though deprecated, the SQL can also include report parameter substitution by embedding '&xyz' in the report to reference the parameter 'xyz' (do not use). |
| link |
stringvec |
Deprecated. Two strings, one specifying the query to link to, and the second specifying the name of the column/field to reference in that query. In the SQL statement, to use this method to reference linked data from other queries, use '&1' and '&2' (and so forth) to reference the first and second links, respectively. |
Child Properties:
(of report/aggregate child widgets)
| Property | Type | Description |
|---|
| compute |
string |
An expression specifying the computation to be performed. Normally this should include one or more aggregate functions (sum, count, avg, min, and max) in some form or fashion. |
| where |
string |
A conditional expression specifying which rows should be included in the aggregate computation. |
| reset |
integer |
Either 0 or 1 (the default). Controls whether or not the aggregate (computed) value is reset when used. Normally, whenever the value is referenced, it is automatically reset to 0 (count) or null (sum/avg/min/max/first/last). Setting this value to 0 causes the value to accumulate during the entire time the report is running, and is useful for such things as numbering lines (when using count()) or generating a running total (with sum()). |
Sample Code:
myQuery "report/query"
{
// standard SQL statement referencing parameter 'minsize'
sql = "select :name, :size from /samples where :size >= :this:minsize";
// a couple of aggregates for totaling things up
totalsize "report/aggregate" { compute = "sum(:size)"; }
bigfilesizetotal "report/aggregate" { compute = "sum(:size)"; where=":size > 10000"; }
}
myQueryTwo "report/query"
{
// dynamically built SQL illustration
sql = runserver("select :name, :size from /samples where :size >= :this:minsize" + condition(:this:orderby_size == 1, " order by :size", " order by :name"));
}
Comments...
(none yet)
Add a Comment...
|
