Thursday, July 21, 2011

Data Model Interface - Formula/Summ Columns

Data Model Interface - Formula Columns

Formula Columns can be placed both within a column listing or
independently within the GUI window.

When placed within the query the Formula will automatically be
initialised when the record changes, when outside a query the
Formula will continue to accrue values for the entire report.

The Formula is in effect a PL/SQL function which can be reached via
the Formula’s property, this in effect can perform a calculation and
‘return’ that value to the Formula variable …

• For efficiency, the use of a formula column should be limited to
instances where it is not possible to use a SELECT statement to
perform the calculation.

• Formula columns should not be used to set a parameter’s value.

Data Model Interface - Summary Columns
By its very nature a Summary Column should not be placed at
the same level as the column(s) it is actually ‘summing’ instead it
should be positioned in the group above or at Report level.

The reset level property will initialise the value whenever a
record changes in that group.

As in the case of Formulas, Summaries should not be created if
either the SQL query or the built-in Report totalling can be used.

Summary Columns can be placed both within a column listing or
independently within the GUI window.

Summary Columns can be created for all datatypes, however
Summaries have greater scope when applied to numeric columns.

• The Summary types are ….
– Sum
– Average
– Minimum
– Maximum
– Count
– First
– Last
– % of Total
– Std Deviation
– Variance

Data Model Interface - Placeholder Columns
Placeholder columns are storage areas in the Data Model, they can
be populated by a Before Report trigger but are generally
populated by a Formula.

Although Placeholders allow PL/SQL to be written within them,
they cannot be ‘self-populated’, their value must be set by an
external Program Unit, any attempts at ‘self-population’ will result
in a runtime compilation error. However the Formula assigned to
the Placeholder must return a value.

To complicate matters, if a Placeholder is defined at Report level
then it can only be populated by a Formula at Report Level or the
above trigger. In many ways using Parameters to perform a
Placeholders functionality is a better option.

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect