Business objects ENTERPRISE 4.0 User Manual

Using functions, formulas and calculations in interactive analysis

■ SAP BusinessObjects Enterprise XI 4.0

2010-11-16

Copyright

© 2010 SAP AG. All rights reserved.SAP, R/3, SAP NetWeaver, Duet, PartnerEdge, ByDesign, SAP
Business ByDesign, and other SAP products and services mentioned herein as well as their respective
logos are trademarks or registered trademarks of SAP AG in Germany and other countries. Business
Objects and the Business Objects logo, BusinessObjects, Crystal Reports, Crystal Decisions, Web
Intelligence, Xcelsius, and other Business Objects products and services mentioned herein as well
as their respective logos are trademarks or registered trademarks of Business Objects S.A. in the
United States and in other countries. Business Objects is an SAP company.All other product and
service names mentioned are the trademarks of their respective companies. Data contained in this
document serves informational purposes only. National product specifications may vary.These materials
are subject to change without notice. These materials are provided by SAP AG and its affiliated
companies ("SAP Group") for informational purposes only, without representation or warranty of any
kind, and SAP Group shall not be liable for errors or omissions with respect to the materials. The
only warranties for SAP Group products and services are those that are set forth in the express
warranty statements accompanying such products and services, if any. Nothing herein should be
construed as constituting an additional warranty.

2010-11-16

Contents

About this guide......................................................................................................................7Chapter 1

Using standard and custom calculations................................................................................9Chapter 2

2.1

2.2

2.2.1

2.3

2.3.1

2.3.2

2.3.3

2.3.4

3.1

3.1.1

3.1.2

3.2

3.2.1

3.2.2

3.2.3

3.2.4

3.2.5

3.3

3.3.1

3.3.2

Standard calculations...............................................................................................................9

Using formulas to build custom calculations...........................................................................10

Using variables to simplify formulas........................................................................................10

Working with functions...........................................................................................................11

Including functions in cells......................................................................................................11

Function syntax......................................................................................................................11

Examples of functions............................................................................................................12

Function and formula operators..............................................................................................16

Understanding calculation contexts......................................................................................19Chapter 3

Calculation contexts defined..................................................................................................19

The input context...................................................................................................................19

The output context.................................................................................................................20

Default calculation contexts...................................................................................................22

Default contexts in a vertical table..........................................................................................23

Default contexts in a horizontal table......................................................................................24

Default contexts in a crosstab................................................................................................24

Default contexts in a section..................................................................................................25

Default contexts in a break.....................................................................................................26

Modifying the default calculation context with extended syntax..............................................27

Extended syntax operators.....................................................................................................27

Extended syntax keywords.....................................................................................................31

4.1

4.2

4.2.1

4.3

4.4

Calculating values with smart measures...............................................................................39Chapter 4

Smart measures defined........................................................................................................39

Grouping sets and smart measures........................................................................................39

Management of grouping sets................................................................................................40

Smart measures and the scope of analysis............................................................................40

Smart measures and SQL......................................................................................................41

2010-11-163

Contents

4.4.1

4.5

4.5.1

4.5.2

4.6

4.6.1

4.6.2

4.6.3

4.6.4

5.1

5.1.1

5.1.2

5.1.3

5.1.4

5.1.5

5.1.6

5.1.7

5.1.8

5.1.9

5.2

5.2.1

5.2.2

5.2.3

5.2.4

5.2.5

5.2.6

5.3

5.3.1

5.3.2

5.3.3

5.3.4

5.3.5

5.4

5.5

Grouping sets and the UNION operator.................................................................................41

Smart measures and formulas................................................................................................43

Smart measures and dimensions containing formulas............................................................43

Smart measures in formulas...................................................................................................43

Smart measures and filters....................................................................................................44

Smart measures and filters on dimensions.............................................................................44

Filtering smart measures........................................................................................................45

Smart measures and drill filters..............................................................................................46

Smart measures and nested OR filters...................................................................................46

Functions, operators and keywords......................................................................................47Chapter 5

Functions...............................................................................................................................47

Aggregate functions...............................................................................................................47

Character functions................................................................................................................70

Date and Time functions.........................................................................................................85

Data Provider functions..........................................................................................................94

Document functions.............................................................................................................104

Logical functions..................................................................................................................112

Numeric functions................................................................................................................119

Set functions........................................................................................................................138

Misc functions......................................................................................................................147

Function and formula operators............................................................................................164

Mathematical operators.......................................................................................................165

Conditional operators...........................................................................................................165

Logical operators.................................................................................................................165

Function-specific operators..................................................................................................168

Extended syntax operators...................................................................................................175

Set operators.......................................................................................................................179

Extended syntax keywords...................................................................................................180

The Block keyword...............................................................................................................180

The Body keyword...............................................................................................................181

The Break keyword..............................................................................................................182

The Report keyword.............................................................................................................183

The Section keyword...........................................................................................................184

Rounding and truncating numbers........................................................................................185

Referring to members and member sets in hierarchies.........................................................186

6.1

6.1.1

Troubleshooting formulas...................................................................................................189Chapter 6

Formula error and information messages..............................................................................189

#COMPUTATION................................................................................................................189

2010-11-164

Contents

6.1.2

6.1.3

6.1.4

6.1.5

6.1.6

6.1.7

6.1.8

6.1.9

6.1.10

6.1.11

6.1.12

6.1.13

6.1.14

6.1.15

6.1.16

6.1.17

6.1.18

7.1

7.2

7.2.1

7.2.2

7.2.3

7.2.4

7.2.5

#CONTEXT..........................................................................................................................189

#DATASYNC.......................................................................................................................190

#DIV/0.................................................................................................................................190

#EXTERNAL.........................................................................................................................190

#INCOMPATIBLE................................................................................................................191

#MIX....................................................................................................................................191

#MULTIVALUE....................................................................................................................191

#OVERFLOW......................................................................................................................192

#PARTIALRESULT...............................................................................................................192

#RANK................................................................................................................................192

#RECURSIVE......................................................................................................................192

#REFRESH...........................................................................................................................193

#SECURITY.........................................................................................................................193

#SYNTAX............................................................................................................................193

#TOREFRESH......................................................................................................................194

#UNAVAILABLE..................................................................................................................194

#ERROR..............................................................................................................................194

Comparing values using functions......................................................................................195Chapter 7

Comparing values using the Previous function.....................................................................195

Comparing values using the RelativeValue function..............................................................195

Slicing dimensions and the RelativeValue function...............................................................196

Slicing dimensions and sections...........................................................................................198

Order of slicing dimensions..................................................................................................200

Slicing dimensions and sorts................................................................................................202

Using RelativeValue in crosstabs.........................................................................................203

More Information.................................................................................................................205Appendix A

Index 207

2010-11-165

Contents

2010-11-166

About this guide

About this guide

The

Using functions, formulas and calculations in interactive analysis

on the advanced calculation capabilities available in when you perform interactive analysis. It also
provides a syntax reference to the available functions and operators.

guide provides detailed information

2010-11-167

About this guide

2010-11-168

Using standard and custom calculations

Using standard and custom calculations

You can use standard calculation functions to make quick calculations on data. If standard calculations
are not sufficient for your needs, you can use the formula language to build custom calculations.

2.1 Standard calculations

You can use standard calculation functions to make quick calculations on data. The following standard
calculations are available:

Count

Percentage

Default

DescriptionCalculation

Calculates the sum of the selected data.Sum

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.Minimum

Display the maximum value of the selected data.Maximum

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.

2010-11-169

Using standard and custom calculations

When you apply standard calculations to table columns, the calculation results appear in footers. One
footer is added for each calculation.

2.2 Using formulas to build custom calculations

Custom calculations allow you to add additional calculations to your report beyond its base objects and
standard calculations.

You add a custom calculation by writing a formula. 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.

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 Revenue]/[Number Sold] gives this value by dividing
the revenue by the number of items sold in order to give the revenue per item.

Related Topics

• Calculation contexts defined

2.2.1 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.

Related Topics

• Simplifying a variance formula with variables

2010-11-1610

Using standard and custom calculations

2.3 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.

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

• Function and formula operators

• Functions

2.3.1 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?"

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.

2.3.2 Function syntax

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).

Here is the syntax of the Abs function:

num Abs(number)

2010-11-1611

Using standard and custom calculations

This syntax tells you that the Abs function takes a single number as input and returns a number as
output.

The Formula Editor displays the function syntax when you select the function.

2.3.3 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")

The report is as follows:

2010-11-1612

Using standard and custom calculations

Example: Calculating a percentage using the Percentage function

The Percentage function calculates 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]).

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.

2010-11-1613

Using standard and custom calculations

Example: Calculating a percentage using the Sum function

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))

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.

Related Topics

• Modifying the default calculation context with extended syntax

2010-11-1614

Using standard and custom calculations

2.3.3.1 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. The Var function 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 complex formula is as follows:

Sum((([Quantity sold] - Average([Quantity sold] ForEach [Quarter]) In Report)*([Quantity sold] - Average([Quan
tity sold] ForEach [Quarter]) In Report)) In [Quarter])/(Count ([Quantity sold] ForEach [Quarter]) - 1)

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:

FormulaVariable

Average([Quantity Sold] In ([Quarter])) In ReportAverage Sold

Count([Quantity Sold] In ([Quarter])) In ReportNumber of Observations

Power(([Quantity sold] - [Average Sold]);2)Difference Squared

Variance

Sum([Difference Squared] In ([Quarter]))/([Number
of Observations] - 1)

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.

2010-11-1615

Using standard and custom calculations

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.

2.3.4 Function and formula operators

Operators link the various components in a formula. Formulas can contain mathematical, conditional,
logical, function-specific or extended syntax operators.

2.3.4.1 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 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".

2.3.4.2 Conditional operators

Conditional operators determine the type of comparison to be made between values.

DescriptionOperator

Equal to=

Greater than>

Less than<

Greater than or equal to>=

Less than or equal to<=

Not equal to<>

2010-11-1616

Using standard and custom calculations

You use conditional operators with the If function, as in:

If [Revenue]>10000 Then "High" Else "Low"

which returns “High" for all rows where the revenue is greater than or equal to 10000 and “Low" for all
other rows.

2.3.4.3 Logical operators

The logical operators are And, Or, Not, Between and Inlist. Logical operators are used in boolean
expressions, which return True or False.

2.3.4.4 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.

2.3.4.5 Function-specific operators

Some functions can take specific operators as arguments. For example, the Previous function can
take the Self operator.

All functions use ) and ( to enclose function arguments. Functions that accept multiple parameters
use ; to separate the parameters.

2010-11-1617

Using standard and custom calculations

2010-11-1618

Understanding calculation contexts

Understanding calculation contexts

3.1 Calculation contexts defined

The calculation context is the data that a calculation takes into account to generate a result. 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

• The output context

Related Topics

• The input context

• The output context

3.1.1 The input context

The input context of a measure or formula is the list of dimensions that feed into the calculation.

2010-11-1619

Understanding calculation contexts

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.

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:

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).

Input contextReport part

YearSection header and block footers

Year, CustomerRows in the block

When specified explicitly in a formula, these input contexts are:

Sum ([Revenue] In ([Year]))

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.

3.1.2 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:

2010-11-1620

Understanding 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 calculates the minimum revenue by year.

If you add an additional column containing this formula to the block, the result is as follows:

You can see that the Min By Year column contains the minimum revenues that appear in the break
footers in the previous report.

2010-11-1621

Understanding calculation contexts

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 determines 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])

Explained in words, this formula calculates revenues by year by quarter, then outputs 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.

3.2 Default calculation contexts

A measure has a default calculation context depending on its place the report. The figures returned by
a measure depend on the dimensions with which it is associated. This combination of dimensions
represents the calculation context.

You can change the 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

8000:Total:

Report total: 8000

2010-11-1622

Understanding calculation contexts

The table below lists the calculation context of the measures in this report:

Related Topics

• Calculation contexts defined

• Modifying the default calculation context with extended syntax

ContextValueMeasure

Total of all revenue in the report20000Report total

Year8000Section header total

Year;Customer1000, 3000, 4000Customer total

Year8000Block footer total

3.2.1 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:

The dimensions and measures

Header

Body of the block

Footer

Example: Default contexts in a vertical table

The following table shows the default contexts in a vertical table:

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 isWhen the calculation is in the...

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

2010-11-1623

Understanding calculation contexts

3.2.2 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.

3.2.3 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 dimensions and measures

Header

Body of the block

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...The calculation is in the...

All the data is aggregated, then
the calculation function returns
a single value.

The same as the input context.

Footer

The dimensions and measures
used to generate the body of the
block.

All the data is aggregated, then
the calculation function returns
a single value.

2010-11-1624

Understanding calculation contexts

The output context is...The input context is...The calculation is in the...

The dimensions and measures

VBody footer

used to generate the current
column.

The dimensions and measures

HBody Footer

used to generate the current
row.

Same as footer.VFooter

Same as footer.HFooter

Example: Default contexts in a crosstab

The following report shows the default contexts in a crosstab:

All the data is aggregated, then
the calculation function returns
a single value.

All the data is aggregated, then
the calculation function returns
a single value.

All the data is aggregated, then
the calculation function returns
a single value.

All the data is aggregated, then
the calculation function returns
a single value.

3.2.4 Default contexts in a section

A section consists of a header, body and footer. The default contexts in a section are:

Body

Example: Default contexts in a section

The following report shows the default contexts in a crosstab:

The dimensions and measures
in the report, filtered to restrict
the data to the section data.

The output context is...The input context is...The calculation is in the...

All the data is aggregated, then
the calculation function returns
a single value.

2010-11-1625

Understanding calculation contexts

3.2.5 Default contexts in a break

A break consists of a header, body and footer. The default contexts in a break are:

The output context is...The input context is...The calculation is in the...

All the data is aggregated, then

Current instance of the break.Header

the calculation function returns
a single value.

All the data is aggregated, then

Current instance of the break.Footer

the calculation function returns
a single value.

2010-11-1626

Understanding calculation contexts

Example: Default contexts in a break

The following report shows the default contexts in a break:

3.3 Modifying the default calculation context with extended syntax

Extended syntax uses context operators that you add to a formula or measure to specify its calculation
context. A measure or formula context consists of its input context and output context.

3.3.1 Extended syntax operators

You specify input and output contexts explicitly with context operators. The following table lists the
context operators:

DescriptionOperator

In

Specifies an explicit list of dimensions to use in
the context.

Adds dimensions to the default contextForEach

Removes dimensions from the default contextForAll

2010-11-1627

Understanding calculation contexts

The ForAll and ForEach operators are useful when you have a default context with many dimensions.
It is often easier to add or subtract from the context using ForAll and ForEach than it is to specify the
list explicitly using In.

3.3.1.1 In context operator

The In context operator specifies dimensions explicitly in a context.

Example: Using In to specify the dimensions in a context

In this example you have a report showing Year and Sales Revenue. Your data provider also contains
the Quarter object but you do not include this dimension in the block. Instead, you want to include an
additional column to show the maximum revenue by quarter in each year. Your report looks like this:

Max Quarterly RevenueSales revenueYear

$2,660,699.50$8,096,123.602001

$4,186,120.00$13,232,246.002002

$4,006,717.50$15,059,142.802003

You can see where the values in the Max Quarterly Revenue column come from by examining this
block in conjunction with a block that includes the Quarter dimension:

2010-11-1628

Understanding calculation contexts

Sales revenueQuarterYear

$2,660,699.50Q12001

$2,279,003.00Q22001

$1,367,841.00Q32001

$1,788,580.00Q42001

$2,660,699.50Max:

Sales revenueQuarterYear

$3,326,172.00Q1

$2,840,651.00Q2

$2,879,303.00Q3

$4,186,120.00Q4

$4,186,120.00Max:

Sales revenueQuarterYear

$3,742,989.00Q1

$4,006,717.50Q2

$3,953,395.00Q3

$3,356,041.00Q4

$4,006,717.50Max:

The Max Quarterly Revenue column shows the highest quarterly revenue in each year. For example,
Q4 has the highest revenue in 2002, so the Max Quarterly Revenue shows Q4 revenue on the row
showing 2002.

Using the In operator, the formula for Max Quarterly Revenue is

Max ([Sales Revenue] In ([Year];[Quarter])) In ([Year])

This formula calculates the maximum sales revenue for each (Year,Quarter) combination, then outputs
this figure by year.

Note:

Because the default output context of the block is Year, you do not need to specify the output context
explicitly in this formula.

2010-11-1629

Understanding calculation contexts

3.3.1.2 ForEach context operator

The ForEach operator adds dimensions to a context.

Example: Using ForEach to add dimensions to a context

The following table shows the maximum revenue for each Quarter in a report which contains the
Quarter dimension but does not include it in the block:

Max Quarterly RevenueSales revenueYear

2660699.508096123.602001

4186120.0013232246.002002

4006717.5015059142.802003

It is possible to create a formula for the Max Quarterly Revenue column that does not include the
ForEach operator:

Max ([Sales Revenue] In ([Year];[Quarter])) In ([Year])

Using the ForEach context operator, you can achieve the same result with the following formula:

Max ([Sales Revenue] ForEach ([Quarter])) In ([Year])

Why? Because the Year dimension is the default input context in the block. By using the ForEach
operator, you add the Quarter dimension to the context, giving an input context of ([Year];[Quarter]).

3.3.1.3 ForAll context operator

The ForAll context operator removes dimensions from a context.

Example: Using ForAll to remove dimensions from a context

You have a report showing Year, Quarter and Sales Revenue and you want to add a column that
shows the total revenue in each year, as shown in the following block:

2010-11-1630

Understanding calculation contexts

To total revenues by year the input context needs to be (Year); by default it is (Year; Quarter). Therefore,
you can remove Quarter from the input context by specifying ForAll ([Quarter]) in the formula, which
looks like this:

Sum([Sales Revenue] ForAll ([Quarter]))

Note that you can use the In operator to achieve the same thing; in this case the formula is:

Sum([Sales Revenue] In ([Year]))

This version of the formula explicitly specifies Year as the context, rather than removing Quarter to
leave Year.

3.3.2 Extended syntax keywords

Extended syntax keywords are a form of shorthand that allows you to refer to dimensions in extended
syntax without specifying those dimensions explicitly. This helps future-proof reports; if formulas do not
contain hard-coded references to dimensions, they will continue to work even if dimensions are added
to or removed from a report.

There are five extended syntax keywords: Report, Section, Break, Block and Body.

3.3.2.1 The Report keyword

The following table describes the data referenced by the Report keyword depending on where it is
placed in a report:

2010-11-1631

Understanding calculation contexts

Example: The Report keyword

You have a report showing Year, Quarter and Sales revenue. The report has a column, Report Total,
that shows the total of all revenue in the report.

References this data...When placed in...

All data in the reportA block

All data in the reportA block break (header or footer)

All data in the reportA section (header, footer, or outside a block)

All data in the reportOutside any blocks or sections

The formula for the Report Total column is Sum([Sales revenue]) In Report. Without the Report
keyword, this column would duplicate the figures in the Sales Revenue column because it would use
the default output context ([Year];[Quarter]).

3.3.2.2 The Section keyword

The following table describes the data referenced by the Section keyword depending on where it is
placed in a report

References this data...When placed in...

All data in the sectionA block

All data in the sectionA block break (header or footer)

2010-11-1632

Understanding calculation contexts

Example: The Section keyword

You have a report showing Year, Quarter, and Sales revenue.

The report has a section based on Year. The Section Total column has the formula:

References this data...When placed in...

All data in the sectionA section (header, footer, or outside a block)

Not applicableOutside any blocks or sections

Sum ([Sales Revenue]) In Section

The figure in the Section Total column is the total revenue for 2001, because the section break occurs
on the Year object. Without the Section keyword this column would duplicate the figures in the Sales
revenue column, because it would use the default output context ([Year];[Quarter]).

3.3.2.3 The Break keyword

The following table describes the dimensions referenced by the Break keyword depending on where it
is placed in a report:

References this data...When placed in...

Data in the part of a block delimited by a breakA block

Data in the part of a block delimited by a breakA block break (header or footer)

Not applicableA section (header, footer, or outside a block)

Not applicableOutside any blocks or sections

Example: The Break keyword

You have a report showing Year, Quarter and Sales revenue:

2010-11-1633

Understanding calculation contexts

The report has break on Year. The Break Total column has the formula:

Sum ([Sales Revenue]) In Break

