Enhancing Reports using Formulas and Variables

Objective

After completing this lesson, you will be able to Implement formulas and variables.

Formula Editor

The Formula Editor has been improved to simplify formula edition with a more powerful code editor:

  • The Objects, Functions and Operators panels have been moved to the right. This leaves more space to display the list in these panels and the Formula field.
  • Syntax analysis is applied on the fly: the strings in the formulas are colored depending on their type: functions, objects, strings, comments… Unrecognized parts are underlined in red.
  • Formulas are displayed using a mono-space font to improve readability.
  • When you move the cursor behind a parenthesis, the pairing parenthesis is highlighted.
  • Line numbers are displayed in the Formula Text field.
  • When hovering the mouse over an object or a function in the panels, a tooltip displays the object properties or the function help.
  • To create more readable and maintainable formulas, you can use the classic comments usually supported by most programming languages in the formula language. To comment the end of a line, you may use // and to comment a block in the formula, you may use /* */

Shortcuts are supported in the Formula text field:

Ooperating SystemShortcuts
Windows

[Ctrl]+[Z], [Ctrl]+[Y] to undo and redo.

[Ctrl]+[X], [Ctrl]+[C], [Ctrl]+[V] to cut, copy and paste content.

IOS

[Cmd]+[Z], [Cmd]+[Shift]+[Z]: to undo and redo.

[Cmd]+[X], [Cmd]+[C], [Cmd]+[V]: to cut, copy and paste content.

Edit Variables

When you select a variable in the Main Side Panel → Objects tab, you can check its properties (name, qualification, description, and formula) in the Build Side Panel → Object Properties tab and modify them on the fly without opening the Formula Editor. Once you have changed a property, click the Apply button to validate them.

If the text field is too small to display the formula, you can open the Formula Editor by right clicking the variable and selecting the Edit Variable command, or by clicking the Display the Formula Editor button near the formula in the Build Side Panel → Object Properties tab.

Watch this video to learn how to create variables:

Formula Language

Time Aware Functions

New functions have been added to the Web Intelligence formula language to take advantage of the time dimension:

FunctionDescriptionSyntax
OpeningPeriod

Returns the measure at the first date of the period in the current context and in the time range defined in the time dimension.

OpeningPeriod(measure;timeperiod)
ClosingPeriod

Returns the measure at the last date of the period in the current context and in the time range defined in the time dimension.

ClosingPeriod(measure;timeperiod)
ParallelPeriod

Returns the measure at the date of a period parallel to the dates present in the block in the current context. Selected dates are shifted a number of intervals either forward in time or back in time.

ParallelPeriod(measure;timeperiod;offset)
PeriodToDate

Returns the running total of the values of the measure over the time period to date, in the current context. For example, user might select Year to see the year-to-date (YTD) values for each month next to the monthly values.

PeriodToDate(measure;timeperiod;[Sum|Max|Min|Count|Average|Product])

Custom Properties

Using the OpenDocument URL, you can set some custom properties in the document. New functions are available in the formula language to retrieve these custom properties and use them in your formulas:

FunctionsDescriptionSyntax
CustomProperties

Returns the list of defined custom properties.

CustomProperties()
CustomPropertyValue

Returns the value of a defined custom property. If this custom property does not exist, the function returns an empty string.

CustomPropertyValue(custom_property)

Data Provider Properties

New auto-documentation functions have been added to provide additional details on the document’s data sources:

FunctionsDescriptionSyntax
DataSourceName

Returns the name of the data source.

DataSourceName(dp|obj)
DataSourceDescription

Returns the description of a data provider’s data source.

DataSourceDescription(dp|obj)
DataSourcePath

Returns the full path folder of a data source.

DataSourcePath(dp|obj)
DataSourceParentFolder

Returns the name of the parent folder containing the data provider’s data source.

DataSourceParentFolder(dp|obj)
DataSourceLocationType

Returns the location type of the data source.

DataSourceLocationType(dp|obj)
QueryName

Returns the name of the query of the data provider.

QueryName(dp|obj)

Note

dp = a data provider ; obj = a report object

UserResponse

New parameters have been added to the UserResponse function to modify the default strings used to separate prompts’ answers.

FunctionsDescriptionSyntax
UserResponse

Returns the response to a prompt.

UserResponse([dp;] prompt_string[;Index] [;multi_separator])
UserResponse

Returns the response to a prompt.

UserResponse([dp;] prompt_string[;Index] [;multi_separator|DefaultSeparator; between_separator])

In which:

  • Multi_separator is a string used to separate the different answers of a prompt’s answer or of a prompt’s complex answer (if the query is based on SAP BW). By default, it is ";".
  • Between_separator is a string used to separate the two values of a prompt’s range. By default, this separator is "-". To change it without changing the multiple values separator, you can pass the keyword DefaultSeparator for the multi_separator parameter.

Input Controls and Element Linking Selection

To retrieve values selected in input control or element linking, two new functions have been introduced:

FunctionsDescriptionSyntax
InputControlFilter

Returns the values selected by the user through input control.

InputControlFilter(string InputControlName [;string separator][;string report])
ElementLinkingFilters

Returns the values selected on the Block Name block for element linking.

ElementLinkingFilters(string Block Name[;string separator[;string report]])

Auto-Documentation Functions

For auto-documentation purposes, two functions have been added: one to return the description of an object or a variable and another one to return the formula of a variable.

FunctionsDescriptionSyntax
DescriptionOf

Returns the description of an object.

DescriptionOf(obj)
FormulaOf

Returns the formula defining a variable if the object is a variable. If the object is not a variable, then it returns an empty string.

FormulaOf(obj)

Note

For more information, you can refer to: Using Functions Formulas and Calculations in Web Intelligence | SAP Help Portal

Log in to track your progress & complete quizzes