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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.