Without the Break keyword this column would duplicate the figures in the Sales revenue column,
because it would use the default output context ([Year];[Quarter]).

Break TotalSales revenueQuarterYear

$8,096,124$2,660,700Q12001

$8,096,124$2,279,003Q2

$8,096,124$1,367,841Q3

$8,096,124$1,788,580Q4

3.3.2.4 The Block keyword

The following table describes the dimensions referenced by the Block keyword depending on where it
is placed in a report: The Block keyword often encompasses the same data as the Section keyword.
The difference is that Block accounts for filters on a block whereas Section ignores them.

References this data...When placed in...

A block

A block break (header or footer)

Example: The Block keyword

You have a report showing Year, Quarter and Sales revenue. The report has a section based on Year.
The block is filtered to exclude the third and fourth quarters.

Data in the whole block, ignoring breaks, respect­ing filters

Data in the whole block, ignoring breaks, respect­ing filters

Not applicableA section (header, footer, or outside a block)

Not applicableOutside any blocks or sections

2010-11-1634

Understanding calculation contexts

The Yearly Average column has the formula

Average([Sales revenue] In Section)

and the First Half Average column has the formula

Average ([Sales revenue]) In Block

You can see how the Block keyword takes account of the filter on the block.

3.3.2.5 The Body keyword

The following table describes the dimensions referenced by the Body keyword depending on where it
is placed in a report:

References this data...When placed in...

Data in the blockA block

Data in the blockA block break (header or footer)

Data in the sectionA section (header, footer, or outside a block)

Data in the reportOutside any blocks or sections

2010-11-1635

Understanding calculation contexts

Example: The Body keyword

You have a report showing Year, Quarter and Sales revenue, with a break on Year. The report has
a section based on Year and a break on Quarter.

The Body column has the formula

Sum ([Sales Revenue]) In Body

The totals in the Body column are the same as those in the Sales revenue column because the Body
keyword refers to the data in the block. If you were to remove the Month object, the figures in the Block
column would change to correspond with the changed figures in the Sales revenue column. If you
were to place the formula in the report footer it would return the total revenue for the block.

BodySales revenueQuarterYear

2,660,699.52,660,700Q12001

2,279,0032,279,003Q2

1,367,840.71,367,841Q3

1,788,580.41,788,580Q4

8,096,123.62001

3.3.2.6 Using keywords to make reports generic

Extended syntax keywords future-proof your report against changes. If you refer to data explicitly (by
specifying dimensions using In, ForEach or ForAll) your reports might return unexpected data if
dimensions are added or removed. The following example illustrates this.

Example: Using the Report keyword to display percentages

In this example you have a block that contains Year, Quarter and Sales revenue objects. You want
to display revenues by year and quarter, and the percentage of the total revenue in the report that
each individual revenue represents, as shown:

2010-11-1636

Understanding calculation contexts

The formula for the Percentage of Total column is:

([Sales revenue]/(Sum([Sales revenue]) In Report)) * 100

In a block, the Report includes all data in a report, so this formula could be written:

([Sales revenue]/Sum([Sales revenue] ForAll ([Year];[Quarter]))) * 100

This formula removes Year and Quarter from the output context (in other words, calculates a grand
total) because there are no other dimensions in the report. The formula then divides each revenue by
the grand total to give its percentage of the total.

Although you can use ForAll in this situation, it is much better to use the Report keyword. Why? What
if the Month dimension were subsequently added to the report? The version of the formula that uses
the Report keyword still calculates each percentage correctly, but the version that explicitly specifies
the Year and Quarter dimensions is now wrong:

2010-11-1637

Understanding calculation contexts

Why is this? The problem lies in:

