BO Formula
|
What You Want to Do
|
Format/Example
|
Notes
|
|
Calculate and display a percentage of an object
|
=Sum(<Sales revenue>*0.3%)
|
The percent must be preceded by a 0 (i.e., “0.3”) if a
decimal point is used.
|
|
Convert a character string to a date
|
=ToDate(character,format)
Example 1:
=ToDate(“20030130”,“yyyymmdd”)
Result:
|
The single value or variable is followed by the
existing format of the string.
This function can be used in If Then Else statements.
If the variable that follows Then
is date-type, the variable that follows Else
must also be date-type.
|
|
Convert a character string to a number
|
=ToNumber(character)
Example:
=ToNumber(“356”)
Result: 356.
|
This function can be used in If Then Else statements.
If the variable that follows Then
is numeric, the variable that follows Else
must also be numeric.
This function works with both constants and variables.
|
|
Convert a date to a character string so that it is
compatible with other elements in a formula
|
=FormatDate(date, string format)
Example:
=FormatDate(<ApplicationDate>,
“mm/dd/yyyy”)
|
The string format must be enclosed in quotes.
This function works with both constants and variables.
Use this function when you want to concatenate text
with a variable. The variable must be
character-type.
|
|
Convert a number to a character string
|
=FormatNumber(number,string format)
Example:
=FormatNumber(<Eff Seq #>,“0”)
|
The string format must be enclosed in quotes.
This function works with both constants and variables.
Use this function when you want to concatenate text
with a variable. The variable must
character-type.
|
|
Convert upper case to lower case (or vice versa)
|
=Lower(<Name>)
=Upper(<Name>)
|
The Lower() function displays a character string in
lower case while the Upper() function displays the string in upper case.
|
|
Count the number of zeroes or null values in a column
|
=If
<mycolumn> = 0 or IsNull(<mycolumn>) Then 1 else 0
|
This formula returns a “1” anytime a blank or zero
appears in the specified column.
Create a variable with this formula, then insert a sum on the variable
to count all occurrences of null or zero.
|
|
Create a table where every other row is shaded
|
Variable formula:
=Mod(LineNumber(), 2)
Alerter:
<New Variable> = 1
|
1. Create a
variable with the formula displayed at left.
The formula checks the line number for each row and returns a value of
1 for any row with a line number that is evenly divisible by a value of
2.
2. Highlight all
columns of the table (but not the headers).
Create an alerter based on the new variable which shades every row for
which the value of the variable is 1.
Under Cell Content, Format…,
go to Shading and select the
desired shading option.
|
|
Create a variable that displays first name and another
that displays last name based on a variable that displays LAST NAME,FIRST
NAME, e.g.
DOE,JANE
|
Last name:
=SubStr(<EmpName> ,1 ,(Pos(<EmpName>
,",")-1))
First name:
=SubStr(<EmpName> ,(Pos(<EmpName> ,
","))+1 ,Length(<EmpName>)-(Pos(<EmpName> ,
",") ))
First name (for
variable with a space after the comma):
=SubStr(<EmpName> ,(Pos(<EmpName> ,
","))+2 ,Length(<EmpName>)
-(Pos(<EmpName> , ","))
|
The Substr() function extracts a sequence of characters
from a character string.
The first formula says: start at position 1, display
all characters up to one position before the comma.
The second formula says: start one character past the
comma and display the number of characters that results from this
calculation: the total number of
characters in this field minus the position number of the comma.
Note: If there is a space after the comma, the
first name formula should start 2 characters after the comma.
|
|
Create calculations that ignore filters on a document
|
=NoFilter(formula)
Example: =NoFilter(Max(<Quantity
sold>))
|
Returns the calculation performed on data with all
filters on the data ignored.
|
|
Display the current page number
|
=Page()
Examples:
=”Page ” & FormatNumber (Page(), "#")
|
The page number will only display correctly in Page
Layout view (View, Page Layout). The
example uses the FormatNumber() function to convert the page number to a
character string and concatenates the number with text. Example:
“Page 1.”
|
|
Display the last time a document was refreshed
|
=LastExecutionDate(data provider)
Example:
=LastExecutionDate("Query 1 with EFASHION")
|
Use the Number tab of the Cell Format dialog
box to format the date.
Select Insert,
Special Field, Date and Time, Last Refresh to insert this formula automatically.
|
|
Display the name of the document
|
=DocumentName()
Example:
=Substr(DocumentName() ,1 , Length(DocumentName())-4)
|
The example displays the document name without the .rep
extension. It uses the SubStr and
Length functions to take a string from the document name, starting in
position 1, returning the length of the document name minus the last 4
characters.
|
|
Display user response to a prompt.
|
=UserResponse(data provider, prompt)
Example 1:
=UserResponse ("Query 1 with EFASHION" ,
"enter state")
Example 2:
=UserResponse (DataProvider(<State>) ,
"state")
|
Select Insert, Special Field, Query Prompt from
the main menu to insert this formula automatically.
In Example 1, the data provider name is hard coded and
would have to be edited if the query name were changed.
Example 2 uses the DataProvider() function which
requires that you specify a variable (in this case, <State>).
|
|
Suppress blank values in a column
|
=IsNull(character string or number or date)
Example: =Not
IsNull(<EmplId>)
To suppress zeroes rather than nulls, use this
formula: =<EmplID> <> 0.
Example of If Then Else statement:
=If IsNull(<DeptID>) Then <Acct Grp> Else
<DeptID>
|
Checks whether a constant or variable is empty. The example can be used to define a formatted
filter and will hide all rows in which the specified field is empty. Select Format,
Filters, select the table, click Add…,
select variable to filter, click Define…,
type the formula.
IsNull() can also be used in an If Then Else
statement. The variable that follows
“Then” must be the same data type as the variable that follows “Else.”
|
0 comments:
Post a Comment