This node describes one
<Variable> node.
It has no children nodes, only attributes.
<Variable ... />
The name of the variable. It must be
unique in the list of variables for
the parent <Report> node.
<Variable name="var1" />
The "value" of the variable, or rather, the expression from which the value is computed. Variables' values are computed for every data row produced by the report query. The expression may therefore reference field names of queries that are declared in the XML description or in programming code.
<Variable value="q1.field1 + 2 * q2.field2" />
The type of the variable. Several variable types exist:
<Variable value="q1.field" type="expression"/> <Variable value="q1.field" type="count"/> <Variable value="q1.field" type="countall"/> <Variable value="q1.field" type="sum"/> <Variable value="q1.field" type="average"/> <Variable value="q1.field" type="averageall"/> <Variable value="q1.field" type="highest"/> <Variable value="q1.field" type="lowest"/> <Variable type="custom" ... />
Default type is expression.
This is just a shortcut for the computed value
of the expression that saves both typing (in other
expressions referencing this variable) and time
to generate the report. This can be considered
a manual optimization.
The count and
countall variable
types count the number of expression
results for the data set. The former
leaves out NULL values, the latter includes them.
This is equivalent to COUNT(field)
and COUNT(*) in SQL.
The sum variable type
sums the non-NULL values of the expression
results for the data set.
The average and
averageall variable types
are combinations of sum
and either count or
countall. They take the value
computed for each data row, add them together,
and divide by the number of values. The result
of average and
averageall may differ if
there is NULL data in the result set.
The highest and
lowest variable types
return the highest and the lowest values
for the data set, respectively.
All of the above pre-defined variables types work on numeric data.
The custom variable type
allow arbitrary user variables if the
predefined types are not enough, for example,
when the base type needs to be something else
then a number. See below.
Here's a complete example of an expression variable:
<Report>
<Variables>
<Variable
name="var1"
value="query1.field1 + query2.field2"
type="expression" />
</Variables>
<Detail>
<FieldHeaders>
<literal value="'My variable'" />
</FieldHeaders>
<FieldDetails>
<field value="v.var1" />
</FieldDetails>
</Detail>
</Report>
Note, that in this simple example, there is
no difference if the variable is used in
the <field> or the
query1.field1 + query2.field2
expression. The efficiency of not computing
the variable again for the same data row can be
observed when the variable is used multiple times
and the report processes a huge data set.
Here's a complete example of using a variable:
<Report>
<Variables>
<Variable
name="var1"
value="r.self + query1.field1 + query2.field2"
type="expression" />
</Variables>
<Detail>
<FieldHeaders>
<literal value="'My variable'" />
</FieldHeaders>
<FieldDetails>
<field value="v.var1" />
</FieldDetails>
</Detail>
</Report>
The trick is to use the r.self
internal variable.
Please note, that the above example will
not work as is, because for the first row,
there is no previous row.
But there is a trick to avoid such problems,
namely using the Ternary operator
(or its equivalent, the iif() function) and the
rownum() to perform only safe
computations. (Note that the value=...
part below is a single line.)
<Variable>
...
value="rownum() == 1 ?
query1.field1 + query2.field2 :
r.self + query1.field1 + query2.field2"
...
</Variable>
This example shows the correct operation of an iterative expression. For the first row, set a known good value. For every subsequent rows, the previous row value may be used for deriving the new value from.
The above spelled out example can also be written
as a summing variable:
<Report>
<Variables>
<Variable
name="var1"
value="query1.field1 + query2.field2"
type="sum" />
</Variables>
<Detail>
<FieldHeaders>
<literal value="'My variable'" />
</FieldHeaders>
<FieldDetails>
<field value="v.var1" />
</FieldDetails>
</Detail>
</Report>
Here are two examples of the
count and
countall variable types:
<Report>
<Variables>
<Variable
name="var1"
value="query1.field1"
type="count" />
<Variable
name="var2"
value="query1.field1"
type="countall" />
</Variables>
</Report>
Here are two examples of using the
average and
averageall
variable types:
<Report>
<Variables>
<Variable
name="var1"
value="query1.field1"
type="average" />
<Variable
name="var2"
value="query1.field1"
type="averageall" />
</Variables>
</Report>
Here are two examples of using
highest and
lowest variable types:
<Report>
<Variables>
<Variable
name="var1"
value="query1.field1"
type="highest" />
<Variable
name="var2"
value="query1.field1"
type="lowest" />
</Variables>
</Report>
These attributes below define a custom variable. A base expression, up to two intermediary expressions and one result expression may be defined, together with the expression type.
<Variable
baseexpr="..."
intermedexpr="..."
intermed2expr="..."
resultexpr="..."
basetype="..."
type="custom"/>
baseexpr, intermedexpr,
intermed2expr and resultexpr
are Expressions.
Iterative or recursive variables can use Expression self reference.
Possible values for basetype are
number, string
or datetime.
It's the user's responsibility to use expressions valid for the base type. Failing that, the result value will be an appropriate error message.
Note that the baseexpr attribute
is an alias for value.
For example, the average variable
works this way behind the scenes as written below.
<Report>
<Variables>
<Variable
name="averagevar1"
type="custom"
basetype="number"
baseexpr="query1.field1"
intermedexpr="(rownum() == 1 ? 0 : r.self) +
(isnull(r.baseexpr) ?
0 : r.baseexpr)"
intermed2expr="r.self +
(isnull(r.baseexpr) ? 0 : 1)"
resultexpr="r.intermedexpr / r.intermed2expr"
/>
</Variables>
</Report>
A variable may be reset on break boundaries to the
base expression value, e.g. 0 for
count and other pre-defined variable
types. See Break node and
Report breaks.
<Variable resetonbreak="break1" />
Default is unset, i.e. no reset on a break.
Here's an example to use a variable that's value is reset on a break boundary:
<Report>
<Breaks>
<Break name="break1" ... >
<BreaksHeader>
<Output>
<field value="v.var1" />
</Output>
</BreaksHeader>
<BreaksFields>
<BreaksField value="query1.field2" />
</BreaksFields>
</Break>
</Breaks>
<Variables>
<Variable
name="var1"
value="query1.field1"
type="average"
precalculate="yes"
resetonbreak="'break1'" />
</Variables>
...
</Report>
A variable may work two ways. The first way is to generate an immediate value that is valid for the current row. See Expressions. An expression may reference the value computed for previous data row, see Expression self reference.
The other way is Precalculated variables. The attribute is accepted under two names:
<Variable precalculate="yes" /> <Variable delayed="yes" />
Default is no.
A precalculated variable may also use the Reset on break attribute. In this case, the precalculated value is computed for each break range separately.
Precalculated variables may be used to show totals in
e.g. Report header,
in <FieldHeaders> in
Detail node,
in BreakHeader
and in
Report page header,
among other places.
Here's an example of a precalculated variable:
<Report>
<Variables>
<Variable
name="var1"
value="query1.field1"
type="average"
precalculate="yes" />
</Variables>
<ReportHeader>
<Output>
<field value="v.var1">
</Output>
</ReportHeader>
</Report>
To reveal the internals of a variable
that's value is reset on break boundaries,
here is the equivalent using a
custom variable.
The value returned by the Break row number function
automatically resets at every break boundary, so it
can be used as below.
<Variables>
<Variable
name="var1"
type="custom"
baseexpr="query1.field1"
intermedexpr="(brrownum('break1') == 1 ? 0 : r.self) +
(isnull(r.baseexpr) ?
0 : r.baseexpr)"
intermed2expr="r.self +
(isnull(r.baseexpr) ? 0 : 1)"
resultexpr="r.intermedexpr / r.intermed2expr"
/>
</Variables>