BQL Quick Reference

ABS

Absolute value of a measure value.

ABS([measure])

ADD

Adds a value to end of an existing list.

ADD([ListVariable],10)

ADDPARENTCHILD

Adds a single parent-child pair to the current list of pairs.

ADDPARENTCHILD([ParentID],[ChildID])

ARCCOS

Returns the arc cosine of a number between ο and π.

ARCCOS([logical_column])

ARCSIN

Returns the arc sine of a number between -π/2 and π/2.

ARCSIN([logical_column])

ARCTAN

Returns the arc tangent of a number between -π/2 and π/2.

ARCTAN([logical_column])

ATAN2

Returns the arc tangent of x and y.

ATAN2(x,y)

Casting

Convert data types. Expressions and variables can be cast to different data types. All data types implicitly cast to a varchar so no cast statement is needed. When a cast is not supported, such as casting ‘A’ to a float, a NULL is returned. See Data Type Casting.

FLOAT()
INTEGER()
DATETIME()

CEILING

Round up. Returns the closest integer greater than or equal to the given number.

CEILING([measure])

COS

Returns the cosine of a given angle.

COS([logical_column])

DATEADD

Adds or subtracts an integer from a date.

DATEADD(Year|Month|Day|Hour|Minute|Second, integer, [date_attribute])

DATEDIFF

Returns the difference in dates as an integer value. NULLS are interpreted as zero (0). See DATEDIFF.

DATEDIFF(Year|Month|Day|Hour|Minute|Second, [start_date_attribute],[end_date_attribute])

DATEPART

Returns integers from dates.

DATEPART(Year|Month|Day|Hour|Minute|Second, [date_attribute])

DEGREES

Converts x radians to degrees.

DEGREES(x)

DISPLAY BY

Sorts results after the result set has been generated and any other display operations have been performed. See Result Sets and Display Operations.

SELECT [query] FROM [ALL] DISPLAY BY[attribute|measure]ASCENDING|DESCENDING

DTOP, (Display Top)

Displays top n results of the returned dataset.

To get the top n from the database, see Top N.

DISPLAY WHERE

A display filter. Filters on the returned data set (the data in cache). Use for dimension columns and measures. Returns entire query but only displays data that matches criteria. See Result Sets and Display Operations.

SELECT [query] FROM [All] DISPLAY WHERE [attribute|measure]=value

DRANK

Dense rank returns the rank of each row within the result set, without any gaps in the ranking.

DRANK([measure])

EXP

Returns e raised to the power of the number given.

EXP([logical_column])

FIND

Same as LOOKUPVALUE, but does not look for an exact match. If the match is exact that row is returned, otherwise, the first row that is greater than the lookup value is used. Parameters can be added before the logical query and referenced in the logical query using the %index syntax, starting with %0.

FIND(index_of_column_to_lookup, value_to_lookup, index_of_column_to _return, logical_query)

FLOOR

Returns the closest integer less than or equal to a given number.

FLOOR([logical_column])

FORMAT

Formats a measure or date to the particular string format provided. See FORMAT and Pattern String Formats.

FORMAT([measure|date_attribute],'format_string')

FUNCTION

Creates a custom function that can be used in any BQL query including an expression. See FUNCTION and FUNCTIONLOOKUP.

FUNCTION(return_data_type, script_statement_block, logical_query)

FUNCTIONLOOKUP

Creates a custom function that executes a script over an entire result set to return a result. See FUNCTION and FUNCTIONLOOKUP.

FUNCTIONLOOKUP(return_data_type, lookup_index, lookup_expression, script_statement_block, logical_query)

GETDAYID

Returns a unique integer signifying the day based on a date or datetime value.

[ID Value] = GETDAYID([date_value])

GETLEVELATTRIBUTE

Provides additional data either on the entire tree or on a given record that is being processed. Two options are supported. NumLevels returns the maximum depth of items in the tree. CurrentDepth returns the depth of the current item in the tree.

GETLEVELATTRIBUTE('NumLevels')

GETLEVELATTRIBUTE('CurrentDepth')

GETLEVELVALUE

For the current pair, returns the ID n levels deep into the hierarchy. If there are fewer than n levels, it returns the ID of the lowest level available. The IDs will repeat for levels lower than their depth.

GETLEVELVALUE(n)

GETMONTHID

Returns a unique integer signifying the month based on a date or datetime value.

[ID Value] = GETMONTHID([date_value])

GETPROMPTFILTER

Passes a user-selected prompt filter from a dashboard to a measure expression. See GETPROMPTFILTER and GETPROMPTVALUE.

GETPROMPTFILTER('prompt_name')

GETPROMPTVALUE()

Passes a prompt filter value from a dashboard to a measure expression. An optional second argument (such as null or a string such as '2012') can be used in the event that the first argument is not a currently defined prompt. See GETPROMPTFILTER and GETPROMPTVALUE.

GETPROMPTVALUE('prompt_name', 'optional_default_value')

GETVARIABLE

Retrieves the value of a variable. See Variables and BQL.

GETVARIABLE('variable_name')

GETWEEKID

Returns a unique integer signifying the week based on a date or datetime value.

[ID Value] = GETWEEKID([date_value])

IFNULL

Allows an expression to return a different result if it is null.

IFNULL(evaluation, result_if_NULL)

IIF

Immediate IF. Allows an expression to return one result if the specified condition is true and a different one if it is false. Can be nested.

IIF(evaluation, result_if_TRUE, result _if_FALSE)

ISINF

Evaluates an expression and returns TRUE if the value is +/- infinity. The result can be used as a conditional check.

ISINF(evaluation, result_if_TRUE, result _if_FALSE)

ISNAN

Evaluates an expression and returns TRUE if it is not a number and FALSE if it is. The result can be used as a conditional check.

ISNAN(evaluation)

IIF(ISNAN(evaluation), result_if_TRUE, result _if_FALSE)

LENGTH

Returns the number of characters in a string. This can also be used in an ETL script to return the number of elements in a List object.

LENGTH([dimension])

LET

Allows logic re-use with expression-level variables. See LET.

LET(variable_declarations, expression_ body)

LN

Returns the natural logarithm of a given number in a specified base.

LN([measure | numeric_attribute])

LOG

Log base e of X.

Returns the logarithm of a given number in a specified base.

LOG([measure | numeric_attribute])

LOG10

Log base 10 of x.

LOG10([measure | numeric_attribute])

LOOKUPROW

Use instead of LOOKUPVALUE if the result set will be in the same order as the existing set. See LOOKUPROW.

LOOKUPROW(index_of_the_row_to return, index_of_the_column_to_return, SELECT [query] FROM [All])

LOOKUPVALUE

Lookup based on value in the current result set to get a dimension or measure value from another result set. Looks for an exact match. See LOOKUPVALUE.

LOOKUPVALUE(column_number_from _the_query, [value_in_the_current_query _to_look_at], column_number_of_the_new_query, SELECT[query] FROM [All])

LTRIM

Trims white space from x on the left.

LTRIM(x)

MEDIAN

Returns the value in the middle of all measure values at the report grain.

Supports an optional BY break by attribute.

MEDIAN([measure] BY [attribute])

NEXTCHILD

Once a set of parent-child pairs have been added, the list can then be iterated over one by one to flatten. NEXTCHILD positions the current record to the next available pair. If there are no more pairs left, it returns false.

NOW

Returns the current date and time.

See NOW and NOWDATE.

NOW

NOWDATE

Returns current date at midnight.

See NOW and NOWDATE.

NOWDATE

NUMROWS

Returns the total number of rows in the current result set.

This function is not available in ETL Services.

NUMROWS()

ORDER BY

Sort function. See BQL Basics - Controlling Sort Ordering.

SELECT [query] FROM [All] ORDER BY [measure | attribute ] Ascending | Asc | Descending | Desc, [measure | attribute ] Ascending | Asc | Descending | Desc ...

POSITION

Returns the position of a string within an attribute. An optional parameter specifies the starting index to begin the search.

POSITION('search string', [attribute], starting_position_number_to_begin_search)

POW

Raise x to the power of y.

POW(x,y)

RADIANS

Converts x degrees to radians.

RADIANS(x)

PI

Returns Pi.

PI()

PTILE

Returns the percentile rank of each row within the result set.

PTILE([measure])

RANDOM

Returns a double from 0.0 to 1.0. Ignores parameter values.

RANDOM()

RANK

Returns the rank of each row within the result set. See Result Sets and Display Operations - Ranking Calculations.

RANK([measure])

To rank within a group:

RANK([measure]) BY ([attribute])

REMOVEALL

Removes all elements from a list.

REMOVEAT

Allows you to remove an item from a given position in a list.

REMOVEAT([ListVariable],[IndexToRemove])

REPLACE

Replaces a string with a string.

REPLACE([attribute], 'part_of_string_to_replace', 'replacement_string')

REPLACEALL

Replaces a string using a regular expression.

REPLACEALL([attribute],[regular_expression], replacement_string)

REXP

Executes an R expression within a BQL query. Birst casts the results to the expected return type.

REXP(return_data_type, 'R_ expression_to_run').

ROWNUMBER

Returns the row number for the current result set.

This function is not available in ETL Services.

ROWNUMBER()

RSUM

Returns the trailing sum of values, based on the window size, at the current report grain for the window provided. See RSUM.

RSUM(window_size_integer, [ measure])

RTRIM

Trims all white space from x on the right.

RTRIM(x)

SIGN

Returns +1 for x > 0, 0 for x == 0, and -1 for x < 0

SIGN(x)

SIN

Returns the sine of a given angle.

SIN([measure | numeric_attribute])

SPACE

Returns x spaces.

SPACE(x)

SPARSE

Shows dimension column values for which there is no fact data. Works with multiple attributes. See SPARSE.

SPARSE([attribute, attribute...])

SQRT

Returns the square root of a measure.

SQRT([measure])

STAT

Aggregate stats based on another result set.

STAT supports the PERCENTILE operator and percentile_value argument, where the value is between 0 and 1. See STAT.

STAT(MEDIAN | AVG | MIN | MAX | SUM | COUNT | COUNTDISTINCT| STDDEV, index, [query])

STAT(PERCENTILE, percentile_value, index, [query])

SUBSTRING

Returns a portion of a given string. Uses a zero-based index.

Starting position is 0, ending position is the position after the last one that should be included.

SUBSTRING([attribute], start_ position_number, end_position_ number)

TAN

Returns the tangent of a given angle.

TAN([measure | numeric_attribute])

TOLOWER

Returns a character expression after converting data to lower case.

TOLOWER([attribute])

TOP

Filter by the top n results from the database. See BQL Basics.

To filter by top n results in cache, see DTOP.

SELECT TOP number query

TOTIME

Converts integer values to a time format.

Supported in ETL scripts and column expressions.

Years and months are not supported.

TOTIME(1000000000, 'hours,minutes,seconds', '%d:%02d:%02d')

TOUPPER

Returns a character expression after converting data to upper case.

TOUPPER([attribute])

TRANSFORM

Directly manipulate or create from scratch the report-level result set. See TRANSFORM.

TRANSFORM(script)

TREND

Uses the least squares method to extrapolate values based on a linear trend. See TREND.

TREND(index_of_column_to_return, index_of_column_to_lookup, value_to_lookup, query)

TRIM

Trims leading and trailing spaces. Can be used in a constant to pad a value.

TRIM([attribute])
[constant_name]= TRIM('string_value_with_spaces')

Absolute

Performs calculation based on specific value.

[measure]/[measure] {[attribute] = value}

Share

Relative measure value calculation.

[measure]/[measure] {[attribute]}

Relative (Growth)

Relative measure comparison, such as month-over-month-growth.

[measure]/[measure] {[time.attribute] +-=value}

Grand Totals

Relative total. In this case Total is a special column.

[measure]/[measure] {[dimension.Total]}

=

Equals

=

<

Less than

<

>

Greater than

>

<>

Not equals. Successful when the column on the left does not match the string value.

<>

<=

Less than or equal to

<=

>=

Greater than or equal to

>=

AND

Returns true if all filter conditions are met.

AND

OR

Returns true if either filter condition is met.

OR

LIKE

Searches for a specified pattern in a column.

LIKE

NOT LIKE

Searches for the non-existence of a specified pattern in a column.

NOT LIKE

IS NULL

Condition is satisfied if the column value contains a null value, or an expression evaluates to null.

IS NULL

IS NOT NULL

Condition is satisfied if the column value is not null, or an expression does not evaluate to null.

IS NOT NULL

+

Concatenation

+

% 

Modulo

[dividend] %  [divisor]

USER

Session variable that stores the user login for that specific session.

Use as a value in expressions.
Not for use in ETL.

LoadDate

Repository variable that stores the last load date.

Use as a value in expressions

LoadDateSFDC

Repository variable that stores the load date and time of the last Salesforce.com load.

Use as a value in expressions

LoadNumber

Repository variable that stores the last load number.

Use as a value in expressions

LoadStartTime

Repository variable that stores the start date and time of the last load for the space.

Note: LoadInitiateTime was a previous version of this variable.

Use as a value in expressions