Functions Function Output Syntax Sample Result

[Pages:23]Webi Report Function Overview

Below are the lists of available Webi 3.1 report functions and will describe each & every functions along with example. Webi 4.0 reports are also having some functions with some additional futures.

Functions

Abs Asc

Average

Block Name Ceil Char Column Number Concatenation Connection

Cos Count

Function Output

Returns the Absolute value of a Number ASCII value of a Character. Only one Character it will define the ASCII value, even if you add more than one Character. Average of the Measure values. Average function mostly useful when you do average on a total of table measures. Average would provide the average where total Measures divided by no of rows of the table

Returns the Table Name where this Block Name function is used. Rounds a Number up to the nearest Integer

Character Associated with ASCII code

Displays the column Number where this function used in a table

Join 2 strings and make it single

The parameters of the database connection used by a data provider. Data Provider name must enclosed in square bracket. Cosine of an angle

Number of Values / rows in a dimensions or Measures. Includes distinct values only (default for dimensions) or all values (default for measures) in the calculation

Syntax

Num Abs(number) Int Asc(Character)

Num Average(Measure[;In clude Empty])

String BlockName()

Num Ceil(Number)

String Char(ascii_code) Int ColumnNumber()

String Concatenation (first_string;Second_s tring) String Connection(dp)

Num Cos(angle) int Count(obj[;IncludeE mpty] [;Distinct|All])

Sample

=Abs(-97324) =Abs(54354) =Asc("M") =Asc("A")

=Average([Amount ])

=BlockName()

=Ceil(1234.51) =Ceil(1234.49) =Ceil(-1234.1) =Char(100) =Char(77) =Char(42)

C N =ColumnNumber() =Concatenation("S AP";" Business Objects") =Connection([Quer y 1])

=Cos(360) =Cos(200) =Count([Desc]) =Count([Amount])

Result

97324 54354 77 65

Desc

Amount

A

2540

B

3210

C

1243

D

5214

E

8003

F

98721

Sum

118931

Average 19821.83

Master Table ? Name

of the table

1235 1235 -1234

d M *

3 ? Column Number function used in 3rd column of that table

SAP Business Objects

DB Layer: "Oracle OCI". DB Type: "Oracle 11".

-0.28 0.49

Desc A B C C E F

Amount 2540 3210 1243 5214 8003

98721

Current Date

Current Time

Current User Data Provider

Data Provider key Date Data Provider key Date Caption

Data Provider SQL Data provider Type

Day Name

Day Number Of Month

Day Number Of Week

Day Number Of Year

Display the Current Date formatted according to the regional Settings in your server Display the Current Time formatted according to the regional Settings in your server Logon of the Current User Name of the Data provider containing a report Object

Key Date of the Data Provider. Data Provider name must enclosed in square bracket. Key Date Caption of the Data Provider. Data Provider name must enclosed in square bracket.

SQL Query generated by the Data Provider

Type of a Data Provider ?what kind of sources used for that report ? Example - Universe, Free hand SQL, etc., Day Name in the Date. The input date must be a variable. You cannot specify the date directly, as in DayName("12/24/2013"). Return the Day number of that month

Return the Day number of that week. Example 6/16/2010 is Wednesday and Webi report would consider Monday as Start of the Week. So Day Number for that week is 3. Return the Day number of that Year

Count Dim

5

Count

Measure

6

Date CurrentDate() =CurrentDate()

12/24/13

Time CurrentTime() =CurrentTime()

2:05:54 PM

String CurrentUser() String DataProvider(obj)

Date DataProviderKeyDate (dp) Stri8ng DataProviderKeyDate Caption(dp)

String DataProviderSQL([dp ]) String DataProviderType([d p])

=CurrentUser() =DataProvider([Co untry])

=DataProviderKeyD ate([dp])

=DataProviderKeyD ateCaption([dp])

=DataProviderSQL([ dp1])

=DataProviderType ([dp1])

devuser Query 1 ? this is Default Data provider name 24 December 2013

Returns "Current calendar date" if the keydate caption in the dp data provider is "Current calendar date". Select object1 from table...

Universe

String DayName(date)

= =DayName([Sales Date])

Int

=DayNumberOfMo

DayNumberOfMonth nth([Sales Date])

(date)

Int

=

DayNumberOfWeek() DayNumberOfWee

k([Sales Date])

Int

=DayNumberOfYea

DayNumberOfYear(d r([Sales Date])

ate)

