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 Function Output Syntax 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 Num Abs(number) Returns the Table Name where this Block Name function is used. Rounds a Number up to the nearest Integer 97324 54354 77 65 Num Average(Measure[;In clude Empty]) =Average([Amount ]) String BlockName() =BlockName() 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 Num Ceil(Number) =Ceil(1234.51) =Ceil(1234.49) =Ceil(-1234.1) =Char(100) =Char(77) =Char(42) Int Asc(Character) Character Associated with ASCII String code Char(ascii_code) Column Number Displays the column Number where this function used in a table Concatenation Join 2 strings and make it single Connection The parameters of the database connection used by a data provider. Data Provider name must enclosed in square bracket. Cosine of an angle Count 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 Result =Abs(-97324) =Abs(54354) =Asc(“M”) =Asc(“A”) Char Cos Sample Int ColumnNumber() C N =ColumnNumber() String Concatenation =Concatenation("S (first_string;Second_s AP";" Business tring) Objects") String =Connection([Quer Connection(dp) y 1]) Num Cos(angle) int Count(obj[;IncludeE mpty] [;Distinct|All]) =Cos(360) =Cos(200) =Count([Desc]) =Count([Amount]) 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 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 Date CurrentDate() =CurrentDate() Count Dim Count Measure 12/24/13 Time CurrentTime() =CurrentTime() 2:05:54 PM String CurrentUser() String DataProvider(obj) =CurrentUser() =DataProvider([Co untry]) 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. Date DataProviderKeyDate (dp) Stri8ng DataProviderKeyDate Caption(dp) =DataProviderKeyD ate([dp]) devuser Query 1 – this is Default Data provider name 24 December 2013 Data Provider SQL SQL Query generated by the Data Provider Data provider Type 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 String DataProviderSQL([dp ]) String DataProviderType([d p]) Current Date Current Time Current User Data Provider Data Provider key Date Data Provider key Date Caption Day Name Day Number Of Month Day Number Of Week Day Number Of Year 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 5 6 =DataProviderKeyD ateCaption([dp]) Returns "Current calendar date" if the keydate caption in the dp data provider is "Current calendar date". =DataProviderSQL([ Select object1 from dp1]) table… =DataProviderType ([dp1]) Universe String DayName(date) = =DayName([Sales Date]) Sales Date 6/16/2010 6/21/2010 6/23/2010 6/25/2010 6/26/2010 Day Name Wednesday Monday Wednesday Friday Saturday Int DayNumberOfMonth (date) =DayNumberOfMo nth([Sales Date]) Sales Date 6/16/2010 6/21/2010 6/23/2010 6/25/2010 6/26/2010 Day Number 16 21 23 25 26 Int DayNumberOfWeek() = DayNumberOfWee k([Sales Date]) Sales Date Week Number 6/16/2010 6/21/2010 6/23/2010 6/25/2010 6/26/2010 3 1 3 5 6 Int DayNumberOfYear(d ate) =DayNumberOfYea r([Sales Date]) 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 Number of Days between 2 dates Document Author Document Creation Date Logon of the Document Creator Document Creation Time Time on which a document was created Document Date Document Name Document Owner Date on which a document was last saved Name of the document / report Document Partially Refreshed Date on which a document was created 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. Int DaysBetween(first_d ate;last_date) =DaysBetween([Sal es Date];CurrentDate( )) ; 3/12/2010 3/16/2010 3/17/2010 3/22/2010 3/23/2010 Days Between 1383 1379 1378 1373 1372 *Current Date – 12/24/13 devuser String DocumentAuthor() Date DocumnetCreationDa te() Time DocumnetCreationTi me() Date DocumnetDate() String DocumnetName() String DocumentOwner() =DocumentAuthor( ) = DocumnetCreation Date() = DocumnetCreation Time() = DocumnetDate() =DocumentOwner( ) devuser Bool DocumentPartiallyRe freshed() =DocumentPartiall yRefreshed() 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 12/10/13 2:55:59 PM 12/10/13 = DocumnetName() Sample Webi Report Document Time Drill Filter Time on which a document was last saved Drill Filters applied to a report or an Object in drill mode Time DocumnetTime() String DrillFilters([Obj |Separator]) = DocumnetTime() Euro Convert From 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 num EuroConvertFrom(eu ro_amount;curr_cod e;round_level) =EuroConvertFrom (1000;"FRF";2) =DrillFilters() 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 =EuroConvertFrom (1000;"GRD";1) 340,750.1 =EuroConvertFrom (1000;"ITL";0) 1,936,270 DEM GRD ESP FRF IEP ITL LUF NLG ATS PTS FIM Euro Convert To Euro From Round Error Euro To Round Error Even Exp Fact Fill First German mark Greek drachma Spanish peseta French franc Irish punt Italian lira Luxembourg franc Dutch guilder Austrian schilling Portuguese escudo Finnish mark Converts an amount to Euros. Currency code can be used as mentioned in the above table num EuroConvertTo(none uro_amount;curr_co de;round_level) 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. num EuroFromRoundError (euro_amount;curr_c ode;round_level) 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 Exp(Number) num EuroToRoundError(n oneuro_amount;curr _code;round_level) Determines the Number is Even Bool Even(number) or not Returns the first value in a data =EuroConvertTo(65 59.57;"FRF";2) 1000 =EuroConvertTo(65 3,353.85 59.57;"DEM";2) =EuroFromRoundEr 0.03 ror(1000;"FRF";1) =EuroFromRoundEr 0 – means there is no ror(1000;"GRD";1) difference in rounding the decimal places. =EuroFromRoundEr -0.01 –> 2,203.71 ror(1000;"NLG";1) when you round this amount 2,203.7 and the difference is -0.01 =EuroToRoundErro -0.05 ->152.45 while r(1000;"FRF";1) rounding it would be 152.4 and the difference is -0.05 =Even(10) 1 – This is even Number =Even(11) 0 – Not an even Number =Exp(2) 7.39 =Exp(3) 20.09 Num Fact(number) =Fact(4) =Fact(6) 24 720 String Fill(repeating_string; num_repeats) =Fill("Business Objects ";3) Business Objects Business Objects Business Objects =Fill("SAP ";5) =First([Country]) SAP SAP SAP SAP SAP USA – First value of Input_type 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) =First([Sales]) Country Object. 102364.55 – first value of the Sales Measure based on your Query data set. =Floor(1234.61) =Floor(-1234.61) =ForceMerge([Sale s]) 1234 -1235 104342.50 2013/24/12 String GetContentLocale() =FormatDate(Curre ntDate();"yyyy/dd/ MM") =FormatDate(Curre ntDate();"MM/yyyy ") =FormatNumber([S ales];"#,##0") =FormatNumber([S ales];"#,##0.00") =GetContentLocale () String GetDominantPreferr edViewingLocale() =GetDominantPref erredViewingLocal e() en_US Num ForceMerge(number) 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 Formats a Number according to the Specified format 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 FormatDate(date;for mat_string) string FormatNumber(num ber;format_string) 12/2013 12,358,210 12,358,210.56 en Locale() Manager Guide lists all the Dominant Preferred Viewing Locales. Please refer from SAP Portal for more information (page 51). http://help.sap.com/businesso bject/product_guides/boexir4/ en/xi4_translation_manageme nt_tool_en.pdf Get Locale User’s local system used format for their Webi Interface (example, menu items and button text) Get Localized 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. Get Preferred User's preferred locale for Viewing Locale viewing document data (the Preferred Viewing Locale) HTML Encode Applies HTML encoding rules to a string String GetLocale() =GetLocale() en string GetLocalized(string[;c omment]) =GetLocalized("Sal es Revenue";"Max 20 characters") Sales Revenue string GetPreferredViewing Locale() string HTMLEncode(html) =GetPreferredView ingLocale() en http://www.sap.com If Then Else If bool_value Then true_value [Else false_value] =HTMLEncode("htt p://www.sap.com" ) =If(10 >5) Then "TRUE" Else "FALSE" FALSE String InitCap(string) =If(10 >11) Then "TRUE" Else If(5 >7) Then "TRUE" Else "FALSE" =InitCap("report") Init Cap Interpolation Returns a value based on whether an expression is true or false Capitalizes the First letter of the String Calculates empty measure values by interpolation. •Interpolation is particularly num Interpolation(measur e[;PointToPoint|Line ar] =Intepolation([Mea surement]) TRUE Report Country M ea su re In te rp 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 Validate / Check whether a returns an error Determines whether a value is [;NotOnBreak|(reset _dims)][;Row|Col]) m en t “Measure” – Any measures “PointTo-Point |Linear” - The interpolation method: Keyword • PointToPoint point-to-point interpolation • Linear - linear regression with least squares Interpolation. USA CANADA JAPAN INDIA CHINA UK GERMANY By Default “PointToPoint” would be taken. 12 14 15 18 at io n( [ M ea su re m e nt ]) 12 13 14 15 16 17 18 “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) Bool IsError(obj) Bool IsLogical(obj) =IsDate("12/24/20 13") 0 –FALSE (Value is not an Date) =IsDate(CurrentDat e()) =IsError(100/0) 1- TRUE (Value is a Date) 1 – 100/0 would return ERROR in the report =IsError(100/10) 0 – 100/10 would returns 10 and there is no error 1 - TRUE =IsLogical(IsString([ Is Null 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 Country])) Bool IsNull(obj) =If (IsNULL([Sales])) then 0 else [Sales] =If (IsNumber([Sales])) then 0 else [Sales] =IsPromptAnswere d("Enter Country Name") Is NUmber Determines whether a value is a number Bool IsNUmber(obj) Is Prompt Answered Returns whether a prompt has been answered. Must enclose the name of the data provider in square brackets. Bool IsPromptAnswered([ dp;]prompt_string) Is String Determines whether a value is String Bool IsString(obj) Is Time 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 Bool IsTime(obj) Last Last Day Of Month Last Day Of Week Last Execution Date 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 Last Execution Duration Time in seconds taken by the last refresh of a data provider Last Execution Time Returns a time when a data provider was last refreshed input_type Last(dimension|meas ure) Date LastDayOfMonth(dat e) Date LastDayOfWeek(date ) date LastExecutionDate([d p]) num LastExecutionDuratio n(dp) time LastExecutionTime(d =If (IsString([Country Code])) then “TRUE” else “FALSE” =IsTime(CurrentTi me()) =Last([Country]) =Last([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. =LastDayOfMonth( CurrentDate()) 12/31/13 =LastDayOfWeek(C urrentDate()) 12/29/13 = LastExecutionDate( [Sales]) =LastExecutionDur ation([Sales]) 12/24/13 =LastExecutionTim e([Sales]) 4:29:54 PM 40 Seconds Left Left Pad Left Trim The leftmost characters of a string. The number of characters to return from the left. Pads a string on its left with another string. •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 Trims the leading spaces from a String p) string Left(string;num_char s) string LeftPad(padded_strin g;length;left_string) =Left("SAP Business Objects";5) SAP B =Left([Country];3) =LeftPad("South";1 0;"West ") AUS for Austria West South =LeftPad("South";1 1;"West ") West WSouth =LeftPad("South";4 ;"West ") South – Left string length is less than Padded String and Left String is truncated and it will display only the Padded String string =LeftTrim(" SAP LeftTrim(trimmed_str BO") ing) =LeftTrim([Country ]) =Length("Web Intelligence Report") =LineNumber() SAP BO USA – if Country Object Has values as “ USA” 23 Length Number of characters in a string Int Length(string) Line Number Line Number in a Block Int LineNumber() Ln Log Natural logarithm of a number Logarithm of a number in a specified base =Ln(20) =Log(100;5) Log10 Base 10 logarithm of a number Num Ln(number) num Log(number;base) Num Log10(number) 2 – Which row the function is used. If it used in a table in will increase the numbers based on the No. of rows 3 2.86 =Log10(10) 1 Lower Match string Lower(string) Bool Match(test_string;pa ttern) =Lower(“SAP”) =Match("SAP Business Objects";"*B*") sap 1 – There is a matching pattern for “B” Max Converts a string to lower case Check whether a string matches a pattern. The pattern can contain the wildcards "*" (replaces any set of characters) or "?" (Replaces any single character). Largest value in a set of values =Max([Country]) USA Median Return Middle Value of a Input_type Max(Dimension | Measure) Num = Median([Sales]) 312,345 - If [Sales] has Min Measure. If the set of numbers has an even number of values, Median takes the average of the middle two values. Smallest value in a set of values Median(Measure) Input_type Min(Dimension | Measure) Num Mod(dividend;divisor ) Input_type Mode(dimension|me asure) Mod Remainder Number from the division of two numbers Mode Most frequently-occurring value in a data set. Mode returns null if the data set does not contain one value that occurs more frequently than all the others. Returns the Month name of the String Month(date) Date Returns the Month Number of Int the Date. MonthNumberOfYea r(date) Return the Number of months Int between two dates MonthsBetween(first _date;last_date) Month Month Number Of Year Months Between Name Of No Filter Number Of Data Providers Number Of Pages Number Of Rows Odd Page Return the Name of an Object and this function used to display the Object Name in Table Headers. Ignore the Filters applied in the table / block while doing the calculation. • NoFilter(obj;Drill) does not work in query drill mode because the drill filters are added to the query rather than applied to the report data. • If you end drill mode with drill filters applied, the drill filters become report filters and can change the value of any objects to which NoFilter(obj;Drill) is applied Return the Number of Data Providers available in a report Return the Number Pages of the report Return the Number Rows in a Data Provider Check whether the given Number is Odd or not Returns the Current Page the values of 54202, 312345, and 4233490. =Min([Country]) AUSTRIA =Mod(102;23) 10 =Mod(10;3) =Mode([Sales]) 1 20 – If [Sales] has values of 10, 5, 20,15,20 USA – If [Country] has most frequent values of USA December =Mode([Country]) =Month(CurrentDa te()) =MonthNumberOf Year(CurrentDate() )) =MonthsBetween([ Sales Date]; CurrentDate()) 12 5 – If [Sales Date] is 24-Jun-2013 and Current Date is 24Dec-2013 Country String NameOf(obj) =NameOf([Country ]) Input_type NoFilter(obj[;All|Drill ]) = NoFilter(Sum([Sale s])) Returns the total sales revenue of all possible rows in the block, even when rows are filtered out of the block. Int NumberOfDataProvid ers() Int NumberOfPages() = NumberOfDataPro viders() =NumberOfPages() 3 – The report has 3 Data Providers. Int NumberOfRows([dp]) Bool Odd(Number) =NumberOfRows([ Query 1]) =Odd(121) =Odd(140) =Page() Int Page() 20 – Report has 20 pages 200 – Data Provider Query 1 has 200 Rows 1 – TRUE 0 - FALSE 1 – First Page of the Percentage Percentile Number of the Report Expresses a measure value as a percentage of its embedding context. 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. You can use the Percentage function across columns or rows; you can specify this explicitly using the optional Row|Col argument. The nth percentile of a measure. 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. Pos The starting position of a text pattern in a string Power Number raised to a Power Previous Previous value of an object. • The default value of offset is 1. Previous([Revenue];1) and Previous([Revenue]) are functionally the same. • When you include the NoNull argument, the function returns the first non-null value of the object beginning from the cell offset rows before the current row and counting backwards. • You can use extended syntax context operators with Previous. • The Self operator allows you to refer to the previous value of a cell when it contains content other than one report object. • 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 report Num Percentage(measure[ ;Break][;Row|Col]) =Percentage([Sales ]) Num Percentile(measure;p ercentile) - percentile - A percentage expressed as a decimal = If [Amount] has the Percentile([Amount set of numbers ];0.3) (100;200;300;400;500) Percentile([Amount];0 .3) returns 220, This is greater than or equal to 30% of the numbers in the set. =Pos("SAP BO" 1 ;"SA") Int Pos(test_string;patte rn) Num Power(number; power) Input_type Previous(dimension| measure|Self[;(reset _dims)][;offset][;NoN ull]) =Power(2;2) Year Sales 2001 2002 2003 Sum 1000 5000 4000 10000 Perc enta ge 10 50 40 100 4 = Previous([Country];1) Country USA UK UAE Sales 1000 5000 4000 Previous USA UK = Previous([Sales]) Country USA UK UAE Sales 1000 5000 4000 Previous 1000 5000 = Previous([Sales];[Country]) Country USA UK UAE Region South West East South North North East Sales 2000 4000 1000 5000 4000 3000 5000 Previous 2000 4000 5000 3000 Product separate them with semicolons. • Previous is applied after all report, section and block filters, and all sorts, are applied. • You cannot apply sorts or filters on formulas that use Previous. • If Previous is applied on a measure and the measure returns an undefined value, Previous returns an undefined value even if the previous line returned a value. • Previous ignores breaks when placed outside a break header or footer. • Previous returns the value in the previous instance of the footer when placed in a break footer. • Previous is reset in each report section. • When used in a crosstab, Previous does not treat the last value in a row as the previous value of the first value of the next row. Multiplies the values of a measure. =Previous ([Sales]) – in a crosstab report. Country 2012 USA UK UAE 2000 5000 3000 Previ ous 2013 3000 6000 5000 Previ ous 2000 5000 3000 =Previous([Sales]);2;NoNull) Year 2012 2012 2012 2012 2013 2013 2013 Quarter Q1 Q2 Q3 Q4 Q1 Q2 Q3 Sales 2000 Previous 1000 5000 2000 2000 1000 5000 5000 5000 Note: Above highlighted 2 means, previous function should start after 2nd row of the table. Num Product(measure) =Product([Amount] ) 180 – If [Amount] has the values of 2,3,5,6 (2*3*5*6) Returns the prompt text and user response of all prompts in a document. String PromptSummary() = PromptSummary() *** Query Name:Query 1 *** Quarter Quarter Number in a Date Int Quarter(date) =Quarter(CurrentD ate()) Query Summary Returns information about the queries in a document. string QuerySummary([dp]) = QuerySummary() Prompt Summary A.) Enter Current Time ID (YYYYMMDD) 20131031 B.) Enter Prior Time ID (YYYYMMDD) 20130930 4 – Considering Current Date as 12/24/13 *** Query Name:dp1 *** ** Query Properties: Universe:Index2 Last Refresh Date:12/24/13 4:10 PM Last Execution Duration: 40 Number of rows: 4,208 Retrieve Duplicate Row: ON ** Query Definition: Result Objects: Calendar Date, Product Sales Amount, Forecast Sales Amount, Country, Product Type Filters (Time Id Equal 20,130,930 ) Rank Ranks a measure by dimensions. • The function uses the default calculation context to calculate the ranking if you do not specify ranking dimensions. • You must always place dimensions in parentheses even if there is only one dimension in the list of ranking or reset dimensions. • When you specify a set of ranking or reset dimensions you must separate them with semi-colons. • By default the ranking is reset over a section or block break. Int Rank(measure;[ranki ng_dims][;Top|Botto m][;(reset_dims)]) ranking_dims - The dimensions used to rank the measure Top | Bottom – Top is for Descending Order and Bottom is for Ascending Order. reset_dims - The dimensions that reset the ranking Note: Multiple Data providers should use Data Provider name in the Query Summary or else it will return complete set of Data provider information =Ran([sales];([Country])) Country USA UK UAE Sales 5,63,482 9,923,756 10,458,691 =Ran([sales];([Country]);Bottom) The Bottom argument means that the measures are ranked in descending order. Country USA UK UAE Sales 5,63,482 9,923,756 10,458,691 Rank 1 2 3 =Ran([sales]; ([Country];[Year]);([Country])) The rank is reset on the Country dimension. Country USA USA USA UK UK UK UAE Year FY2011Q1 FY2011Q2 FY2011Q4 FY2012Q1 FY2012Q2 FY2012Q3 FY2013Q3 Ref Value Reference value of a report object when data tracking is activated Input_type RefValue(obj) = RefValue([Sales]) Ref Value Date The date of the reference data used for data tracking Response to a prompt when the reference data was the current data. • The function returns an empty string if data tracking is not activated. • 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. • If you selected more than one Date RefValueDate() = RefValueDate() String RefValueUserRespon se([dp;]prompt_strin g[;Index]) = RefValueUserRespo nse( "Enter Country Name” ) Ref Value User Response Rank 3 2 1 Sales 2,000,000 5,000,000 6,000,000 300,000 5,600,000 7,900,100 8,000,000 Rank 1 2 3 1 2 3 1 500 - if the value of the [Sales] measure is 500 in the reference data. 12/24/13 USA - if you entered "USA" in the "Enter Country Name" prompt at the time when the reference data was the current data. Relative Date Relative Value Replace value in answer to a prompt, the function returns a string consisting of a list of values (or primary keys if the Index operator is specified) separated by semi-colons. Date relative to another date. The num_days parameter can be negative to return a date earlier than start_date. Returns previous or subsequent values of an object. • The object must be a measure or a detail of a dimension available in the block. • The sort order of the list of values of the slicing dimensions is used to determine the output of the function. The sort order is determined by two factors: sorts applied to the slicing dimensions, and the order in which the slicing dimensions are listed in the function. • A dimension used as a section master can be specified as a slicing dimension. • All the slicing dimensions must be present in the block or section header of the block in which the function is placed. If a slicing dimension is later removed from the block, the function returns the #COMPUTATION error. • If the offset exceeds the number of rows in the list of values of the slicing dimension, the function returns null. • RelativeValue cannot be used recursively. • You must always place dimensions in parentheses even if there is only one dimension in the list of slicing dimensions. Replaces part of a string with another string. Date RelativeDate(start_d ate;num_days) =RelativeDate(Curr entDate();10) 1/3/14 – if the current Date in 12/24/13 Input_type =RelativeValue([Sales];([Year]);-1) RelativeValue(measu re|detail;slicing_dims Year Quarter Sales Rank ;offset) 2011 Q1 200,000 2011 Q2 500,000 2011 Q3 600,000 2012 Q4 300,000 2012 Q1 560,000 200,000 2012 Q2 790,100 500,000 2012 Q3 800,000 600,000 2012 Q4 850,00 300,000 String Replace(replace_in;r eplaced_string;replac e_with) replace_in - The =Replace("SAP Business OBJECTS";"OBJECT S";"Objects") SAP Business Objects Report Filter Returns the Object information in which report filters applied to an object or report Report Filter Summary Summary of the report filters in a document or report. If report_name is omitted, ReportFilterSummary returns a summary of all the report filters in all the reports (tabs) in the document. string in which the text is replaced replaced_string - The text to be replaced replace_with - The text that replaces replaced_string String ReportFilter(obj) =ReportFilter([Cou ntry]) String ReportFilterSummary (report_name) = ReportFilterSumma ry() =ReportFilterSumm ary("Report 2" United States – If filter applied in the report / object where Country =”United States” *** Filter on Report Report 1 *** No Filter on Report 1 *** Filter on Report Report 2 *** No Filter on Report 2 *** Filter on Report Report 3 *** No Filter on Report 3 *** Filter on Report Report 2 *** No Filter on Report 2 Report 2 – Tab name where Report Name function is placed Objects – Rightmost 7 Characters displayed. Report Name Returns Name of the report (Tab Name) String ReportName() =ReportName Right Returns rightmost characters of a string Right pad Pads a string on its right with another string. • 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. Trim the trailing spaces from a string String Right(string;num_cha rs) String RightPad(padded_stri ng;length;right_string ) =Right("SAP Business Objects";7) =RightPad("SAP ";6;"BO") =RightPad("SAP ";8;"BO") SAP BOBO String RightTrim(trimmed_s tring) =RightTrim(“SAP BO “) SAP BO =RightTrim([Countr y]) =Round(123.45;1) =Round(123.45;-1) USA – If [Country] has values of “USA “. 123.5 120 Right Trim Round Round a Number Num Round (number;round_level SAP BO Row Index Running Average Running Count Row Number in a Table. Row numbering starts at 0 and RowIndex returns #MULTIVALUE when placed in a table header or footer. Returns the running average of a measure. • 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 semicolons. • RunningAverage does not automatically reset the average after a block break or new section. The running count of a number set. • 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- ) Integer RowIndex() Num RunningAverage(mea sure[;Row|Col][;Inclu deEmpty][;(reset_di ms)]) measure - Any measure Row|Col - Sets the calculation direction IncludeEmpty Includes empty values in the calculation reset_dims - Resets the calculation on the specified dimensions Num RunningCount(dimen sion|measure[;Row| Col][;IncludeEmpty][; (reset_dims)]) measure - Any measure Row|Col - Sets the calculation direction IncludeEmpty Includes empty values in the calculation reset_dims - Resets the calculation on the specified dimensions =Round(1600;-3) =RowIndex() 2000 0 – First row of the table. =RunningAverage([Sales]) Country USA USA UK Region East West North Sales 3568124 300456 8489381 Running Average 3568124 1934290 4119320.3 =RunningAverage([Sales];([Country])) Country USA USA UK Region East West North Sales 3568124 300456 8489381 Running Average 3568124 1934290 8489381 =RunningCount([Sales]) Country USA USA UK Region East West North Sales 3568124 300456 8489381 Running Count 1 2 3 =RunningCount([Sales];([Country])) Country USA USA UK Region East West North Sales 3568124 300456 8489381 Running Count 1 2 1 Running Max Running Min colons. • RunningCount does not automatically reset the count after a block break or new section. Running maximum of a dimension or measure. • 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 semicolons. • RunningMax does not automatically reset the max after a block break or new section. Running minimum of a dimension or measure. • 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 semicolons. Input_type RunningMax(dimensi on|measure[;Row|C ol][;(reset_dims)]) measure - Any measure Row|Col - Sets the calculation direction reset_dims - Resets the calculation on the specified dimensions Input_type RunningMin(dimensi on|measure;[Row|C ol];[(reset_dims)]) measure - Any measure Row|Col - Sets the calculation direction reset_dims - Resets the calculation on the specified dimensions =RunningMax([Sales]) Country USA USA USA UK UK Region East North West North South Sales 3568124 3000900 300456 8489381 4561090 Running Max 3568124 3568124 3568124 8489381 8489381 =RunningMax([Sales];([Country])) Country USA USA USA UK UK Region East North West North South Sales 3568124 3000900 300456 8489381 4561090 Running Max 3568124 3568124 3568124 8489381 8489381 Sales 3568124 3000900 300456 8489381 4561090 Running Min 3568124 3000900 300456 300456 300456 =RunningMin([Sales]) Country USA USA USA UK UK Region East North West North South =RunningMin([Sales];([Country])) Country USA USA USA UK UK Region East North West North South Sales 3568124 3000900 300456 8489381 4561090 Running Min 3568124 3000900 300456 8489381 4561090 Running Product Running Sum • RunningMin does not automatically reset the minimum after a block break or new section. Running product of a measure. • 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 semicolons. • RunningProduct does not automatically reset the product after a block break or new section. Running product of a measure. • 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 semicolons. • RunningSum does not automatically reset the sum Input_type RunningProduct(dim ension|measure;[Ro w|Col];[(reset_dims)] ) measure - Any measure Row|Col - Sets the calculation direction reset_dims - Resets the calculation on the specified dimensions Num RunningProduct(mea sure[;Row|Col][;(rese t_dims)]) measure - Any measure Row|Col - Sets the calculation direction reset_dims - Resets the calculation on the specified dimensions =RunningProduct([No. of Branch]) Country USA USA UK No. of Branch City LA SFO London Running Product 3 15 600 3 5 40 =RunningSum([Sales]) Country USA USA USA UK UK Region East North West North South Sales 3568124 3000900 300456 8489381 4561090 Running Sum 3568124 6569024 6869480 15358861 19919951 =RunningSum([Sales];([Country])) Country USA USA USA UK UK Region East North West North South Sales 3568124 3000900 300456 8489381 4561090 Running Sum 3568124 6569024 6869480 8489381 13050471 Sign Sin Sqrt StdDev StdDevP Substr after a block break or new section. The sign of a number. Sign returns -1 if number is negative, 0 if number is zero and 1 if number is positive. Returns the sine of an angle. Square root of a number. The standard deviation of a measure. 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 • 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 Population standard deviation of a measure. 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. Returns part of a string Int Sign(number) =Sign(23) =Sign(-31) =Sign(0) 1 -1 0 Num Sin(angle) Num Sqrt(number) Num StdDev(measure) =Sin(1000) =Sin(-100) =Sqrt(4) =StdDev([measure] ) 0.83 0.51 16 2.58 - If measure has the set of values (2, 4, 6, 8). Num StdDevP(measure) =StdDev([measure] ) 2.24 - If measure has the set of values (2, 4, 6, 8). String SubStr(string;start;le ngth) =Substr("SAP Business Objects";1;12) SAP Business string - Any string =Substr("SAP Objects Sum The Sum of a Measure. • 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 {}. • If the member set expression does not specify a precise member or node, the hierarchy referenced must be present in the table, then the member set expression references the current member in the hierarchy in the table. If the hierarchy is not in the table, the function returns the message #MULTIVALUE. • Delegated measure aggregation returns #TOREFRESH when the required aggregation is not available in the query. The user has to refresh the document to get the new level of aggregation. This occurs for example when using the filter bar when the user selects a value before “all values” and vice versa when selecting “all values” before a selected value. • When migrating from XIR2 to XIR3, aggregation functions containing IN and WHERE clauses in XI2 queries should be included into Sum function definitely by using parenthesis as follows: In XIR2, the formula: =Sum( [Measure] In ([Dim 1]) ) Where start - The start Business position of the Objects";14;7) extracted string length - The length of the extracted string Num =Sum([Sales]) Sum(measure[;memb er_set]) Total Amount should be returned. Tan To Date To Number Trim ([Dim 3]="Constant") should be expressed as: =Sum(([Sales revenue]ForEach([Month]))Wh ere([Month]=1)) The tangent of an angle The TimeDim time dimension allows you to build a time axis from a date type universe object. TimeDim returns the data for the dates given as the first parameter over the time periods given as the second parameter. When there are periods that have no data, the first day of each empty period is returned. This ensures a full axis for the given period. This guarantees: • That the axis retains the natural time order (oldest objects first, the most recent objects last). • The axis contains all the periods between the minimum and maximum dates in the current context. Use the above function in conjunction with the following functions: • DayName • DayNumberOfMonth • DayNumberOfWeek • DayNumberOfYear • Month • MonthNumberOfYear • Quarter Turns a character string into a date. Give the date format as the parameter to indicate to Web Intelligence how to convert the string into a date. The date format you provide must match the format of the date in the original string. Refer to the link below for the possible date formats. Returns a string as a number. If string is not a number, ToNumber returns #ERROR. Trims the leading and trailing spaces from a string Num Tan(angle) TimeDim([Date]; Period) Date - The date object for the report, for example, InvoiceDate. Period - The period for the results, from the following values: • DayPeriod • MonthPeriod • QuarterPeriod • YearPeriod When no value is selected, the DayPeriod is used by default. This object should be a data provider object, it must be available from report objects, and cannot be a variable. Date ToDate(date_string;f ormat) =Tan(180) 1.34 If we have data for Payment date as mention below: Payment Date Payment 1/1/2013 50,000.00 1/8/2013 35,000.00 7/3/2013 70,000.00 Use the TimeDim([Payment Date]) in the above table the axis value for Date will be fill the Null also. Payment Date 1/1/2013 Payment 50000 1/8/2013 35000 TimeDim 1/1/2013 1/2/2013 1/3/2013 1/4/2013 1/5/2013 1/6/2013 1/7/2013 1/8/2013 1/9/2013 1/10/2013 1/11/2013 1/12/2013 Etc., =ToDate("24/12/20 13";"dd/MM/yyyy" ) 24/12/2013 ToDate("24/12/13" ;"dd/MM/yy") 24/12/13 ToDate("24/12/13" ;"dd/MMMM/yy") 24/DECEMBER/13 Num ToNumber(string) =ToNUmber(“5454 ”) 5454 String Trim(string) =Trim(“ SAP BO “) SAP BO Truncate Truncates a number Num Truncate(number;tru ncate_level) =Truncate(1400;-3) 1000 The function rounds/truncates to the nearest 10 (parameter = -1), 100 (parameter = -2), 1000 (parameter = -3) and so on. Payment Date Unique Name Of Universe Name Returns the unique name of an object The name of the universe on which a data provider is based String UniqueNameOf(obj) String UniverseName(dp) =UniqueNameOf([P ayment Date]) =UniverseName([Q uery 1]) Upper Url Encode Converts a String to Upper Case Applies URL encoding rules to a string String Upper(string) String UrlEncode(html) User Response Returns the response to a prompt. • 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. • If you select more than one value in answer to a prompt, the function returns a string consisting of a list of values (or primary keys if the Index operator is specified) separated by semi-colons. The Variance of a Measure. 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) The variance is the square of the standard deviation. You can use extended syntax context operators with Var. Population variance of a measure. String UserResponse([dp;]p rompt_string[;Index]) =Upper(”sap”) =URLEncode("http: //www.google.com ") =UserResponse(“En USA – If you entered ter Country USA in the Prompt Name”) Var VarP Payment – This was the Name of a universe. SAP http%3A%2F%2Fwww %2Egoogle%2Ecom dp - The data provider prompt_string - The prompt text Index - Tells the function to return the database primary keys of the prompt values Num Var(measure) =Var([Sales]) 6.67 - If Sales has the set of values (2, 4, 6, 8) Num VarP(measure) =VarP([Sales]) 5 - If Sales has the set of values (2, 4, 6, 8) Week Word Cap Year 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. Week Number of the Date Int Week(date) =Week(CurrentDat e()) Capitalizes the first letter of all the words in a string Year in a date String WordCap(string) Int Year(date) =WordCap(“Payme nt due for june”) =Year(CurrentDate( )) 2 – Current Date 12/24/13 and Start week is for Monday Payment Due For June 2013
© Copyright 2024