Sum ([Sales Revenue] ForAll ([Year];[Quarter))

When Year and Quarter were the only dimensions in the report, this was equivalent to “a grand total
of all revenues". Once you add the Month dimension, this expression removes Year and Quarter from
the default output context, but leaves Month.

The formula now has a “break" on month. In other words, on every row where Month is 1, this expression
now means “the total revenue of all month 1s". In every row where Month is 2, it means “the total
revenue of all month 2s". As a result, the percentages are not the percentages you expect.

2010-11-1638

Calculating values with smart measures

Calculating values with smart measures

4.1 Smart measures defined

“Smart measures” are measures whose values are calculated by the database (relational or OLAP) on
which a universe is based. They differ from classic measures, which are calculated from the detailed
values returned by the database. The data returned by smart measures is aggregated in ways not
supported natively by the Interactive Analysis component of SAP BusinessObjects BI.

Queries that contain smart measures calculate the measures in all the calculation contexts required in
a report. These contexts can change as the report changes. As a result, the query changes at each
data refresh after the required contexts have changed.

Smart measures behave differently from classic measures, which support a basic set of aggregation
functions (Max, Min, Count, Sum, Average) that can be calculated in all contexts without help from the
database. For example, if you build a query containing the [Country] and [Region] dimensions and the
[Revenue] measure (which calculates the sum of the revenue), the initial display shows Country, Region
and Revenue in a block. If you remove Region from the block, the total revenue for each country can
still be calculated without a data refresh by summing the revenues for all the regions in the country. A
smart measure requires a data refresh in this situation.

Calculation contexts are represented by “grouping sets” in the generated query.

4.2 Grouping sets and smart measures

A “grouping set” is a set of dimensions that generates a result for a measure. The generated SQL that
returns the data in a smart measure includes grouping sets for all the aggregations of that measure
that are included in the report.

Example: Grouping sets in a query

A query contains the [Country], [Region], [City] dimensions and the [Revenue] smart measure. These
objects imply the following grouping sets to calculate revenue in all possible contexts:

• Total smart measure value

• smart measure value by (Country, Region, City)

• smart measure value by (Country, City)

2010-11-1639

Calculating values with smart measures

• smart measure value by (City)

• smart measure value by (Region, City)

• smart measure value by (Region)

• smart measure value by (Country, Region)

• smart measure value by (Country)

If the database supports UNION, each grouping set is represented in a UNION clause in the generated
SQL.

The grouping sets are updated according to the calculation contexts required by the report, which can
change in response to changes in the report structure.

4.2.1 Management of grouping sets

When you first build and run a query including smart measures, the generated SQL includes the grouping
set necessary to calculate the smart measures at the most detailed level implied by the query objects.

For example, if you build a query containing the [Country], [Region] and [City] dimensions and the
[Revenue] smart measure, the (Country, Region, City) grouping set appears in the generated SQL. The
most detailed grouping set always appears in the SQL. Other grouping sets are added and removed
in response to changes in the report.

If you remove the [City] dimension from the block, the (Country, Region) grouping set is required to
return the revenue values. This grouping set is not yet available in the query SQL, so #TOREFRESH
appears in the [Revenue] cells. When you refresh the data, #TOREFRESH is replaced with the revenue
values.

If you then replace the [City] dimension in the block, the (Country, Region) grouping set is no longer
needed. It is removed from the query SQL and its values discarded the next time you refresh the data.

Each time you refresh the report data, grouping sets are included or discarded according to the calculation
contexts required by the report.

In certain situations, it is not possible to display the value of a smart measure. In this case,
#UNAVAILABLE appears in the measure cells.

4.3 Smart measures and the scope of analysis

When you build a query with a scope of analysis, the initial grouping set contains the result objects, but
not the scope objects. The query does not generate all the possible grouping sets from the combination
of the result objects and the scope objects.

2010-11-1640

Calculating values with smart measures

Example: A query with a scope of analysis and a smart measure

A query has the result objects [Country] and [Revenue]. The scope of analysis contains the [Region]
and [City] dimensions. When you run the query, its SQL contains the (Country) grouping set and it
displays [Country] and [Revenue] in a block.

4.4 Smart measures and SQL

4.4.1 Grouping sets and the UNION operator

Some databases support grouping sets explicitly with the GROUPING SETS operator. When you build
a query containing smart measures, the generated SQL uses multiple result sets and the UNION operator
to simulate the effect of GROUPING SETS.

Example: Grouping sets retrieved with the UNION operator

This example describes a query containing [Country], [Region], [City] dimensions and the [Revenue]
smart measure.

Note:

For simplicity, the smart measure calculates a sum. In practice, a smart measure is not needed for
this aggregation because sums are supported natively in Interactive Analysis.

When the query is first run, the grouping set is (Country, Region, City). The entire SQL query returns
this grouping set and there is no need for the UNION operator in the SQL.

If you remove the [City] dimension from the table, the (Country, Region) grouping set is required to
display the revenue (which appears initially as #TOREFRESH). After data refresh, the SQL is as
follows:

SELECT

SELECT
0 AS GID,
country.country_name,
region.region_name,
NULL,
sum(city.revenue)

FROM

country,
region,
city

WHERE

( country.country_id=region.country_id )
AND ( region.region_id=city.region_id )

GROUP BY

country.country_name,

region.region_name
UNION
SELECT

2010-11-1641

Calculating values with smart measures

1 AS GID,

country.country_name,

region.region_name,

city.city_name,

sum(city.revenue)
FROM

country,

region,

city
WHERE

( country.country_id=region.country_id )

AND ( region.region_id=city.region_id )
GROUP BY

country.country_name,

region.region_name,

city.city_name

Each grouping set is represented by a SELECT statement, and each has its own ID (the GID column).
Grouping sets that do not contain the full set of dimensions include empty columns (SELECT '')
because each SELECT statement in a query including UNION must have the same number of columns.

If you add a new block containing [Country] and [Revenue] to the report, the (Country) grouping set
is required. The generated SQL now includes three grouping sets as follows:

SELECT

0 AS GID,

country.country_name,

region.region_name,

NULL,

sum(city.revenue)
FROM

country,

region,

city
WHERE

( country.country_id=region.country_id )

AND ( region.region_id=city.region_id )
GROUP BY

country.country_name,

region.region_name
UNION
SELECT

1 AS GID,

country.country_name,

NULL,

NULL,

sum(city.revenue)
FROM

country,

city,

region
WHERE

( country.country_id=region.country_id )

AND ( region.region_id=city.region_id )
GROUP BY

country.country_name
UNION
SELECT

2 AS GID,

country.country_name,

region.region_name,

city.city_name,

sum(city.revenue)
FROM

country,

region,

city
WHERE

( country.country_id=region.country_id )

AND ( region.region_id=city.region_id )
GROUP BY

country.country_name,

region.region_name,

city.city_name

2010-11-1642

Calculating values with smart measures

4.5 Smart measures and formulas

4.5.1 Smart measures and dimensions containing formulas

If a formula or variable appears as a dimension in the calculation context of a smart measure, and the
formula determines the grouping set required by the measure, the values of the smart measure cannot
be displayed.

For example, a report contains a variable, Semester, with the formula

If [Quarter] = "Q1" or [Quarter] = "Q2" Then "H1" Else "H2"

Placed in a block, the Semester variable returns the following result:

4.5.2 Smart measures in formulas

Smart measures can return values when included in formulas, even when the formula requires a different
calculation context from the context implied by the position of the formula.

For example, a report contains a block as follows:

RevenueSemester

#UNAVAILABLEH1

#UNAVAILABLEH2

RevenueRegionCountry

10000NorthUS

15000SouthUS

14000EastUS

12000WestUS

2010-11-1643

Calculating values with smart measures

If you include an additional column in this table with the formula

[Revenue] ForAll ([Region])

the initial value of the column is #TOREFRESH because the formula, which excludes regions from the
calculation, requires the grouping set (Country). Refreshing the data adds the (Country) grouping set
to the query and displays the values of the measure.

Related Topics

• ForAll context operator

4.6 Smart measures and filters

4.6.1 Smart measures and filters on dimensions

If you apply a multi-valued filter to a dimension on which the value of a smart value depends, but the
dimension does not appear explicitly in the calculation context of the measure, the smart measure
cannot return a value and the cell displays #UNAVAILABLE.

#UNAVAILABLE appears because the measure must be filtered in the report and then aggregated, but
a smart measure cannot be aggregated after a report-level filter is applied. Calculating the measure
would be possible by adding a query filter to the generated SQL, but this solution carries the risk of
impacting other reports based on the same query.

Note:

A multi-valued filter filters on multiple values using operators such as Greater Than, In List or Less
Than. You can apply single-valued filters such as Equal To without generating the #UNAVAILABLE
error.

Example: A smart measure and a filter on a dimension

A query contains the Country and Resort dimensions and the Revenue smart measure. Country and
Revenue are displayed in a block. If you apply a report filter restricting the values of Resort to "French
Riviera" or "Bahamas Beach", #UNAVAILABLE appears in the Revenue cells.

2010-11-1644

Calculating values with smart measures

If you restrict Resort to "Bahamas Beach" only, the values are displayed.

RevenueCountry

#UNAVAILABLEFrance

#UNAVAILABLEUS

#UNAVAILABLESum:

RevenueCountry

971,444US

971,444Sum:

4.6.2 Filtering smart measures

If you apply a filter to a smart measure in a context where the smart measure is aggregated at different
levels of detail, the smart measure returns the #UNAVAILABLE error when it is aggregated at a less
detailed level.

Example: Filering a smart measure

You have a block displaying revenue by country and resort, where Revenue is a smart measure.

If you apply a filter, Revenue > 900,000, to the block, the total displays #UNAVAILABLE.

RevenueResortCountry

835,420French RivieraFrance

971,444Bahamas BeachUS

1,479,660Hawaiian ClubUS

3,286,524Sum:

2010-11-1645

Calculating values with smart measures

#UNAVAILABLE appears because the revenue in the block is aggregated by the grouping set (Country,
Resort), but the total revenue in the footer, which is also impacted by the filter, is aggregated at a less
detailed level than (Country, Resort).

4.6.3 Smart measures and drill filters

In general, it is not possible to return values for smart measures when a filter is applied to a dimension
that impacts the calculation of the measure. Dimensions filtered by drill filters are an exception to this
rule.

RevenueResortCountry

971,444Bahamas BeachUS

1,479,660Hawaiian ClubUS

#UNAVAILABLESum:

Example: A drill filter that affects a smart measure

A block contains the [Country] and [Revenue] objects. You drill on [Country] to display [Region],
[Revenue] in the block and move the filter on [Country] to the drill toolbar.

To do this, the (Country, Region) grouping set is added to the query. The result is then filtered to
display only those regions contained in the drilled country. It is not necessary to add a filter at the
query level to filter regions based on their country.

4.6.4 Smart measures and nested OR filters

Nested OR filters in which at least one of the filtered dimensions does not appear in a block generate
the #UNAVAILBLE error for a smart measure in the block.

Example:

• The filter [Country] = "US" OR [Country] = "France" returns #UNAVAILABLE for a

smart measure that uses the [Country] dimension if [Country] does not appear in the block.

• The filter [Country] = "US" OR [Year] = 2008 returns #UNAVAILABLE for a smart measure

that uses the [Country] or [Year] dimensions if either [Country] or [Year] does not appear in the
block.

2010-11-1646

Functions, operators and keywords

Functions, operators and keywords

5.1 Functions

Functions are divided into the following categories:

DescriptionCategory

Aggregate

5.1.1 Aggregate functions

Aggregates data (for example by summing or
averaging a set of values)

Manipulates character stringsCharacter

Returns date or time dataDate and Time

Returns data about a documentDocument

Returns data about a document’s data providerData Provider

Returns TRUE or FALSELogical

Returns numeric dataNumeric

Functions that do not fit into the above categoriesMisc

Returns sets of members from hierarchiesSet

5.1.1.1 Aggregate

2010-11-1647

Functions, operators and keywords

Description

Returns the default aggregation of a measure for a given member set

Function Group

Aggregate

Syntax

num Aggregate(measure[;member_set])

Input

RequiredTypeDescriptionParameter

YesMeasureAny measuremeasure

member_set

aggregation

Member setThe member set used to calculate the

No

Notes

• You can use extended syntax context operators with Aggregate.

• If you include member_set, Aggregate returns the aggregate value of the measure for all members

in the member set.

• member_set can include multiple sets separated by semicolons (;).

• The list of member sets must be enclosed in {}.

• All member sets must be from the current hierarchy. If another hierarchy is used, the #MULTIVALUE

error occurs.

• If a hierarchy only is specified in a member set, the current member of the hierarchy is used.

Examples

If the default aggregation of the [Sales Revenue] measure is Sum, and [California] is a member in the
[Geography] hierarchy (Country > State > City), Aggregate([Sales Revenue];{Descendants([Ge
ography]&[US].[California];1)}) returns the total sales revenue of all cities in California.

Related Topics

• Referring to members and member sets in hierarchies

5.1.1.2 Average

Description

Returns the average value of a measure

2010-11-1648

Functions, operators and keywords

Function Group

Aggregate

Syntax

num Average(measure[;member_set][;IncludeEmpty])

Input

RequiredTypeDescriptionParameter

YesMeasureAny measuremeasure

NoMember setA set of membersmember_set

IncludeEmpty

calculation

KeywordIncludes empty rows in the

No

(Empty rows excluded by default)

Notes

• You can use extended syntax context operators with Average.

• If you include member_set, Average returns the average value of the measure for all members in

the member set.

• member_set can include multiple sets separated by semicolons (;).

• The list of member sets must be enclosed in {}.

• All member sets must be from the current hierarchy. If another hierarchy is used, the #MULTIVALUE

error occurs.

• If a hierarchy only is specified in a member set, the current member of the hierarchy is used.

Examples

If the [Sales Revenue] measure has the values 41569, 30500, 40000 and 50138, Average([Sales
Revenue]) returns 40552.

If [California] is a member in the [Geography] hierarchy (Country > State > City), Average([Sales
Revenue];{[Geography]&[US].[California].children}) returns the average sales revenue

of all cities in California.

Related Topics

• Referring to members and member sets in hierarchies

• IncludeEmpty operator

5.1.1.3 Count

2010-11-1649

Functions, operators and keywords

Description

Returns the number of values in a set of values

Function Group

Aggregate

Syntax

integer Count(aggregated_data[;member_set][;IncludeEmpty][;Distinct|All])

Input

RequiredTypeDescriptionParameter

aggregat­ed_data

Any dimension, measure, hierarchy, level or mem­ber set

hierarchy, member set

YesDimension, measure,

NoMember setThe member set used to calculate the countmem-

ber_set

NoKeywordIncludes empty values in the calculationIncludeEmp-

ty

Distinct|All

NoKeywordIncludes distinct values only (default for dimensions)

or all values (default for measures) in the calculation

Notes

• You can use extended syntax context operators with Count.

• If you specify IncludeEmpty as the second argument, the function takes empty (null) values into

consideration in the calculation.

• If you do not specify the Distinct|All parameter, the default values are Distinct for dimensions

and All for measures.

• If you include member_set, Count restricts the count to the number of values in member_set.

• member_set can include multiple sets separated by semicolons (;).

• The list of member sets must be enclosed in {}.

• All member sets must be from the current hierarchy. If another hierarchy is used, the #MULTIVALUE

error occurs.

• If a hierarchy only is specified in a member set, the current member of the hierarchy is used.

Examples

Count("Test") returns 1

Count([City];Distinct) returns 5 if there are 5 different cities in a list of cities, even if there are

more than 5 rows in the list due to duplication.

Count([City];All) returns 10 if there are 10 cities in a list of cities, even though some are duplicated.

Count ([City];IncludeEmpty) returns 6 if there are 5 cities and one blank row in a list of cities.

Count([Product];{[Geography]&[State]}) returns the total number of products at the [State]

level in the [Geography] hierarchy.

2010-11-1650

Functions, operators and keywords

Related Topics

• IncludeEmpty operator

• Distinct/All operators

5.1.1.4 First

Description

Returns the first value in a data set

Function Group

Aggregate

Syntax

input_type First(dimension|measure)

Input

RequiredTypeDescriptionParameter

YesDimension or measureAny dimension or measuredimension|measure

Notes

• When placed in a break footer, First returns the first value in the in the break.

• When placed a a section footer, First returns the first value in the section.

Examples

When placed in a table footer, First([Revenue]) returns the first value of [Revenue] in the table.

5.1.1.5 Last

Description

Returns the last value in a dimension or measure

Function Group

Aggregate

Syntax

input_type Last(dimension|measure)

2010-11-1651

Functions, operators and keywords

Input

Notes

• When placed in a break footer, Last returns the last value in the in the break.

• When placed a a section footer, Last returns the last value in the section.

Examples

When placed in a table footer, First([Revenue]) returns the first value of [Revenue] in the table.

5.1.1.6 Max

RequiredTypeDescriptionParameter

YesDimension or measureAny dimension or measuredimension|measure

Description

Returns the largest value in a set of values

Function Group

Aggregate

Syntax

input_type Max(aggregated_data[;member_set])

Input

RequiredTypeDescriptionParameter

aggregat­ed_data

Any dimension, measure, hierarchy,
level or member set

level or member set

YesDimension, measure, hierarchy,

NoMember setA set of membersmember_set

Notes

• You can use extended syntax context operators with Max.

• If you include member_set, Max returns the maximum value of the aggregated data for all members

in the member set.

• member_set can include multiple sets separated by semicolons (;).

• The list of member sets must be enclosed in {}.

• All member sets must be from the current hierarchy. If another hierarchy is used, the #MULTIVALUE

error occurs.

• If a hierarchy only is specified in a member set, the current member of the hierarchy is used.

2010-11-1652

Functions, operators and keywords

Examples

If the [Sales Revenue] measure has the values 3000, 60034 and 901234, Max([Sales Revenue])
returns 901234.

If the [City] dimension has the values "Aberdeen" and "London", Max ([City]) returns "London".

If [US] is a member in the [Geography] hierarchy (Country > State > City), Max([Sales Rev
enue];{[Geography].[US].Children}) returns the highest sales revenue for a US state.

5.1.1.7 Median

Description

Returns the median (middle value) of a measure

Function Group

Aggregate

Syntax

num Median(measure)

Input

RequiredTypeDescriptionParameter

YesMeasureAny measuremeasure

Notes

If the set of numbers has an even number of values, Median takes the average of the middle two values.

Examples

Median([Revenue]) returns 971,444 if [Revenue] has the values 835420, 971444, and 1479660.

5.1.1.8 Min

Description

Returns the smallest value in a set of values

Function Group

Aggregate

2010-11-1653

Functions, operators and keywords

Syntax

any_type Min(aggregated_data[;member_set])

Input

RequiredTypeDescriptionParameter

aggregat­ed_data

Any dimension, measure, hierarchy,
level or member set

level or member set

YesDimension, measure, hierarchy,

NoMember setA set of membersmember_set

Notes

• You can use extended syntax context operators with Min.

• If you include member_set, Min returns the minimum value of the aggregated data for all members

in the member set.

• member_set can include multiple sets separated by semicolons (;).

• The list of member sets must be enclosed in {}.

• All member sets must be from the current hierarchy. If another hierarchy is used, the #MULTIVALUE

error occurs.

• If a hierarchy only is specified in a member set, the current member of the hierarchy is used.

Examples

If the [Sales revenue] measure has the values 3000, 60034 and 901234, Min([Sales Revenue])
returns 3000.

If the [City] dimension has the values Aberdeen and London, Min([City])returns "Aberdeen".

Min([Sales Revenue];{[Geography]&[US].children}) returns the lowest sales revenue for
a US state if [US] is a member in the [Geography] hierarchy with levels [Country] > [State] > [City].

5.1.1.9 Mode

Description

Returns the most frequently-occuring value in a data set

Function Group

Aggregate

Syntax

input_type Mode(dimension|measure)

2010-11-1654

Functions, operators and keywords

Input

Notes

• Mode returns null if the data set does not contain one value that occurs more frequently than all the

others.

Examples

Mode([Revenue]) returns 200 if [Revenue] has the values 100, 200, 300, 200.

Mode([Country]) returns the most frequently-occuring value of [Country].

5.1.1.10 Percentage

RequiredTypeDescriptionParameter

YesMeasureAny dimension or measuredimension|measure

Description

Expresses a measure value as a percentage of its embedding context

Function Group

Aggregate

Syntax

num Percentage(measure[;Break][;Row|Col])

Input

RequiredTypeDescriptionParameter

YesMeasureAny measuremeasure

NoKeywordAccounts for table breaksBreak

NoKeywordSets the calculation directionRow|Col

Examples

In the following table, the Percentage column has the formula Percentage([Sales Revenue])

PercentageSales RevenueYear

1010002001

5050002002

4040002003

2010-11-1655

Functions, operators and keywords

By default the embedding context is the measure total in the table. You can make the function take
account of a break in a table by using the optional Break argument. In this case the default embedding
context becomes the table section.

In the following table, the Percentage column has the formula Percentage([Sales Rev

enue];Break)

10010000Sum:

PercentageSales RevenueQuarterYear

101000Q12001

202000Q2

505000Q3

202000Q4

10010000Sum:2001

PercentageSales RevenueQuarterYear

202000Q12002

202000Q2

505000Q3

101000Q4

10010000Sum:2002

You can use the Percentage function across columns or rows; you can specify this explicitly using
the optional Row|Col argument. For example, in the following crosstab, the % column has the formula

Percentage([Sales Revenue];Row)

%Q4%Q3%Q2%Q1

2020005050002020001010002001

1010005050002020002020002002

2010-11-1656

Functions, operators and keywords

5.1.1.11 Percentile

Description

Returns the nth percentile of a measure

Function Group

Numeric

Syntax

num Percentile(measure;percentile)

Input

RequiredTypeDescriptionParameter

YesMeasureAny measuremeasure

YesNumberA percentage expressed as a decimalpercentile

Notes

The nth percentile is a number that is greater than or equal to n% of the numbers in a set. You express
n% in the form 0.n.

Examples

If [measure] has the set of numbers (10;20;30;40;50), Percentile([measure];0.3) returns 22,
which is greater than or equal to 30% of the numbers in the set.

5.1.1.12 Product

Description

Multiplies the values of a measure

Function Group

Aggregate

Syntax

num Product(measure)

2010-11-1657

Functions, operators and keywords

Input

Examples

Product([Measure]) returns 30 if [Measure] has the values 2, 3, 5.

5.1.1.13 RunningAverage

Description

Returns the running average of a measure

Function Group

Aggregate

RequiredTypeDescriptionParameter

YesMeasureAny measuremeasure

Syntax

num RunningAverage(measure[;Row|Col][;IncludeEmpty][;(reset_dims)])

Input

RequiredTypeDescriptionParameter

YesMeasureAny measuremeasure

NoKeywordSets the calculation directionRow|Col

NoKeywordIncludes empty values in the calculationIncludeEmpty

reset_dims

NoDimension listResets the calculation on the specified di-

mensions

Notes

• You can use extended syntax context operators with RunningAverage.

• You can set the calculation direction with the Row and Col operators.

• If you apply a sort on the measure referenced by RunningAverage, the running average is calculated

after the measure is sorted.

• You must always place dimensions in parentheses even if there is only one dimension in the list of

reset dimensions.

• When you specify a set of reset dimensions you must separate them with semi-colons.

• RunningAverage does not automatically reset the average after a block break or new section.

Examples

RunningAverage([Revenue]) returns these results in the following table:

2010-11-1658

Functions, operators and keywords

RunningAverage([Revenue];([Country])) returns these results in the following table:

Running AverageRevenueResortCountry

835,4201,479,660Hawaiian ClubUS

1,225,552971,444Bahamas BeachUS

1,095,508835,420French RivieraFrance

Running AverageRevenueResortCountry

835,4201,479,660Hawaiian ClubUS

1,225,552971,444Bahamas BeachUS

835,420835,420French RivieraFrance

Related Topics

• IncludeEmpty operator

• Row/Col operators

5.1.1.14 RunningCount

Description

Returns the running count of a number set

Function Group

Aggregate

Syntax

num RunningCount(dimension|measure[;Row|Col][;IncludeEmpty][;(reset_dims)])

2010-11-1659

Functions, operators and keywords

Input

RequiredTypeDescriptionParameter

YesDimension or

sure

Any dimension or measuredimension|mea-

measure

NoKeywordSets the calculation directionRow|Col

NoKeywordIncludes empty values in the calculationIncludeEmpty

reset_dims

NoDimension listResets the calculation on the specified di-

mensions

Notes

• You can use extended syntax context operators with RunningCount.

• You can set the calculation direction with the Row and Col operators.

• If you apply a sort on the measure referenced by RunningCount, the running count is calculated

after the measure is sorted.

• You must always place dimensions in parentheses even if there is only one dimension in the list of

reset dimensions.

• When you specify a set of reset dimensions you must separate them with semi-colons.

• RunningCount does not automatically reset the count after a block break or new section.

Examples

RunningCount([Revenue]) returns these results in the following table:

Running CountRevenueResortCountry

11,479,660Hawaiian ClubUS

2971,444Bahamas BeachUS

3835,420French RivieraFrance

RunningCount([Revenue];([Country])) returns these results in the following table:

Running CountRevenueResortCountry

11,479,660Hawaiian ClubUS

2971,444Bahamas BeachUS

1835,420French RivieraFrance

Related Topics

• IncludeEmpty operator

• Row/Col operators

• IncludeEmpty operator

• IncludeEmpty operator

2010-11-1660

Functions, operators and keywords

5.1.1.15 RunningMax

Description

Returns the running maximum of a dimension or measure

Function Group

Aggregate

Syntax

input_type RunningMax(dimension|measure[;Row|Col][;(reset_dims)])

Input

RequiredTypeDescriptionParameter

YesDimension or

sure

Any dimension or measuredimension|mea-

measure

NoKeywordSets the calculation directionRow|Col

reset_dims

NoDimension listResets the calculation on the specified di-

mensions

Notes

• You can use extended syntax context operators with RunningMax.

• You can set the calculation direction with the Row and Col operators.

• If you apply a sort on the measure referenced by RunningMax, the running maximum is calculated

after the measure is sorted .

• You must always place dimensions in parentheses even if there is only one dimension in the list of

reset dimensions.

• When you specify a set of reset dimensions you must separate them with semi-colons.

• RunningMax does not automatically reset the max after a block break or new section.

Examples

RunningMax([Revenue]) returns these results in the following table:

Running MaxRevenueResortCountry

835,420835,420French RivieraFrance

971,444971,444Bahamas BeachUS

1,479,6601,479,660Hawaiian ClubUS

2010-11-1661

Functions, operators and keywords

Related Topics

• IncludeEmpty operator

• Row/Col operators

5.1.1.16 RunningMin

Description

Returns the running minimum of a dimension or measure

Function Group

Aggregate

Syntax

input_type RunningMin(dimension|measure;[Row|Col];[(reset_dims)])

Input

RequiredTypeDescriptionParameter

YesDimension or

tail|measure

Any dimension or measuredimension|de-

measure

NoKeywordSets the calculation directionRow|Col

reset_dims

NoDimension listResets the calculation on the specified di-

mensions

Notes

• You can use extended syntax context operators with RunningMin.

• You can set the calculation direction with the Row and Col operators.

• If you apply a sort on the measure referenced by RunningMin, the running minimum is calculated

after the measure is sorted.

• You must always place dimensions in parentheses even if there is only one dimension in the list of

reset dimensions.

• When you specify a set of reset dimensions you must separate them with semi-colons.

• RunningMin does not automatically reset the minimum after a block break or new section.

Examples

RunningMin([Revenue]) returns these results in the following table:

Running MaxRevenueResortCountry

835,420835,420French RivieraFrance

2010-11-1662

Functions, operators and keywords

Related Topics

• IncludeEmpty operator

• Row/Col operators

5.1.1.17 RunningProduct

Description

Returns the running product of a measure

835,420971,444Bahamas BeachUS

835,4201,479,660Hawaiian ClubUS

Function Group

Aggregate

Syntax

num RunningProduct(measure[;Row|Col][;(reset_dims)])

Input

RequiredTypeDescriptionParameter

YesMeasureAny measuremeasure

NoKeywordSets the calculation directionRow|Col

reset_dims

NoDimension listResets the calculation on the specified di-

mensions

Notes

• You can use extended syntax context operators with RunningProduct.

• You can set the calculation direction with the Row and Col operators.

• If you apply a sort on the measure referenced by RunningProduct, the running product is calculated

after the measure is sorted.

• You must always place dimensions in parentheses even if there is only one dimension in the list of

reset dimensions.

• When you specify a set of reset dimensions you must separate them with semi-colons.

• RunningProduct does not automatically reset the product after a block break or new section.

Examples

RunningProduct([Number of guests]) returns these results in the following table:

2010-11-1663

Functions, operators and keywords

RunningProduct([Number of guests];([Country of origin])) returns these results in
the following table:

Running ProductNumber of guestsCityCountry of origin

66KobeJapan

244OsakaJapan

5,784241ChicagoUS

Running ProductNumber of guestsCityCountry of origin

66KobeJapan

244OsakaJapan

5784241ChicagoUS

Related Topics

• IncludeEmpty operator

• Row/Col operators

5.1.1.18 RunningSum

Description

Returns the running sum of a measure

Function Group

Aggregate

Syntax

num RunningSum(measure[;Row|Col][;(reset_dims)])

Input

RequiredTypeDescriptionParameter

YesMeasureAny measuremeasure

NoKeywordSets the calculation directionRow|Col

2010-11-1664

Functions, operators and keywords

RequiredTypeDescriptionParameter

reset_dims

NoDimension listResets the calculation on the specified di-

mensions

Notes

• You can use extended syntax context operators with the RunningSum.

• You can set the calculation direction with the Row and Col operators.

• If you apply a sort on the measure referenced by the RunningSum function, the running sum is

calculated after the measure is sorted.

• You must always place dimensions in parentheses even if there is only one dimension in the list of

reset dimensions.

• When you specify a set of reset dimensions you must separate them with semi-colons.

• RunningSum does not automatically reset the sum after a block break or new section.

Example

RunningSum([Revenue]) returns these results in the following table:

Running SumRevenueResortCountry

835,420835,420French RivieraFrance

1,806,864971,444Bahamas BeachUS

3,286,5241,479,660Hawaiian ClubUS

RunningSum([Revenue];([Country])) returns these results in the following table:

Running SumRevenueResortCountry

835,420835,420French RivieraFrance

971,444971,444Bahamas BeachUS

2,451,1041,479,660Hawaiian ClubUS

Related Topics

• IncludeEmpty operator

• Row/Col operators

5.1.1.19 ServerValue

2010-11-1665

Functions, operators and keywords

Description

Returns the database value of a measure

Function Group

Aggregate

Syntax

num ServerValue([measure])

Input

Notes

• ServerValue ignores all local filters applied to dimensions or hierarchies used to calculate the

measure

RequiredTypeDescriptionParameter

YesMeasureAny measuremeasure

Example

ServerValue([Internet Sales Amount] returns the database value of the measure [Internet
Sales Amount]

5.1.1.20 StdDev

Description

Returns the standard deviation of a measure

Function Group

Aggregate

Syntax

num StdDev(measure)

Input

RequiredTypeDescriptionParamter

YesMeasureAny measuremeasure

Notes

The standard deviation is a measure of the statistical dispersion in a set of numbers. It is calculated
by:

• finding the average of the set of numbers

2010-11-1666

Functions, operators and keywords

• subtracting the average from each number in the set and squaring the difference

• summing all these squared differences

• dividing this sum by (number of numbers in the set - 1)

• finding the square root of the result

Examples

If measure has the set of values (2, 4, 6, 8) StdDev([measure]) returns 2.58.

Related Topics

• Var

5.1.1.21 StdDevP

Description

Returns the population standard deviation of a measure

Function Group

Aggregate

Syntax

num StdDevP(measure)

Input

RequiredTypeDescriptionParamter

YesMeasureAny measuremeasure

Notes

The population standard deviation is a measure of the statistical dispersion in a set of numbers. It is
calculated by:

• finding the average of the set of numbers;

• subtracting the average from each number in the set and squaring the difference;

• summing all these squared differences;

• dividing this sum by (number of numbers in the set);

• finding the square root of the result.

You can use extended syntax context operators with StdDevP.

Examples

If measure has the set of values (2, 4, 6, 8) StdDevP([measure]) returns 2.24.

2010-11-1667

Functions, operators and keywords

5.1.1.22 Sum

Description

Returns the sum of a measure

Function Group

Aggregate

Syntax

num Sum(measure[;member_set])

Input

RequiredTypeDescriptionParameter

YesMeasureAny measuremeasure

NoMember setA set of membersmember_set

Notes

• You can use extended syntax context operators with Sum.

• If you include member_set, Sum returns the sum of the measure for all members in the member

set.

• member_set can include multiple sets separated by semicolons (;).

• The list of member sets must be enclosed in {}.

• All member sets must be from the current hierarchy. If another hierarchy is used, the #MULTIVALUE

error occurs.

• If a hierarchy only is specified in a member set, the current member of the hierarchy is used.

Examples

If the Sales Revenue measure has the values 2000, 3000, 4000, and 1000, Sum([Sales Revenue])
returns 10000.

If [California] is a member in the [Geography] hierarchy (Country > State > City), Sum([Sales Rev
enue];{Descendants([Geography]&[US].[California];1)}) returns the total sales revenue
of all cities in California.

5.1.1.23 Var

2010-11-1668

Functions, operators and keywords

Description

Returns the variance of a measure

Function Group

Aggregate

Syntax

num Var(measure)

Input

Notes

The variance is a measure of the statistical dispersion in a set of numbers. It is calculated by:

• finding the average of the set of numbers

• subtracting the average from each number in the set and squaring the difference

• summing all these squared differences

• dividing this sum by (number of numbers in the set - 1)

RequiredTypeDescriptionParamter

YesMeasureAny measuremeasure

The variance is the square of the standard deviation.

You can use extended syntax context operators with Var.

Examples

If measure has the set of values (2, 4, 6, 8) Var([measure]) returns 6.67.

Related Topics

• StdDev

5.1.1.24 VarP

Description

Returns the population variance of a measure

Function Group

Aggregate

Syntax

num VarP(measure)

2010-11-1669

Functions, operators and keywords

Input

Notes

The population variance is a measure of the statistical dispersion in a set of numbers. It is calculated
by:

• finding the average of the set of numbers

• subtracting the average from each number in the set and squaring the difference

• summing all these squared differences

• dividing this sum by (number of numbers in the set)

The population variance is the square of the population standard deviation.

You can use extended syntax context operators with VarP.

Examples

If measure has the set of values (2, 4, 6, 8) VarP([measure]) returns 5.

RequiredTypeDescriptionParamter

YesMeasureAny measuremeasure

Related Topics

• StdDevP

5.1.2 Character functions

5.1.2.1 Asc

Description

Returns the ASCII value of a character

Function Group

Character

Syntax

int Asc(string)

2010-11-1670

Functions, operators and keywords

Input

Notes

If string contains more than one character, the function returns the ASCII value of the first character
in the string.

Examples

Asc("A") returns 65.

Asc("ab") returns 97.

Asc([Country]) returns 85 when the value of [Country] is "US".

RequiredTypeDescriptionParameter

YesStringAny stringstring

5.1.2.2 Char

Description

Returns the character associated with an ASCII code

Function Group

Character

Syntax

string Char(ascii_code)

Input

Notes

If number is a decimal, the function ignores the decimal part.

Example

s

Char(123) returns "{".

RequiredTypeDescriptionParameter

YesNumberAn ASCII codeascii_code

2010-11-1671

Functions, operators and keywords

5.1.2.3 Concatenation

Description

Concatenates (joins) two character strings

Function Group

Character

Syntax

string Concatenation(first_string;second_string)

Input

RequiredTypeDescriptionParameter

YesStringThe first stringfirst_string

YesStringThe second stringsecond_string

Notes

You can also use the '+' operator to concatenate strings.

"First " + "Second" returns "First Second".

"First " + "Second" + " Third" returns "First Second Third".

You can use concatenation to include multiple dimensions in an aggregation function. For example,

Count([Sales Person]+[Quarter]+[Resort]) is equivalent to the syntax Count(<Sales
Person>,<Quarter>,<Resort>) that is allowed by Desktop Intelligence.

Examples

Concatenation("First ";"Second") returns "First Second".

Concatenation("First ";Concatenation("Second ";"Third")) returns "First Second

Third".

5.1.2.4 Fill

Description

Builds a string by repeating a string n times

2010-11-1672

Functions, operators and keywords

Function Group

Character

Syntax

string Fill(repeating_string;num_repeats)

Input

Examples

Fill ("New York ";2) returns "New York New York ".

RequiredTypeDescriptionParameter

YesStringThe repeating stringrepeating_string

YesNumberThe number of repeatsnum_repeats

5.1.2.5 FormatDate

Description

Formats a date according to a specified format

Function Group

Character

Syntax

string FormatDate(date;format_string)

Input

RequiredTypeDescriptionParameter

YesDateThe date to formatdate

YesStringThe format to applyformat_string

Notes

• The format of the output is dependent on the date format applied to the cell.

• The color formatting strings (for example: [Red], [Blue] and so on ) cannot be applied to FormatDate.

Examples

FormatDate(CurrentDate();"dd/MM/yyyy") returns "15/12/2005" if the current date is 15
December 2005.

2010-11-1673

Functions, operators and keywords

5.1.2.6 FormatNumber

Description

Formats a number according to a specified format

Function Group

Character

Syntax

string FormatNumber(number;format_string)

Input

RequiredTypeDescriptionParameter

YesNumberThe number to formatnumber

YesStringThe format to applyformat_string

Notes

• The format of the output is dependent on the number format applied to the cell.

• The color formatting strings (for example: [Red], [Blue] and so on ) cannot be applied to FormatNum

ber.

Examples

FormatNumber([Revenue];"#,##.00") returns 835,420.00 if [Revenue] is 835,420.

5.1.2.7 HTMLEncode

Description

Applies HTML encoding rules to a string

Function Group

Character

Syntax

string HTMLEncode(html)

2010-11-1674

Functions, operators and keywords

Input

Examples

HTMLEncode("http://www.businessobjects.com") returns
"http%3A%2F%2Fwww%2Ebusinessobjects%2Ecom ".

5.1.2.8 InitCap

Description

Capitalizes the first letter of a string

RequiredTypeDescriptionParamter

YesStringAn HTML stringhtml

Function Group

Character

Syntax

string InitCap(string)

Input

RequiredTypeDescriptionParameter

YesStringThe string to capitalizestring

Examples

InitCap("we hold these truths to be self-evident") returns "We hold these truths to
be self-evident".

5.1.2.9 Left

Description

Returns the leftmost characters of a string

Function Group

Character

Syntax

string Left(string;num_chars)

2010-11-1675

Functions, operators and keywords

Input

RequiredTypeDescriptionParameter

YesstringThe input stringstring

num_chars

left

Examples

Left([Country];2) returns "Fr" if [Country] is "France".

5.1.2.10 LeftPad

Description

Pads a string on its left with another string

Function Group

Character

Syntax

string LeftPad(padded_string;length;left_string)

Input

YesnumberThe number of characters to return from the

RequiredTypeDescriptionParameter

YesStringThe original stringpadded_string

YesNumberThe length of the output stringlength

left_string

YesStringThe string to be added to the left of

padded_string

Notes

• If length is less than the length of left_string and padded_string combined, left_string

is truncated.

• If length is less than or equal to the length of padded_string, the function returns

padded_string.

• If length is greater than the lengths of padded_string and left_string combined,

left_string is repeated or partially repeated enough times to fill out the length.

Examples

LeftPad("York";8;"New ") returns "New York"

LeftPad("York";6;"New ") returns "NeYork"

2010-11-1676

Functions, operators and keywords

LeftPad("York";11;"New ") returns "New NewYork"

LeftPad("New ";2;"York") returns "New".

5.1.2.11 LeftTrim

Description

Trims the leading spaces from a string

Function Group

Character

Syntax

string LeftTrim(trimmed_string)

Input

Examples

LeftTrim([Country]) returns "France" if [Country] is " France".

5.1.2.12 Length

Description

Returns the number of characters in a string

Function Group

Character

Syntax

int Length(string)

RequiredTypeDescriptionParameter

YesStringThe string to be trimmedtrimmed_string

2010-11-1677

Functions, operators and keywords

Input

Examples

Length([Last Name]) returns 5 if [Last Name] is "Smith".

5.1.2.13 Lower

Description

Converts a string to lower case

Function Group

Character

RequiredTypeDescriptionParameter

YesStringThe input stringstring

Syntax

string Lower(string)

Input

string

case

Examples

Lower("New York") returns "new york".

5.1.2.14 Match

Description

Determines whether a string matches a pattern

Function Group

Character

RequiredTypeDescriptionParameter

YesStringThe string to be converted to lower

Syntax

bool Match(test_string;pattern)

2010-11-1678

Functions, operators and keywords

Input

RequiredTypeDescriptionParamter

test_string

YesstringThe string to be tested against the text pat-

tern

YesstringThe text patternpattern

Notes

• The pattern can contain the wildcards "*" (replaces any set of characters) or "?" (replaces any single

character).

Examples

Match([Country];"F*") returns True if [Country} is "France".

Match([Country];"?S?") returns True if [Country] is "USA".

Match("New York";"P*") returns False.

5.1.2.15 Pos

Description

Returns the starting position of a text pattern in a string

Function Group

Character

Syntax

int Pos(test_string;pattern)

Input

RequiredTypeDescriptionParamter

test_string

YesstringThe string to be tested for the text pat-

tern

YesstringThe text patternpattern

Notes

• If the pattern occurs more than once, Pos returns the position of the first occurrence.

Examples

Pos("New York";"Ne") returns 1.

Pos("New York, New York";"Ne") returns 1.

2010-11-1679

Functions, operators and keywords

Pos("New York"; "York") returns 5.

5.1.2.16 Replace

Description

Replaces part of a string with another string

Function Group

Character

Syntax

string Replace(replace_in;replaced_string;replace_with)

Input

replace_with

placed_string

Examples

Replace("New YORK";"ORK";"ork") returns "New York".

5.1.2.17 Right

Description

Returns the rightmost characters of a string

Function Group

Character

Syntax

string Right(string;num_chars)

RequiredTypeDescriptionParameter

YesstringThe string in which the text is replacedreplace_in

YesstringThe text to be replacedreplaced_string

YesstringThe text that replaces re

2010-11-1680

Functions, operators and keywords

Input

RequiredTypeDescriptionParameter

YesstringAny stringstring

num_chars

right

Examples

Right([Country];2) returns "ce" if [Country] is "France".

5.1.2.18 RightPad

Description

Pads a string on its right with another string

Function Group

Character

Syntax

string RightPad(padded_string;length;right_string)

Input

YesnumberThe number of characters to return from the

RequiredTypeDescriptionParameter

YesStringThe original stringpadded_string

YesNumberThe length of the output stringlength

right_string

YesStringThe string to be added to the right of

padded_string

Notes

• If length is less than the length of right_string and padded_string combined, right_string

is truncated.

• If length is less than or equal to the length of padded_string, the function returns

padded_string.

• If length is greater than the lengths of padded_string and right_string combined,

right_string is repeated or partially repeated enough times to fill out the length.

Examples

RightPad("New ";8;"York") returns "New York"

RightPad("New ";6;"York") returns "New Yo"

2010-11-1681

Functions, operators and keywords

RightPad("New ";11;"York") returns "New YorkYor"

RightPad("New ";2;"York") returns "New".

5.1.2.19 RightTrim

Description

Trims the trailing spaces from a string

Function Group

Character

Syntax

string RightTrim(trimmed_string)

Input

Examples

RightTrim([Country]) returns "France" if [Country] is "France ".

5.1.2.20 Substr

Description

Returns part of a string

Function Group

Character

Syntax

string SubStr(string;start;length)

RequiredTypeDescriptionParameter

YesStringThe string to be trimmedtrimmed_string

2010-11-1682

Functions, operators and keywords

Input

Examples

SubStr("Great Britain";1;5) returns "Great".

SubStr("Great Britain";7;7) returns "Britain".

5.1.2.21 Trim

RequiredTypeDescriptionParameter

YesStringAny stringstring

YesNumberThe start position of the extracted stringstart

YesNumberThe length of the extracted stringlength

Description

Trims the leading and trailing spaces from a string

Function Group

Character

Syntax

string Trim(trimmed_string)

Input

Examples

Trim(" Great Britain ") returns "Great Britain".

5.1.2.22 Upper

Description

Converts a string to upper case

RequiredTypeDescriptionParameter

YesStringThe string to be trimmedstring

Function Group

Character

2010-11-1683

Functions, operators and keywords

Syntax

string Upper(string)

Input

Examples

Upper("New York") returns "NEW YORK".

5.1.2.23 UrlEncode

Description

Applies URL encoding rules to a string

RequiredTypeDescriptionParameter

YesStringThe string to be convertedstring

Function Group

Character

Syntax

string UrlEncode(html)

Input

Examples

UrlEncode("http://www.businessobjects.com") returns
"http%3A%2F%2Fwww%2Ebusinessobjects%2Ecom".

5.1.2.24 WordCap

Description

Capitalizes the first letter of all the words in a string

RequiredTypeDescriptionParameter

YesStringThe URL to be encodedhtml

Function Group

Character

2010-11-1684

Functions, operators and keywords

Syntax

string WordCap(string)

Input

Examples

WordCap("Sales revenue for March") returns "Sales Revenue For March".

5.1.3 Date and Time functions

RequiredTypeDescriptionParameter

YesStringThe string to be capitalizedstring

5.1.3.1 CurrentDate

Description

Returns the current date formatted according to the regional settings

Function Group

Date and Time

Syntax

date CurrentDate()

Examples

CurrentDate() returns 10 September 2002 if the date is 10 September 2002.

5.1.3.2 CurrentTime

Description

Returns the current time formatted according to the regional settings

Function Group

Date and Time

2010-11-1685

Functions, operators and keywords

Syntax

time CurrentTime()

Examples

CurrentTime returns 11:15 if the current time is 11:15.

5.1.3.3 DayName

Description

Returns the day name in a date

Function Group

Date and Time

Syntax

string DayName(date)

Input

RequiredTypeDescriptionParameter

YesDateThe input datedate

Examples

DayName([Reservation Date]) returns "Saturday' when the date in [Reservation Date] is 15
December 2001 (which is a Saturday).

Note

The input date must be a variable. You cannot specify the date directly, as in DayName("07/15/2001").

5.1.3.4 DayNumberOfMonth

Description

Returns the day number in a month

Function Group

Date and Time

Syntax

int DayNumberOfMonth(date)

2010-11-1686

Functions, operators and keywords

Input

Examples

DayNumberOfMonth([Reservation Date]) returns 15 when the date in [Reservation Date] is 15
December 2001.

5.1.3.5 DayNumberOfWeek

Description

Returns the day number in a week

RequiredTypeDescriptionParameter

YesDateThe input datedate

Function Group

Date and Time

Syntax

int DayNumberOfWeek(date)

Input

RequiredTypeDescriptionParameter

YesDateThe input datedate

Notes

The function treats Monday as the first day of the week.

Examples

DayNumberOfWeek([Reservation Date]) returns 1 when the date in [Reservation Date] is 2 May
2005 (which is a Monday).

5.1.3.6 DayNumberOfYear

Description

Returns the day number in a year

2010-11-1687

Functions, operators and keywords

Function Group

Date and Time

Syntax

int DayNumberOfYear(date)

Input

Examples

DayNumberOfYear([Reservation Date]) returns 349 when the date in [Reservation Date] is 15
December 2001.

RequiredTypeDescriptionParameter

YesDateThe input datedate

5.1.3.7 DaysBetween

Description

Returns the number of days between two dates

Function Group

Date and Time

Syntax

int DaysBetween(first_date;last_date)

Input

RequiredTypeDescriptionParameter

YesDateThe first datefirst_date

YesDateThe last datelast_date

Examples

DaysBetween([Sale Date];[Invoice Date]) returns 2 if [Sale Date] is 15 December 2001 and
[Invoice Date] is 17 December 2001.

5.1.3.8 LastDayOfMonth

2010-11-1688

Functions, operators and keywords

Description

Returns the date of the last day in a month

Function Group

Date and Time

Syntax

date LastDayOfMonth(date)

Input

Examples

LastDayOfMonth([Sale Date]) returns 31 December 2005 if [Sale Date] is 11 December 2005.

RequiredTypeDescriptionParameter

YesDateAny date in the monthdate

5.1.3.9 LastDayOfWeek

Description

Returns the date of the last day in a week

Function Group

Date and Time

Syntax

date LastDayOfWeek(date)

Input

RequiredTypeDescriptionParameter

YesDateAny date in the weekdate

Notes

The function treats Monday as the first day of the week.

Examples

LastDayOfWeek([Sale Date]) returns 15 May 2005 (a Sunday) if [Sale Date] is 11 May 2005.

2010-11-1689

Functions, operators and keywords

5.1.3.10 Month

Description

Returns the month name in a date

Function Group

Date and Time

Syntax

string Month(date)

Input

RequiredTypeDescriptionParameter

YesDateThe input datedate

Examples

Month([Reservation Date]) returns "December" when the date in [Reservation Date] is 15
December 2005.

5.1.3.11 MonthNumberOfYear

Description

Returns the month number in a date

Function Group

Date and Time

Syntax

int MonthNumberOfYear(date)

Input

RequiredTypeDescriptionParameter

YesDateAny date in the yeardate

Example

MonthNumberOfYear([Reservation Date]) returns 12 when the date in [Reservation Date] is
15 December 2005.

2010-11-1690

Functions, operators and keywords

5.1.3.12 MonthsBetween

Description

Returns the number of months between two dates

Function Group

Date and Time

Syntax

int MonthsBetween(first_date;last_date)

Input

RequiredTypeDescriptionParameter

YesDateThe first datefirst_date

YesDateThe last datelast_date

Examples

MonthsBetween([Sale Date];[Invoice Date]) returns 1 if [Sale Date] is 2 December 2005
and [Invoice Date] is 2 January 2006.

5.1.3.13 Quarter

Description

Returns the quarter number in a date

Function Group

Date and Time

Syntax

int Quarter(date)

2010-11-1691

Functions, operators and keywords

Input

Examples

Quarter([Reservation Date]) returns 4 when the date in [Reservation Date] is 15 December

2005.

5.1.3.14 RelativeDate

Description

Returns a date relative to another date

RequiredTypeDescriptionParameter

YesDateAny date in the quarterdate

Function Group

Date and Time

Syntax

date RelativeDate(start_date;num_days)

Input

RequiredTypeDescriptionParameter

YesDateThe start datestart_date

num_days

YesNumberThe number of days from the start

date

Notes

The num_days parameter can be negative to return a date earlier than start_date.

Examples

RelativeDate[Reservation Date];2) returns 17 December 2005 when [Reservation Date] is
15 December 2005.

RelativeDate[Reservation Date];-3) returns 9 January 2007 when [Reservation Date] is 12
January 2007.