Sales Date 6/16/2010 6/21/2010 6/23/2010 6/25/2010 6/26/2010

Day Name Wednesday

Monday Wednesday

Friday Saturday

Sales Date 6/16/2010 6/21/2010 6/23/2010 6/25/2010 6/26/2010

Day Number 16 21 23 25 26

Sales Date Week

Number

6/16/2010

3

6/21/2010

1

6/23/2010

3

6/25/2010

5

6/26/2010

6

Sales Date 4/5/2010 4/7/2010 4/8/2010

4/12/2010 4/13/2010

Day of Year 95 97 98

102 103

Days Between

Document Author Document Creation Date Document Creation Time Document Date Document Name Document Owner

Document Partially Refreshed

Document Time Drill Filter

Euro Convert From

Number of Days between 2 dates

Logon of the Document Creator

Date on which a document was created

Time on which a document was created

Date on which a document was last saved Name of the document / report

Logon user name of the owner of the document (the last person who saved the document) Will tell you whether the report is fully or partially refreshed.

Time on which a document was last saved Drill Filters applied to a report or an Object in drill mode

Converts a Euro amount to another currency. The currency code must be the code of one of the 12 EU currencies whose values were fixed in relation to the Euro prior to their abolition in January 2002. If it is not, the function returns #ERROR. The currencies are:

Code Currency Desc BEF Belgian franc

Int DaysBetween(first_d ate;last_date)

String DocumentAuthor() Date DocumnetCreationDa te() Time DocumnetCreationTi me() Date DocumnetDate() String DocumnetName() String DocumentOwner()

Bool DocumentPartiallyRe freshed()

Time DocumnetTime() String DrillFilters([Obj |Separator])

num EuroConvertFrom(eu ro_amount;curr_cod e;round_level)

=DaysBetween([Sal es Date];CurrentDate( ))

=DocumentAuthor( ) = DocumnetCreation Date() = DocumnetCreation Time() = DocumnetDate() = DocumnetName() =DocumentOwner( )

=DocumentPartiall yRefreshed()

= DocumnetTime() =DrillFilters()

=EuroConvertFrom (1000;"FRF";2) =EuroConvertFrom (1000;"GRD";1) =EuroConvertFrom (1000;"ITL";0)

;

3/12/2010 3/16/2010

Days Between

1383 1379

3/17/2010 3/22/2010 3/23/2010

1378 1373 1372

*Current Date ?

12/24/13

devuser

12/10/13

2:55:59 PM

12/10/13 Sample Webi Report devuser

Returns 1 & 0. 0 ? Means Fully refreshed. 1 ? Means partially refreshed. You can use these values in IF function to display TRUE / FALSE 4:25:39 PM

Consider report has drill on Country and Year. If the DrillFilter return US ? means Drill Filter applied on Country. US 2013 ? Means Drill filter Applied on County & year. 6559.57

340,750.1

1,936,270

DEM German mark GRD Greek drachma ESP Spanish peseta FRF French franc IEP Irish punt ITL Italian lira LUF Luxembourg franc NLG Dutch guilder ATS Austrian schilling PTS Portuguese escudo FIM Finnish mark

Euro Convert To Euro From Round Error

Euro To Round Error Even Exp Fact

Fill

Converts an amount to Euros. Currency code can be used as mentioned in the above table

Returns the Rounding decimal place amount in the conversion. Example, EuroConvertFrom(1000;"FRF";1 ) return 6559.60 whereas without rounding this would be 6559.57, the difference is 0.03. The difference amount 0.03 will be returned when you use EuroFromRoundError(1000;"FR F";1). Returns the Rounding decimal place amount in the conversion. Same as above.

Determines the Number is Even or not

Exponential (e raised to a power). An exponential is the constant e (2.718...) raised to a power. Factorial of an Integer. Factorial of number is multiple of all the integers from 1 to number. If FACT(4) then it would calculate 1*2*3*4= 24 an FACT(6) is 1*2*3*4*5*6=720 Concatenate the string by repeating N times.

num EuroConvertTo(none uro_amount;curr_co de;round_level) num EuroFromRoundError (euro_amount;curr_c ode;round_level)

num EuroToRoundError(n oneuro_amount;curr _code;round_level) Bool Even(number)

Num Exp(Number)

Num Fact(number)

String Fill(repeating_string; num_repeats)

=EuroConvertTo(65 59.57;"FRF";2) =EuroConvertTo(65 59.57;"DEM";2) =EuroFromRoundEr ror(1000;"FRF";1) =EuroFromRoundEr ror(1000;"GRD";1)

