SAP Business objects Enterprise XI 3.0 Using Functions, Formulas and Calculations in Web Intelligence
Using Functions, Formulas and
Calculations in Web Intelligence
BusinessObjects Enterprise XI 3.0
Copyright
© 2008 Business Objects. All rights reserved. Business Objects owns the following
U.S. patents, which may cover products that are offered and licensed by Business
Objects: 5,555,403; 5,857,205; 6,289,352; 6,247,008; 6,490,593; 6,578,027;
6,831,668; 6,768,986; 6,772,409; 6,882,998; 7,139,766; 7,299,419; 7,194,465;
7,222,130; 7,181,440 and 7,181,435. Business Objects and the Business Objects
logo, BusinessObjects, Business Objects Crystal Vision, Business Process On
Demand, BusinessQuery, Crystal Analysis, Crystal Applications, Crystal Decisions,
Crystal Enterprise, Crystal Insider, Crystal Reports, Desktop Intelligence, Inxight,
the Inxight Logo, LinguistX, Star Tree, Table Lens, ThingFinder, Timewall, Let
there be light, Metify, NSite, Rapid Marts, RapidMarts, the Spectrum Design, Web
Intelligence, Workmail and Xcelsius are trademarks or registered trademarks in
the United States and/or other countries of Business Objects and/or affiliated
companies. All other names mentioned herein may be trademarks of their respective
owners.
Third-party
Contributors
Business Objects products in this release may contain redistributions of software
licensed from third-party contributors. Some of these individual components may
also be available under alternative licenses. A partial listing of third-party
contributors that have requested or permitted acknowledgments, as well as required
notices, can be found at: http://www.businessobjects.com/thirdparty
2008-02-08
Contents
About this guide 7Chapter 1
Using standard and custom calculations 9Chapter 2
Using standard and custom calculations in your reports...........................10
Understanding calculation contexts 23Chapter 3
What are calculation contexts?..................................................................24
Default calculation contexts.......................................................................27
Modifying the default calculation context with extended syntax................34
Standard calculations...........................................................................10
Using formulas to build custom calculations........................................11
Working with functions.........................................................................13
The input context..................................................................................24
The output context................................................................................25
Default contexts in a vertical table........................................................29
Default contexts in a horizontal table...................................................30
Default contexts in a crosstab..............................................................30
Default contexts in a section................................................................32
Default contexts in a break...................................................................33
Specifying input and output contexts in the same formula...................35
Extended syntax context operators......................................................35
Web Intelligence extended syntax keywords.......................................38
Web Intelligence functions, operators and keywords 49Chapter 4
Web Intelligence functions.........................................................................50
Using Functions, Formulas and Calculations in Web Intelligence 3
Contents
Aggregate functions.............................................................................50
Character functions..............................................................................77
Date and Time functions......................................................................96
Data Provider functions......................................................................109
Document functions............................................................................122
Logical functions.................................................................................134
Numeric functions...............................................................................145
Misc functions.....................................................................................168
Web Intelligence function and formula operators....................................184
Mathematical operators......................................................................184
Conditional operators.........................................................................185
Logical operators................................................................................185
Function-specific operators................................................................190
Extended syntax context operators....................................................198
Web Intelligence extended syntax keywords...........................................202
The Block keyword.............................................................................203
The Body keyword..............................................................................204
The Break keyword............................................................................205
The Report keyword...........................................................................206
The Section keyword..........................................................................207
Troubleshooting Web Intelligence formulas 209Chapter 5
Formula error and information messages................................................210
#CONTEXT........................................................................................210
#DATASYNC......................................................................................210
#DIV/0................................................................................................211
#INCOMPATIBLE...............................................................................211
#MULTIVALUE...................................................................................211
#OVERFLOW.....................................................................................212
#PARTIALRESULT.............................................................................212
#RANK...............................................................................................212
4 Using Functions, Formulas and Calculations in Web Intelligence
Contents
#RECURSIVE....................................................................................213
#SECURITY.......................................................................................213
#SYNTAX...........................................................................................214
#TOREFRESH...................................................................................214
#UNAVAILABLE.................................................................................214
#ERROR............................................................................................215
Calculating values with smart measures 217Chapter 6
Smart measures defined..........................................................................218
Grouping sets and smart measures........................................................218
How Web Intelligence manages grouping sets..................................219
Smart measures and the scope of analysis.............................................220
Smart measures and SQL.......................................................................220
Grouping sets and the UNION operator.............................................220
Smart measures and formulas................................................................223
Smart measures and dimensions containing formulas......................223
Smart measures in formulas..............................................................223
Smart measures and filters......................................................................224
Smart measures and filters on dimensions........................................224
Smart measures and drill filters..........................................................225
Get More Help 227Appendix A
Index 231
Using Functions, Formulas and Calculations in Web Intelligence 5
Contents
6 Using Functions, Formulas and Calculations in Web Intelligence
About this guide
1
About this guide
1
The Using Functions, Formulas and Calculations in Web Intelligence guide
provides detailed information on the advanced calculation capabilities in Web
Intelligence. It also provides a syntax reference to the Web Intelligence
functions and operators.
The guide presents this information generically, without reference to the Web
Intelligence interface. For information on how to work with calculation-related
features in your Web Intelligence documents (for example, how to add a
variable or a formula to a report), see Performing On-Report Analysis With
Web Intelligence, Building Reports with the Java Report Panel and Web
Intelligence Rich Client User's Guide.
8 Using Functions, Formulas and Calculations in Web Intelligence
Using standard and custom calculations
2
Using standard and custom calculations
2
Using standard and custom calculations in your reports
Using standard and custom calculations
in your reports
You can use standard calculation functions to make quick calculations on
the data in Web Intelligence reports. If standard calculations are not sufficient
for your needs, you can use the Web Intelligence formula language to build
custom calculations.
Standard calculations
You can use standard calculation functions to make quick calculations on
the data in Web Intelligence reports. The following standard calculations are
available:
DescriptionCalculation
Sum
Count
Minimum
Maximum
Calculates the sum of the selected data.
Counts all rows for a measure object
or count distinct rows for a dimension
or detail object.
Calculates the average of the data.Average
Displays the minimum value of the selected data.
Display the maximum value of the selected data.
10 Using Functions, Formulas and Calculations in Web Intelligence
Percentage
Default
Using standard and custom calculations
Using standard and custom calculations in your reports
DescriptionCalculation
Displays the selected data as a percentage of the total. The results of the percentage are displayed in an additional
column or row of the table.
Note: Percentages are calculated for
the selected measure compared to the
total results for that measure on the
table or break. To calculate the percentage of one measure compared to another measure, you need to build a
custom calculation.
Applies the default aggregation function
to a standard measure, or the database
aggregation function to a smart measure.
2
When you apply a standard calculation to a table column, the calculation
result appears in a footer in the column. Web Intelligence adds a footer for
the result of each calculation if you apply multiple calculations to the same
column.
Using formulas to build custom calculations
Custom calculations allow you to add additional calculations to your report
beyond its base objects and the standard calculations provided by Web
Intelligence.
You add a custom calculation by writing a formula that Web Intelligence
evaluates when you run the report. A formula can consist of base report
variables, functions, operators and calculation contexts.
A custom calculation is a formula that can consist of report objects, functions
and operators. Formulas have a calculation context that you can specify
explicitly if you choose. (For more information, see What are calculation
contexts? on page 24.)
Using Functions, Formulas and Calculations in Web Intelligence 11
Using standard and custom calculations
2
Using standard and custom calculations in your reports
Example: Showing average revenue per sale
If you have a report with Sales Revenue and Number Sold objects and you
want to add revenue per sale to the report. The calculation [Sales Rev
enue]/[Number Sold] gives this value by dividing the revenue by the
number of items sold in order to give the revenue per item.
Using variables to simplify formulas
If a formula is complex you can use variables to simplify it. By using variables
you break a complex formula down into manageable parts and make it much
easier to read, as well as making building formulas much less error-prone.
You can use previously-created variables in a formula in exactly the same
way as you use other report objects. Variables appear in the formula editor
under the “Variables” folder.
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)
12 Using Functions, Formulas and Calculations in Web Intelligence
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.
Working with functions
A custom calculation sometimes contains report objects only, for example
[Sales Revenue]/[Number of Sales]. Calculations can also include
functions in addition to report objects.
Using standard and custom calculations
Using standard and custom calculations in your reports
2
A function receives zero or more values as input and returns output based
on those values. For example, the Sum function totals all the values in a
measure and outputs the result. The formula Sum([Sales Revenue]) outputs
a total of sales revenues. In this case, the function input is the Sales Revenue
measure and the output is the total of all Sales Measures.
Related Topics
• Web Intelligence function and formula operators on page 184
• Web Intelligence functions on page 50
Including functions in cells
The text in report cells always begins with ‘=’ . Literal text appears in quotation
marks, while formulas appear without quotation marks. For example, the
formula Average([Revenue]) appears in a cell as =Average([Revenue]).
The text “Average Revenue” appears as =”Average Revenue”
Using Functions, Formulas and Calculations in Web Intelligence 13
Using standard and custom calculations
2
Using standard and custom calculations in your reports
You can use text alone in a cell, or mix formulas and text by using the ‘+’
operator. If you want a cell to display the average revenue preceded by the
text “Average Revenue”, the cell text is as follows: =”Average Revenue: ”
+ Average([Revenue])
Note the space at the end of the text string so that the text and the value are
not placed directly side-by-side in the cell.
Function prototypes
To use a function you need to know its name, how many input values it
requires and the data types of these input values. You also need to know
the type of data that the function outputs.
For example, the Sum function takes a numerical object as input (for example
a measure showing sales revenue) and outputs numeric data (the sum of
all the values of the measure object).
This description of a function’s inputs and outputs it known as its prototype.
Here is the prototype of the Abs function:
number Abs (number input_number)
This prototype tells you that the Abs function takes a single number (in
put_number) as input and returns a number as output.
The Formula Editor displays the function prototype when you select the
function.
Examples of functions
Example: Showing prompt input with the UserResponse function
You have a report showing Year, Quarter and Sales revenue. The State
object also appears in the report data, although it is not displayed. When
the user runs the report they are presented with a prompt and they must
choose a state. You want to show the state that they have chosen in the
report title. If your data provider is called “eFashion” and the text in the
prompt is “Choose a State”, the formula for the title is:
"Quarterly Revenues for " + UserResponse( "eFashion";"Choose
a State")
14 Using Functions, Formulas and Calculations in Web Intelligence
The report is as follows:
Using standard and custom calculations
Using standard and custom calculations in your reports
2
Example: Calculating a percentage using the Percentage function
Web Intelligence has the Percentage function for calculating percentages.
This function calculates the percentage of a number in relation to its
surrounding context. For example, the following table shows revenues by
year and quarter. The percentage column contains the formula Percentage
([Sales Revenue]).
Using Functions, Formulas and Calculations in Web Intelligence 15
Using standard and custom calculations
2
Using standard and custom calculations in your reports
In this case the function calculates each revenue as a percentage of the
total revenue. The surrounding context is the total revenue; this is the only
revenue figure that is relevant outside the breakdown by year and quarter
in the table.
If the report is split into sections by year, the surrounding context outside
the table becomes the total revenue in the section.
If the Percentage cell is placed outside the table but still inside the section,
the surrounding context becomes the total revenue. In this case the
Percentage function calculates the total revenue for the section as a
percentage of the total overall revenue.
16 Using Functions, Formulas and Calculations in Web Intelligence
Using standard and custom calculations
Using standard and custom calculations in your reports
Example: Calculating a percentage using the Sum function
2
You can gain more control over the context in which a percentage is
calculated by using the Sum function rather than the Percentage function.
If you divide one figure in a set of figures by the total of those figures, you
get its percentage of the total; for example, the formula [Sales
Revenue]/Sum([Sales Revenue]) gives the sales revenue as a percentage
of the total revenue.
In the following table the Percentage of Total column has the formula:
[Sales revenue]/(Sum([Sales revenue] In Report))
and the Percentage of Year column has the formula:
[Sales revenue]/(Sum([Sales revenue] In Section))
Using Functions, Formulas and Calculations in Web Intelligence 17
Using standard and custom calculations
2
Using standard and custom calculations in your reports
These formulas take advantage of the extended syntax keywords Report
and Section to instruct the Sum function to calculate the overall total revenue
and yearly revenue respectively. (For more information, see Modifying the
default calculation context with extended syntax on page 34.)
Simplifying a variance formula with variables
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 [Quar
ter]) In Report)*([Quantity sold] - Average([Quantity sold]
ForEach [Quarter]) In Report)) In [Quarter])/(Count ([Quantity
sold] ForEach [Quarter]) - 1)
which is clearly unwieldy.
Creating the variance formula
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
The variable formulas are as follows:
18 Using Functions, Formulas and Calculations in Web Intelligence
Using standard and custom calculations
Using standard and custom calculations in your reports
FormulaVariable
2
Average Sold
Number of Observations
Difference Squared
Variance
The final formula is now
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.
Average([Quantity Sold] In ([Quarter]))
In Report
Count([Quantity Sold] In ([Quarter])) In
Report
Power(([Quantity sold] - [Average
Sold]);2)
Sum([Difference Squared] In ([Quarter]))/([Number of Observations] - 1)
Web Intelligence function and formula operators
Operators link the various components in a formula. Formulas can contain
mathematical, conditional, logical, function-specific or extended syntax
operators.
Mathematical operators
Mathematical operators are familiar from everyday arithmetic. There are
addition (+), subtraction (-), multiplication (*), division (/) operators that allow
you to perform mathematical operations in a formula. The formula [Sales
Using Functions, Formulas and Calculations in Web Intelligence 19
Using standard and custom calculations
2
Using standard and custom calculations in your reports
Revenue] - [Cost of Sales]contains a mathematical operator, in this
case subtraction.
Note: When used with character strings, the ‘+’ operator becomes a string
concatenation operator. That is, it joins character strings. For example, the
formula “John” + “ Smith” returns ‘John Smith’.
Conditional operators
Conditional operators determine the type of comparison to be made between
values. The following table describes them:
DescriptionOperator
Equal to=
Greater than>
Less than<
Greater than or equal to>=
Less than or equal to<=
Not equal to!=
You use conditional operators with the If function, as in:
If ([Revenue] >= 10000;‘High’;‘Low’)
which returns “High” for all rows where the revenue is greater than or equal
to 10000 and “Low” for all other rows.
Logical operators
Logical operators are used in expressions that return True or False. You use
such expressions in the If function. The Web Intelligence logical operators
are AND, OR, NOT, Between and InList. For example, the formula
If ([Resort] = ‘Bahamas Beach’ OR [Resort]=’Hawaiian Club’;
‘US’; ‘France’)
returns “US” if the resort is “Bahamas Beach or “Hawiian Club”, “France”
otherwise.
20 Using Functions, Formulas and Calculations in Web Intelligence
The formula
[Resort] = ‘Bahamas Beach’ OR [Resort]=’Hawaiian Club’
returns True or False, True if the Resort variable is equal to ‘Bahamas Beach’
or ‘Hawaiian Club’, False otherwise.
Context operators
Context operators form part of extended calculation syntax. Extended syntax
allows you to define which dimensions a measure or formula takes into
account in a calculation.
Function-specific operators
Some Web Intelligence functions can take specific operators as arguments.
For example, the Previous function can take the SELF operator.
Using standard and custom calculations
Using standard and custom calculations in your reports
2
Using Functions, Formulas and Calculations in Web Intelligence 21
Using standard and custom calculations
Using standard and custom calculations in your reports
2
22 Using Functions, Formulas and Calculations in Web Intelligence
Understanding calculation contexts
3
Understanding calculation contexts
3
What are calculation contexts?
What are calculation contexts?
The calculation context is the data that a calculation takes into account to
generate a result. Web Intelligence, this means that the value given by a
measure is determined by the dimensions used to calculate the measure.
A report contains two kinds of objects:
• Dimensions represent business data that generate figures. Store outlets,
years or regions are examples of dimension data. For example, a store
outlet, a year or a region can generate revenue: we can talk about revenue
by store, revenue by year or revenue by region.
• Measures are numerical data generated by dimension data. Examples
of measure are revenue and number of sales. For example, we can talk
about the number of sales made in a particular store.
Measures can also be generated by combinations of dimension data. For
example, we can talk about the revenue generated by a particular store in
2005.
The calculation context of a measure has two components:
• the dimension or list of dimensions that determine the measure value
• the part of the dimension data that determines the measure value
The calculation context has two components:
•
The input context (see The input context on page 24)
•
The output context (see The output context on page 25)
The input context
The input context of a measure or formula is the list of dimensions that feed
into the calculation.
The list of dimensions in an input context appears inside the parentheses of
the function that outputs the value. The list of dimensions must also be
enclosed in parentheses (even if it contains only one dimension) and the
dimensions must be separated by semicolons.
24 Using Functions, Formulas and Calculations in Web Intelligence
Understanding calculation contexts
What are calculation contexts?
Example: Specifying an input context
In a report with Year sections and a block in each section with Customer
and Revenue columns, the input contexts are:
Input contextReport part
YearSection header and block footers
Year, CustomerRows in the block
In other words, the section headers and block footers show aggregated
revenue by Year, and each row in the block shows revenue aggregated by
Year and Customer (the revenue generated by that customer in the year
in question).
When specified explicitly in a formula, these input contexts are:
Sum ([Revenue] In ([Year]))
3
Sum ([Revenue] In ([Year];[Customer]))
That is, the dimensions in the input context appear inside the parentheses
of the function (in this case, Sum) whose input context is specified.
The output context
The output context causes the formula to output a value is if it is placed in
the footer of a block containing a break.
Example: Specifying an output context
The following report shows revenue by year and quarter, with a break on
year, and the minimum revenue calculated by year:
Using Functions, Formulas and Calculations in Web Intelligence 25
Understanding calculation contexts
3
What are calculation contexts?
What if you want to show the minimum revenue by year in a block with no
break? You can do this by specifying the output context in a formula. In this
case, the formula looks like this:
Min ([Revenue]) In ([Year])
That is, the output context appears after the parentheses of the function
whose output context you are specifying. In this case, the output context
tells Web Intelligence to calculate minimum revenue by year.
If you add an additional column containing this formula to the block, the
result is as follows:
26 Using Functions, Formulas and Calculations in Web Intelligence
Understanding calculation contexts
Default calculation contexts
You can see that the Min By Year column contains the minimum revenues
that appear in the break footers in the previous report.
Notice that in this example, the input context is not specified because it is
the default context (Year, Quarter) for the block. In other words, the output
context tells Web Intelligence which revenue by year and quarter to output.
In full, with both input and output formulas explicitly specified, the formula
looks like this:
Min ([Sales Revenue] In([Year];[Quarter])) In ([Year])
3
Explained in words, this formula tells Web Intelligence to “calculate revenues
by year by quarter, then output the smallest of these revenues that occurs
in each year”.
What would happen if you did not specify the output context in the Min by
Year column? In this case, these figures would be identical to the figures
in the Sales Revenue column. Why? Remember that the default context in
a block includes the dimensions in that block. The minimum revenue by
year by quarter is the same as the revenue by year by quarter simply,
because there is only one revenue for each year/quarter combination.
Default calculation contexts
Depending on where you place a measure or formula, Web Intelligence
assigns a default calculation context to the measure.
Using Functions, Formulas and Calculations in Web Intelligence 27
Understanding calculation contexts
3
Default calculation contexts
Measures are semantically dynamic. This means that the figures returned
by a measure depend on the dimensions with which it is associated. This
combination of dimensions represents the calculation context.
Web Intelligence associates a default context with a measure depending on
where the measure is placed. You can change this default context with
extended syntax. In other words, you can determine the set of dimensions
used to generate a measure. This is what is meant by defining the calculation
context.
Example: Default contexts in a report
This example describes the default calculation context of the measures in
a simple report. The report shows revenue generated by customers and is
split into sections by year.
Total:80002005
RevenueCustomer
1000Harris
3000Jones
4000Walsh
8000Total:
Report total: 8000
The table below lists the calculation context of the measures in this report:
20000Report total
28 Using Functions, Formulas and Calculations in Web Intelligence
ContextValueMeasure
Total of all revenue in the
report
Year8000Section header total
Related Topics
• What are calculation contexts? on page 24
• Modifying the default calculation context with extended syntax on page 34
Default contexts in a vertical table
A vertical table is a standard report table with headers at the top, data going
from top to bottom and footers at the bottom. The default contexts in a down
table are:
Understanding calculation contexts
Default calculation contexts
ContextValueMeasure
Year;Customer1000, 3000, 4000Customer total
Year8000Block footer total
3
When the calculation is
in the...
The dimensions and
Header
Body of the block
Footer
Example: Default contexts in a vertical table
The following table shows the default contexts in a vertical table:
Using Functions, Formulas and Calculations in Web Intelligence 29
measures used to generate the body of the block
The dimensions and
measures used to generate the current row
The dimensions and
measures used to generate the body of the block
The output context isThe input context is
All the data is aggregated
then the calculation function returns a single value
The same as the input
context
All the data is aggregated
then the calculation function returns a single value
Understanding calculation contexts
3
Default calculation contexts
Default contexts in a horizontal table
A horizontal table is like a vertical table turned on its side. Headers appear
at the left, data goes left to right and footers appear at the right. The default
contexts for a horizontal table are the same as those for a vertical table.
Default contexts in a crosstab
A crosstab displays data in a matrix with measures appearing at the
intersections of dimensions. The default contexts in a crosstab are:
The calculation is in
the...
The dimensions and
Header
Body of the block
30 Using Functions, Formulas and Calculations in Web Intelligence
measures used to generate the body of the block.
The dimensions and
measures used to generate the body of the block.
The output context is...The input context is...
All the data is aggregated, then the calculation
function returns a single
value.
The same as the input
context.
Loading...