Users can apply ceiling or floor functions in Strategy Developer 9.x to round metric values up or down. Ceiling or floor functions take only one argument. An argument is a metric representing a list of real numbers.
Use ceiling calculations in situations where results are expressed as specified units of measurement. For example, to avoid returns at the cent level, CEILING could be used on a product price to round up the figures to the nearest dollar.
Use floor calculations in situations where results are expressed as specified units of measurement. For example, to avoid returns at the cent level, FLOOR could be used on a product price to round down the figures to the nearest dollar.
The chart below shows an example of floor and ceiling values for various arguments:
Refer to the graph below for an example of ceiling and floor functionality:
To create a ceiling metric, go to the metrics folder and create a new metric. The ceiling function is found under Schema Objects > Functions and Operators > Functions > OLAP Functions. Fill in the function parameters appropriately by adding a metric, (Metric), and then the Ceiling (Metric) as the external parameter, as shown in the following image. Save and close the metric and add it to the report:
To create a floor metric, go to the metrics folder and create a new metric. The floor function is found in Schema Objects > Functions and Operators > Functions > OLAP Functions. Fill in the function parameters appropriately by adding a metric, (Metric), and then the floor (Metric) as the external parameter, as shown in the following image. Save and close the metric and add it to the report.
Notice in the report SQL that there is no difference or database round functionality, as shown in the example below:
select a13. AS CATEGORY_ID,
max(a14.)AS
CATEOGORY_DESC,
Avg(a11.)AS WJXBFS1
from a11,
a12,
a13,
a14
where a11. = a12. and
a12. = a13. and
a13. = a14.
group by a13.
