HP Storage Essentials Storage Resource Management Report Optimizer Software 6.0 Building Reports Using the Web Intelligence Java Report Panel (August 2008)

Building Reports Using the Web Intelligence Java Report Panel 347
You can type this variable name into a formula or drag the variable to the Formula toolbar
as you would for any report object.
Example: Create a formula to return a statistical variance
Variance is a statistical term. The variance of a set of values measures the spread of those
values around their average. Web Intelligence has the function
Var() that calculates the
variance in one step, but manual calculation of variance provides a good example of how
to simplify a complex formula using variables. To calculate the variance manually you
need to:
• calculate the average number of items sold
• calculate the difference between each number of items sold and the average, then
square this value
• add up all these squared differences
• divide this total by the number of values - 1
You have a report showing numbers of items sold by quarter and you want to include the
variance. Without the use of variables to simplify it, this formula is as follows:
Sum((([Quantity sold] - Average([Quantity sold] ForEach [Quarter]) In
Report)*([Quantity sold] - Average([Quantity sold] ForEach [Quarter])
In Report)) In [Quarter])/(Count ([Quantity sold] ForEach [Quarter])
- 1)
This formula is clearly unwieldy. By using variables you can simplify it to:
Sum ([Difference Squared])/[Number of Observations] - 1)
which is much easier to understand. This simplified version of the formula gives you a
high-level view of what the formula is doing, rather than plunging you into the confusing
details. You can then examine the formulas of the variables referenced in the high-level
formula to understand its component parts.
For example, the formula references the variable Difference Squared, which itself
references the variable Average Sold. By examining the formulas of Difference Squared
and Average sold, you can drill down into the formula to understand the details of what it
is doing.
Simplifying a variance formula with variables
As the example above shows, there are several steps involved in creating a variance
formula. You encapsulate each of these steps in a variable. The variables you create are:
• average number of items sold
• number of observations (that is, the number of separate values of the number of items
sold)
• difference between an observation and the average, squared
• sum of these differences divided by the number of observations - 1