5.1.3.15 ToDate

2010-11-1692

Functions, operators and keywords

Description

Returns a character string formatted according to a date format

Function Group

Date and Time

Syntax

date ToDate(date_string;format)

Input

Examples

ToDate("15/12/2002";"dd/MM/yyyy") returns 15/12/2002.

RequiredTypeDescriptionParameter

YesstringThe date to be formatteddate_string

YesstringThe date formatformat

5.1.3.16 Week

Description

Returns the week number in the year

Function Group

Date and Time

Syntax

int Week(date)

Input

RequiredTypeDescriptionParameter

YesDateThe input datedate

Examples

Week([Reservation Date]) returns 1 when the date in [Reservation Date] is 4 January 2004 (which
occurs in the first week of the year 2004).

2010-11-1693

Functions, operators and keywords

5.1.3.17 Year

Description

Returns the year in a date

Function Group

Date and Time

Syntax

int Year(date)

Input

RequiredTypeDescriptionParameter

YesDateThe input datedate

Examples

Year([Reservation Date]) returns 2005 when the date in [Reservation Date] is 15 December

2005.

5.1.4 Data Provider functions

5.1.4.1 Connection

Description

Returns the parameters of the database connection used by a data provider

Function Group

Data Provider

Syntax

string Connection(dp)

