Databases Reference
In-Depth Information
If we want to get the record set corresponding to the previous year to whatever year
the user selects, we would use:
Sum({$<Year = {$(=Max(Year)-1)}>} [# Departures Performed])
In this case, the DSE is first calculating the last possible year from the current
selections record set. It goes back one year and the output is then passed to the set
modifier as the field value definition. Anytime the user changes his selections, the set
modifier is changed as well.
At this point, we must reinforce our recommendation that it is very
important to let the user know exactly what records the calculation
is taking into account. By doing so, we will avoid confusion as
it might result counter-intuitive for the user to see values being
calculated that are not associated to his selections.
The same concept also works with Quarters, Months, Days, and so on. However,
additional considerations need to be made for some fields. For example, if we were
to use the previous calculation for the
Month
field,
Max(Month) - 1
would not
work if the user selects
January
. The expression would return zero, which is not a
valid month. Although we can easily build an expression that returns the number
12 instead of zero (with the use of the
If
function, for instance), we will provide an
even simpler and straightforward solution for this scenario in an upcoming section
of this chapter (Point In Time Reporting).
More assignment operators
All of the preceding examples use set expressions which have predefined field values
in the element list definitions, overriding the user selections on the specified fields.
However, in some cases, we will need to first take the actual record set that the user
has selected and, from there, modify it by adding or removing certain values. To do
that, we need to use a different assignment operator in the field-value definition,
instead of the equal sign. The available assignment operators are:
•
=
: This is what we have been using, and it simply redefines the selection for a
certain field.
•
+=
: This operator implicitly defines a union between the selected field values
and the ones we specify in the element list.
•
-=
: This operator implicitly defines an exclusion of the values we specify
from the values the user has selected.