=EuroFromRoundEr ror(1000;"NLG";1)

=EuroToRoundErro r(1000;"FRF";1)

=Even(10) =Even(11) =Exp(2) =Exp(3)

=Fact(4) =Fact(6)

=Fill("Business Objects ";3)

1000

3,353.85

0.03

0 ? means there is no difference in rounding the decimal places.

-0.01 ?> 2,203.71 when you round this amount 2,203.7 and the difference is -0.01 -0.05 ->152.45 while rounding it would be 152.4 and the difference is -0.05 1 ? This is even Number 0 ? Not an even Number 7.39 20.09

24 720

Business Objects Business Objects Business Objects

=Fill("SAP ";5)

SAP SAP SAP SAP SAP

First

Returns the first value in a data Input_type

=First([Country]) USA ? First value of

Floor Force Merge

set (dimension or Measure Objects). Used in a break footer, First returns the first value in the in the break. Used in a section footer, First returns the first value in the section. Used in a table footer, First([Sales]) returns the first value of [Sales] in the table. Round the Number down to the Nearest integer ForceMerge is the equivalent of the BO Desktop Intelligence Multicube function. Includes synchronized dimensions in measure calculations when the dimensions are not in the measure's Calculation context / Data Provider.

First(dimension|mea sure)

Int Floor(number) Num ForceMerge(number)

=First([Sales])

=Floor(1234.61) =Floor(-1234.61) =ForceMerge([Sale s])

Country Object. 102364.55 ? first value of the Sales Measure based on your Query data set.

1234 -1235 104342.50

When you have more than one data provider and one DP does not have a synchronized dimension and you need to merge that DP measure with other DP, ForceMerge would help you to get the Measure amount in single Object.

Format Date

Format Number

Get Content Locale

Get Dominant Preferred Viewing

ForceMerge([Sales]) returns the value of [Revenue], taking into account any synchronized Dimensions that do not appear in the same block as the [Sales] measure. Formats a date according to the Specified format

string FormatDate(date;for mat_string)

Formats a Number according to string

the Specified format

FormatNumber(num

ber;format_string)

Returns the Local (Desktop ? where you are accessing the report) Language of your report Data content Returns the dominant locale in the user's Preferred Viewing Locale group. The Translation

String GetContentLocale()

String GetDominantPreferr edViewingLocale()

=FormatDate(Curre ntDate();"yyyy/dd/ MM") =FormatDate(Curre ntDate();"MM/yyyy ") =FormatNumber([S ales];"#,##0") =FormatNumber([S ales];"#,##0.00") =GetContentLocale ()

2013/24/12

12/2013

12,358,210 12,358,210.56 en

=GetDominantPref en_US erredViewingLocal e()

Locale()

Manager Guide lists all the Dominant Preferred Viewing Locales. Please refer from SAP Portal for more information (page 51). bject/product_guides/boexir4/ en/xi4_translation_manageme nt_tool_en.pdf

Get Locale Get Localized

Get Preferred Viewing Locale HTML Encode If Then Else

User's local system used format for their Webi Interface (example, menu items and button text) This function mostly used in the translation (language Translator) purpose and would return the Users preferred Viewing Locale. ? The string parameter can be a string in any formula (for example, in a cell, an alerter message or a variable definition). ? When designing a report, you can use the comment parameter to provide further information to help Translators translate the string. The comment appears with the string in the Translation Manager tool which translators use to translate reports. User's preferred locale for viewing document data (the Preferred Viewing Locale) Applies HTML encoding rules to a string

Returns a value based on whether an expression is true or false

String GetLocale()

string GetLocalized(string[;c omment])

string GetPreferredViewing Locale() string HTMLEncode(html) If bool_value Then true_value [Else false_value]

=GetLocale()

=GetLocalized("Sal es Revenue";"Max 20 characters")

=GetPreferredView ingLocale() =HTMLEncode("htt p://" ) =If(10 >5) Then "TRUE" Else "FALSE"

en Sales Revenue

en TRUE

Init Cap Interpolation

Capitalizes the First letter of the String Calculates empty measure values by interpolation.

?Interpolation is particularly

String InitCap(string)

=If(10 >11) Then "TRUE" Else If(5 >7) Then "TRUE" Else "FALSE" =InitCap("report")

FALSE Report