2010-11-1694

Functions, operators and keywords

Input

Notes

• You must enclose the name of the data provider in square brackets.

• For security reasons, the output of the function does not include the database host name, user name

and user password.

5.1.4.2 DataProvider

Description

Returns the name of the data provider containing a report object

RequiredTypeDescriptionParameter

YesData providerThe data providerdp

Function Group

Data Provider

Syntax

string DataProvider(obj)

Input

RequiredTypeDescriptionParameter

YesReport objectA report objectobj

Examples

DataProvider([Total Revenue]) returns "Sales" if the [Total Revenue] measure is in a data
provider called "Sales".

5.1.4.3 DataProviderKeyDate

Description

Returns the keydate of a data provider

Function Group

Data Provider

2010-11-1695

Functions, operators and keywords

Syntax

date DataProviderKeyDate(dp)

Input

Notes

• You must enclose the name of the data provider in square brackets.

• The returned keydate is formatted according to the document locale.

Examples

DataProviderKeyDate([Sales]) returns 3 August 2007 if the keydate for the Sales data provider
is 3 August 2007.

RequiredTypeDescriptionParameter

YesData providerThe data providerdp

5.1.4.4 DataProviderKeyDateCaption

Description

Returns the keydate caption of a data provider

Function Group

Data Provider

