Using functions, formulas and calculations in Web Intelligence

1 SAP BusinessObjects Business Intelligence SuiteDocument Version: Support Package 2 2016-03-07 Using functions, formulas and calculations in Web IntelligenceContent1 Document History: Web Intelligence Functions, formulas and About this Using standard and custom Standard Using formulas to build custom variables to simplify Working with functions in of and formula Understanding calculation The input The output Default calculation contexts in a vertical contexts in a horizontal contexts in a crosstab contexts in a contexts in a Modifying the default calculation context with extended syntax Calculating values with smart Grouping sets and smart of grouping Smart measures and the scope of Smart measures and sets and the UNION Smart measures and measures and dimensions containing measures in Smart measures and concerning

2 Smart measures and measures and filters on 2016 SAP SE or an SAP affiliate company. All rights functions, formulas and calculations in Web IntelligenceContentFiltering smart measures and drill measures and nested OR Functions, operators and and Time Provider Function and formula syntax Extended syntax Block Body Break Report Section Rounding and truncating Referring to members and member sets in Troubleshooting Automatic rewrite formula Formula error and information # # # # # # # functions, formulas and calculations in Web IntelligenceContent 2016 SAP SE or an SAP affiliate company. All rights # # # # # # # # # # # # Comparing values Using Comparing values Using the Previous Comparing values Using the RelativeValue dimensions and the RelativeValue dimensions and of slicing dimensions and RelativeValue in 2016 SAP SE or an SAP affiliate company.

3 All rights functions, formulas and calculations in Web IntelligenceContent1 Document History: Web Intelligence Functions, formulas and CalculationsThe following table provides an overview of the most important document BusinessObjects Web Intelligence Support Package 2 March 2016 The following section has been added to the guide: Description of Comment function Comment [page 204]SAP BusinessObjects Web Intelligence 2015 The following sections have been added to the guide: Description of Automatic Formula Rewrite mechanism Automatic rewrite formula mechanism [page 251] SAP HANA Online mode formulas , functions and operators restrictions Function and formula operators [page 14] Strings to create custom formats for weeks Custom formats [page 37] Time zone can be displayed on a date/time value Custom formats [page 37] New ToDecimal function added ToDecimal [page 179] Updated Concatenation function behavior Concatenation [page 82] Using functions, formulas and calculations in Web IntelligenceDocument History: Web Intelligence Functions, formulas and calculations 2016 SAP SE or an SAP affiliate company.

4 All rights About this guideThe Using Functions, formulas and calculations in Web Intelligence guide provides detailed information on the advanced calculation capabilities that you can use when you perform data analysis. This guide also provides a syntax reference to the available functions and 2016 SAP SE or an SAP affiliate company. All rights functions, formulas and calculations in Web IntelligenceAbout this guide3 Using standard and custom calculationsYou can use standard calculation functions to make quick calculations on standard calculations are not sufficient for your needs, you can use the formula language to build custom Standard calculationsYou can use standard calculation functions to make quick calculations on following standard calculations are available:Table 1.

5 CalculationDescriptionSumCalculates the sum of the selected all rows for a measure object or count distinct rows for a dimension or detail the average of the the minimum value of the selected the maximum value of the selected the selected data as a percentage of the total. The results of the percentage are dis played in an additional column or row of the 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 the default aggregation function to a standard measure, or the database aggregation function to a smart you apply standard calculations to table columns , the calculation results appear in footers.

6 One footer is added for each functions, formulas and calculations in Web IntelligenceUsing standard and custom calculations 2016 SAP SE or an SAP affiliate company. All rights Using formulas to build custom calculationsCustom calculations allow you to add additional calculations to your report beyond its base objects and standard add a custom calculation by writing a formula. A formula can consist of base report variables, functions, operators and calculation 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 average revenue per saleIf you have a report with Sales Revenue and Number Sold objects and you want to add revenue per sale to the report.

7 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 Using variables to simplify formulasIf a formula is complex you can use variables to simplify 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 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 can type this variable name into a formula or drag the variable to the Formula toolbar as you would for any report Working with functionsA custom calculation sometimes contains only report objects, for example [Sales Revenue]/[Number of Sales].

8 calculations can also include functions in addition to report 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 2016 SAP SE or an SAP affiliate company. All rights functions, formulas and calculations in Web IntelligenceUsing standard and custom calculationsRelated InformationFunction and formula operators [page 14]Functions [page 36] Including functions in cellsThe text in report cells always begins with =.

9 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 Function syntaxThe Formula Editor displays the function syntax when you select the use a function you need to know its name, how many input values it requires and the data types of these input values.

10 You also need to know the type of data that the function 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)This syntax tells you that the Abs function takes a single number as input and returns a number as Examples of functionsThis topic offers examples of functions used in prompt input with the UserResponse functionYou 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 Using functions, formulas and calculations in Web IntelligenceUsing standard and custom calculations 2016 SAP SE or an SAP affiliate company.