num Interpolation(measur e[;PointToPoint|Line ar]

=Intepolation([Mea surement])

Country

M In ea te su rp re ol

Is Date Is Error Is Logical

useful when you create a line graph on a measure that contains missing values. By using the function you ensure that the graph plots a continuous line rather than disconnected lines and points. ? Linear regression with least squares interpolation calculates missing values by calculating a line equation in the form f(x) = ax + b that passes as closely as possible through all the available values of the measure. ? Point-to point interpolation calculates missing values by calculating a line equation in the form f(x) = ax + b that passes through the two adjacent values of the missing value. ? The sort order of the measure impacts the values returned by Interpolation. ? You cannot apply a sort or a ranking to a formula containing Interpolation. ? If there is only one value in the list of values, Interpolation uses this value to supply all the missing values. ? Filters applied to an interpolated measure can change the values returned by Interpolation depending on which values the filter impacts. Validate / Check whether a value is a Date

[;NotOnBreak|(reset _dims)][;Row|Col])

"Measure" ? Any measures

"PointTo-Point |Linear" - The interpolation method: Keyword ? PointToPoint point-to-point interpolation ? Linear - linear regression with least squares Interpolation.

By Default "PointToPoint" would be taken.

"NotOnBreak| reset_dims" ? ? NotOnBreak prevents the function from resetting the calculation on block and section breaks ? reset_dims - the list of dimensions used to reset the interpolation

"Row|Col" - Sets the calculation direction

By Default "Row" would be taken.

Bool IsDate(obj)

=IsDate("12/24/20 13")

m at

en io

t n(

[

M

ea

su

re

m

e

nt

])

USA

12 12

CANADA

13

JAPAN 14 14

INDIA

15 15

CHINA

16

UK

17

GERMANY 18 18

0 ?FALSE (Value is not an Date)

Validate / Check whether a returns an error

Bool IsError(obj)

=IsDate(CurrentDat e()) =IsError(100/0)

1- TRUE (Value is a Date) 1 ? 100/0 would return ERROR in the report

Determines whether a value is Bool IsLogical(obj)

=IsError(100/10) =IsLogical(IsString([

0 ? 100/10 would returns 10 and there is no error 1 - TRUE

Is Null Is NUmber Is Prompt Answered

Is String

Is Time Last

Last Day Of Month Last Day Of Week Last Execution Date Last Execution Duration Last Execution Time

Boolean. ? IsLogical returns a boolean value that you can use in the If function. ? If you place IsLogical directly into a column, it returns an integer (1=true; 0=false). You can format this integer using a Boolean number format. Determines whether a value is null

Determines whether a value is a number

Returns whether a prompt has been answered. Must enclose the name of the data provider in square brackets.

Determines whether a value is String

Determines whether a value is a Time Returns the Last value in a data set (dimension or Measure Objects). Used in a break footer, Last returns the Last value in the in the break. Used in a section footer, Last returns the Last value in the section. Used in a table footer, Last([Sales]) returns the Last value of [Sales] in the table. Date of the last day in a month

Date of the last day in a Week. The function treats Monday as the first day of the week. The Date on which a Data Provider was last refreshed

Time in seconds taken by the last refresh of a data provider

Returns a time when a data provider was last refreshed

Bool IsNull(obj)

Bool IsNUmber(obj)

Bool IsPromptAnswered([ dp;]prompt_string)

Bool IsString(obj)

Bool IsTime(obj) input_type Last(dimension|meas ure)

Date LastDayOfMonth(dat e) Date LastDayOfWeek(date ) date LastExecutionDate([d p]) num LastExecutionDuratio n(dp) time LastExecutionTime(d

Country]))

=If (IsNULL([Sales])) then 0 else [Sales] =If (IsNumber([Sales])) then 0 else [Sales] =IsPromptAnswere d("Enter Country Name")

=If (IsString([Country Code])) then "TRUE" else "FALSE" =IsTime(CurrentTi me()) =Last([Country]) =Last([Sales])

=LastDayOfMonth( CurrentDate()) =LastDayOfWeek(C urrentDate()) = LastExecutionDate( [Sales]) =LastExecutionDur ation([Sales]) =LastExecutionTim e([Sales])

Return the value based on the Sales object data Return the value based on the Sales object data 1 ? TRUE - if the prompt identified by the text "Enter Country Name" has been answered. Return the value based on the Sales object data

1 ? Return TRUE

YEMAN ? Last value of Country Object. 2364.55 ? Last value of the Sales Measure based on your Query data set.

12/31/13

12/29/13

12/24/13

40 Seconds

4:29:54 PM

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download