Syntax

string DataProviderKeyDateCaption(dp)

Input

RequiredTypeDescriptionParameter

YesData providerThe data providerdp

Notes

You must enclose the name of the data provider in square brackets.

Examples

DataProviderKeyDateCaption([Sales]) returns "Current calendar date" if the keydate caption
in the Sales data provider is "Current calendar date".

2010-11-1696

Functions, operators and keywords

5.1.4.5 DataProviderSQL

Description

Returns the SQL generated by a data provider

Function Group

Data Provider

Syntax

string DataProviderSQL(dp)

Input

RequiredTypeDescriptionParameter

YesData providerThe data providerdp

Notes

You must enclose the name of the data provider in square brackets.

Examples

DataProviderSQL([Query 1]) returns SELECT country.country_name FROM country if
the data provider SQL is SELECT country.country_name FROM country.

5.1.4.6 DataProviderType

Description

Returns the type of a data provider

Function Group

Data Provider

Syntax

string DataProviderType(dp)

2010-11-1697

Functions, operators and keywords

Input

Notes

• DataProviderType returns "Universe" for universe data providers or "Personal data" for personal

data providers.

• You must enclose the name of the data provider in square brackets.

Examples

DataProviderType([Sales]) returns "Universe" if the "Sales" data provider is based on a universe.

5.1.4.7 IsPromptAnswered

RequiredTypeDescriptionParameter

YesData providerThe data providerdp

Description

Determines whether a prompt has been answered

Function Group

Data Provider

Syntax

bool IsPromptAnswered([dp;]prompt_string)

Input

RequiredTypeDescriptionParameter

dp

NoData providerThe data provider containing the

prompt

YesStringThe prompt textprompt_string

Notes

You must enclose the name of the data provider in square brackets.

Examples

IsPromptAnswered("Choose a city") returns true if the prompt identified by the text "Choose
a city" has been answered.

IsPromptAnswered([Sales];"Choose a city") returns true if the prompt identified by the text
"Choose a city" in the [Sales] data provider has been answered.

2010-11-1698

Functions, operators and keywords

5.1.4.8 LastExecutionDate

Description

Returns the date on which a data provider was last refreshed

Function Group

Data Provider

Syntax

date LastExecutionDate(dp)

Input

RequiredTypeDescriptionParameter

YesData providerThe data providerdp

Notes

• If your report has one data provider only you can omit the dp parameter.

• You must enclose the name of the data provider in square brackets.

• You can use the DataProvider function to provide a reference to a data provider.

Examples

LastExecutionDate([Sales Query]) returns "3/4/2002" if the Sales Query data provider was
last refreshed on 4 March 2002.

Related Topics

• DataProvider

5.1.4.9 LastExecutionDuration

Description

Returns the time in seconds taken by the last refresh of a data provider

Function Group

Data Provider

Syntax

num LastExecutionDuration(dp)

2010-11-1699

Functions, operators and keywords

Input

Notes

You must enclose the name of the data provider in square brackets.

Examples

LastExecutionDuration([Sales]) returns 3 if the "Sales" data provider took 3 second to return
its data the last time it was run.

5.1.4.10 LastExecutionTime

RequiredTypeDescriptionParameter

YesData providerThe data providerdp

Description

Returns the time at which a data provider was last refreshed

Function Group

Data Provider

Syntax

time LastExecutionTime(dp)

Input

RequiredTypeDescriptionParameter

YesData providerThe data providerdp

Notes

• If your report has one data provider only you can omit the dp parameter.

• You can use the DataProvider function to provide a reference to a data provider.

• You must enclose the name of the data provider in square brackets.

Examples

LastExecutionTime([Sales Query]) returns "2:48:00 PM" if the Sales Query data provider was
last refreshed at 2:48:00 PM.

Related Topics

• DataProvider

2010-11-16100