BQL Quick Reference

You can use the BQL functions listed here in report expressions in Designer and Visualizer, unless otherwise noted.

Functions


Function	Use	Syntax	Example
ABS	Absolute value of a measure value.	`ABS([measure])`	`ABS([OrderDate: Sum: Quantity])`
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])`	`ARCCOS([OrderDate: Sum: Quantity])`
ARCSIN	Returns the arc sine of a number between -π/2 and π/2.	`ARCSIN([logical_column])`	`ARCSIN([OrderDate: Sum: Quantity])`
ARCTAN	Returns the arc tangent of a number between -π/2 and π/2.	`ARCTAN([logical_column])`	`ARCTAN([OrderDate: Sum: Quantity])`
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.	`FLOAT()` `INTEGER()` `DATETIME()`	`INTEGER(GETPROMPTVALUE('Stage 4 Probability'))`
CEILING	Round up. Returns the closest integer greater than or equal to the given number.	`CEILING([measure])`	`CEILING([OrderDate: Sum: Quantity])`
COS	Returns the cosine of a given angle.	`COS([logical_column])`	`COS([OrderDate: Sum: Quantity])`
DATEADD	Adds or subtracts an integer from a date.	`DATEADD(Year\|Month\|Day\|Hour\|Minute\|Second, integer, [date_attribute])`	`DATEADD(Day, 5, [Time.Date])`
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])`	`DATEDIFF(Month,[Order_Details.OrderDate],[Order_Details.RequiredDate])`
DATEPART	Returns integers from dates.	`DATEPART(Year\|Month\|Day\|Hour\|Minute\|Second, [date_attribute])`	`DATEPART(Day, [Time.Date])`
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.	`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, refer to TOP.	`SELECT DTOP n [query] FROM[all] ORDER BY [attribute\|measure]Ascending\|Asc\|Descending\|Desc`	To display the top 5 results of a newly-created expression: `SELECT DTOP 5 USING OUTER JOIN [Time.Year] 'F9',[Order_Details.ContactName] 'F11',[OrderDate: Sum: Quantity] 'OrderDate: Sum: Quantity',[OrderDate: Sum: UnitPrice]'OrderDate: Sum: UnitPrice', [OrderDate: Sum: Quantity]*[OrderDate: Sum: UnitPrice] 'F19' FROM [ALL] ORDER BY [EXPR.F19] ASCENDING NULLS LAST`
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.	`SELECT [query] FROM [All] DISPLAY WHERE [attribute\|measure]=value`	`SELECT [Sales] FROM [All] DISPLAY WHERE [Sales]>10000`
DRANK	Dense rank returns the rank of each row within the result set, without any gaps in the ranking.	`DRANK([measure])`	`DRANK([OrderDate: Sum: Quantity])`
EXP	Returns e raised to the power of the number given.	`EXP([logical_column])`	`EXP([OrderDate: Sum: Quantity])`
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)`	`FIND(0,[Products.ProductName], 1, SELECT [Products.ProductName],[OrderDate: Sum: Quantity] FROM [All])`
FLOOR	Returns the closest integer less than or equal to a given number.	`FLOOR([logical_column])`	`FLOOR([OrderDate: Sum: Quantity])`
FORMAT	Formats a measure or date to the particular string format provided.	`FORMAT([measure\|date_attribute],'format_string')`	`FORMAT([OrderDate: Sum: Quantity],'##,###,##0.00')`
FUNCTION	Creates a custom function that can be used in any BQL query including an expression.	`FUNCTION(return_data_type, script_statement_block, logical_query)`	`FUNCTION (Float,` `Dim [Var1] as Float =3.0` `Dim [Var2] as Float = 2.0` `COMPLETE` `[Result] = [Var1] - [Var2]` `END COMPLETE,` `SELECT [Time.Year] FROM [All]` `)`
FUNCTIONLOOKUP	Creates a custom function that executes a script over an entire result set to return a result.	`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.	`GETPROMPTFILTER('prompt_name')`	`[OrderDate: Sum: Quantity] WHERE GETPROMPTFILTER('MyPrompt')`
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.	`GETPROMPTVALUE('prompt_name', 'optional_default_value')`	`[OrderDate: Sum: Quantity]*Float(GETPROMPTVALUE('MyPrompt','2015'))`
GETVARIABLE	Retrieves the value of a variable.	`GETVARIABLE('variable_name')`	`GetVariable('LoadDate')`
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)`	`IFNULL([OrderDate: Sum: Quantity],0)`
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)`	`IIF([OrderDate: Sum: Quantity] >6000, 0,` `[OrderDate: Sum: Quantity])`
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)`	`IIF(ISINF([OrderDate: Sum: Quantity]),0,` `[OrderDate: Sum: Quantity])`
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)`	`IIF(ISNAN([OrderDate: Sum: Quantity]),0,` `[OrderDate: Sum: Quantity])`
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])`	`LENGTH([Products.CategoryName])` ETL Script Example: `LENGTH([myList]) - 1`
LET	Allows logic re-use with expression-level variables.	`LET(variable_declarations, expression_ body)`	`LET(Dim [AvgPrice] As Float = LookupValue(0,` `[Categories.CategoryName],1,Select` `[Categories.CategoryName],[OrderDate: Avg: UnitPrice]` `from [All]) Dim [TotalQuantity] As Float = LookupValue(0,` `[Categories.CategoryName],1,Select [Categories.CategoryName],` `[OrderDate: Sum: Quantity]` `from [All]), IIF([AvgPrice]<25,25,[AvgPrice])*[TotalQuantity])`
LN	Returns the natural logarithm of a given number in a specified base.	`LN([measure \| numeric_attribute])`	`LN([OrderDate: Sum: Quantity])`
LOG	Log base e of X. Returns the logarithm of a given number in a specified base.	`LOG([measure \| numeric_attribute])`	`LOG([OrderDate: Sum: Quantity])`
LOG10	Log base 10 of x.	`LOG10([measure \| numeric_attribute])`	`LOG10([OrderDate: Sum: Quantity])`
LOOKUPROW	Use instead of LOOKUPVALUE if the result set will be in the same order as the existing set.	`LOOKUPROW(index_of_the_row_to return, index_of_the_column_to_return,` `SELECT [query] FROM [All])`	`LOOKUPROW(0,1, SELECT [OrderDetail.OrderID],` `[OrderDate: Sum: Discount] 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.	`LOOKUPVALUE(column_number_from _the_query, [value_in_the_current_query _to_look_at], column_number_of_the_new_query, SELECT[query] FROM [All])`	`LOOKUPVALUE(0,[Products.CategoryName],1,` `SELECT [Products.CategoryName],[OrderDate: Sum: Quantity] 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])`	`MEDIAN([OrderDate: Sum: Quantity] BY[Products.Products])`
NEXTCHILD	After 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.	`NOW`	`yyyy-mm-dd hh:mm:ss` For example: `2016-04-19 15:24:25`
NOWDATE	Returns current date at midnight.	`NOWDATE`	`yyyy-mm-dd 12:00:00` For example: `2016-04-19 12:00:00`
NUMROWS	Returns the total number of rows in the current result set. This function is not available in ETL Services.	`NUMROWS()`	`NUMROWS()`
ORDER BY	Sort function.	`SELECT [query] FROM [All] ORDER BY [measure \| attribute ] Ascending \| Asc \| Descending \| Desc, [measure \| attribute ] Ascending \| Asc \| Descending \| Desc ..`	`SELECT [Product.Category Name], [Time.Year],[Unit Sales] FROM [All] ORDER BY [Product.Category Name] Ascending, [Time.Year] Descending`
PI	Returns Pi.	`PI()`	`PI()`
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)`	`POSITION(‘Bev’, [Product.ProductCategory], 3)` In this example, it would start looking for "Bev" in the product name at the 4th character.
POW	Raise x to the power of y.	`POW(x,y)`	`POW(4,2)`
PTILE	Returns the percentile rank of each row within the result set.	`PTILE([measure])`	`PTILE([OrderDate: Sum: Quantity])`
RADIANS	Converts x degrees to radians.	`RADIANS(x)`
RANDOM	Returns a double from 0.0 to 1.0. Ignores parameter values.	`RANDOM()`	`RANDOM()`
RANK	Returns the rank of each row within the result set.	`RANK([measure])` To rank within a group: `RANK([measure]) BY ([attribute])`	`RANK([OrderDate: Sum: Quantity])` `RANK([OrderDate: Sum: Quantity] BY [Products.CategoryName])` `SELECT [Time.Year],[Product.Category Name], RANK([Unit Sales] BY [Time.Year]) ‘Sales Rank’ FROM [All] WHERE [Time.Year]=2014 Or [Time.Year]=2015 DISPLAY BY [Time.Year] Ascending, [Sales Rank] Ascending`
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')`	`REPLACE([Products.CategoryName], 'ages', 'age')`
REPLACEALL	Replaces a string using a regular expression.	`REPLACEALL([attribute],[regular_expression], replacement_string)`	`REPLACEALL([Products.CategoryName],'P.*e','Organic Produce')`
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')`	`REXP(Number,'predict(model, newdata=data.frame(dist=c('+[Avg: dist]+')))')`
ROWNUMBER	Returns the row number for the current result set. T function is not available in ETL Services.	`ROWNUMBER()`	`ROWNUMBER()`
RSUM	Returns the trailing sum of values, based on the window size, at the current report grain for the window provided.	`RSUM(window_size_integer, [ measure])`	`RSUM(10,[OrderDate: Sum: Quantity])`
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])`	`SIN([OrderDate: Sum: Quantity])`
SPACE	Returns x spaces.	`SPACE(x)`
SPARSE	Shows dimension column values for which there is no fact data. Works with multiple attributes.	`SPARSE([attribute, attribute...])`	`SPARSE([Time.Year])`
SQRT	Returns the square root of a measure	`SQRT([measure])`	`SQRT([OrderDate: Sum: Discount])`
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.	`STAT(MEDIAN \| AVG \| MIN \| MAX \| SUM \| COUNT \| COUNTDISTINCT\| STDDEV, index, [query])` `STAT(PERCENTILE, percentile_value, index, [query])`	`STAT(MEDIAN, 1, SELECT [Products.CategoryName],[OrderDate: Sum: Quantity] FROM [All])` `STAT(PERCENTILE, .50, 1, SELECT [Products.CategoryName],[OrderDate: Sum: Quantity] FROM [All])`[
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)`	`SUBSTRING([Products.ProductName], 2, 8)` The example above returns "member" for "Camembert Pierrot".
TAN	Returns the tangent of a given angle.	`TAN([measure \| numeric_attribute])`	`TAN([OrderDate: Sum: Quantity])`
TOLOWER	Returns a character expression after converting data to lower case.	`TOLOWER([attribute])`	`TOLOWER([Products.CategoryName])`
TOP	Filter by the top n results from the database. To filter by top n results in cache, refer to DTOP.	`SELECT TOP number query`	`SELECT TOP 10 USING OUTER JOIN [Order_Date: Sum: Unit Cost] 'Column1', [Products.Product] 'Column2' FROM [ALL] ORDER BY [Order_Date: Sum: Unit Cost] ASC`
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')`	`TOTIME(Integer([Order_Details.OrderID]+[Order_Details.ProductID]*1000),'HH:mm:ss:SSS')+`
TOUPPER	Returns a character expression after converting data to upper case.	`TOUPPER([attribute])`	`TOUPPER([Products.CategoryName])`
TRANSFORM	Directly manipulate or create from scratch the report-level result set.	`TRANSFORM(script)`	`TRANSFORM(` `Dim [Odd] As Integer = 0` `IF (Integer([Odd]/2)*2 = [Odd])` `THEN [Sum: Quantity] = [Sum: Quantity] WRITERECORD` `ENDIF [Odd] = [Odd] + 1` `)`
TREND	Uses the least squares method to extrapolate values based on a linear trend.	`TREND(index_of_column_to_return, index_of_column_to_lookup, value_to_lookup, query)`	`TREND(1, 0, [Time.Month Seq Number],` `SELECT [Time.Month Seq Number], [OrderDate: Sum: Quantity]` `FROM [All] WHERE [Time.Month Seq Number] >= 1136 AND` `[Time.Month Seq Number] <= 1148)`
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')`	`TRIM([Products.CategoryName])` `[BevConstant] = TRIM('Beverages ')`

{} Positional Calculations


Function	Use	Syntax	Example
Absolute	Performs calculation based on specific value.	`[measure]/[measure] {[attribute] = value}`	`[OrderDate: Sum: Quantity]/[OrderDate: Sum: Quantity]` `{[Products.CategoryName] = 'Beverages'}`
Share	Relative measure value calculation.	`[measure]/[measure] {[attribute]}`	`[OrderDate: Sum: Quantity]/[OrderDate: Sum: Quantity]` `{[Products.CategoryName]}` How much each product makes up of the entire product category
Relative (Growth)	Relative measure comparison, such as month-over-month-growth.	`[measure]/[measure] {[time.attribute] +-=value}`	`[OrderDate: Sum: Quantity]/[OrderDate: Sum: Quantity]` `{[Time.Year Seq Number] -=1}-1`
Grand Totals	Relative total. In this case Total is a special column.	`[measure]/[measure] {[dimension.Total]}`	`[OrderDate: Sum: Quantity]/[OrderDate: Sum: Quantity]` `{[Products.Total]}`

Operators


Operator	Meaning	Syntax	Example
=	Equals	=	`[Products.CategoryName]='Beverages'`
<	Less than	<	`[Sum: Quantity]<3000`
>	Greater than	>	`[Sum: Quantity]>0`
<>	Not equals. Successful when the column on the left does not match the string value.	<>	`[Products.CategoryName]<>'Beverages'`
<=	Less than or equal to	<=	`[Sum: Quantity]<=500`
>=	Greater than or equal to	>=	`[Sum: Quantity]>=1000`
AND	Returns true if all filter conditions are met.	AND	`[OrderDate: Sum: Quantity] WHERE [Categories.CategoryName] ='Beverages' AND [Categories.CategoryName] = 'Seafood'`
OR	Returns true if either filter condition is met.	OR	`[OrderDate: Sum: Quantity] WHERE [Categories.CategoryName] ='Beverages' OR [Categories.CategoryName] = 'Seafood'`
LIKE	Searches for a specified pattern in a column.	LIKE	`[OrderDate: Sum: Quantity] WHERE [Categories.CategoryName] LIKE 'Bev%'`
NOT LIKE	Searches for the non-existence of a specified pattern in a column.	NOT LIKE	`[OrderDate: Sum: Quantity] WHERE [Categories.CategoryName] NOT LIKE 'Bev%'`
IS NULL	Condition is satisfied if the column value contains a null value, or an expression evaluates to null.	IS NULL	`[OrderDate: Sum: Quantity] WHERE [Time.Year] 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	`[OrderDate: Sum: Quantity] WHERE [Time.Year] IS NOT NULL`
+	Concatenation	+	`[Account.First Name] + ' ' + [Account.Last Name]`
%	Module	[dividend] % [divisor]	`9 % 4 = 1`

Built-in System Variables


Variable	Meaning	Use	Example
`USER`	Session variable that stores the user login for that specific session.	Use as a value in expressions. Not for use in ETL.	`asmith@birst.com`
`LoadDate`	Repository variable that stores the last load date.	Use as a value in expressions	`08/29/2015`
`LoadDateSFDC`	Repository variable that stores the load date and time of the last Salesforce.com load.	Use as a value in expressions	`2015-08-29T00:00:00-05.00`
`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	`2015-08-29T11:46:45-05:00`
`LoadCompletionTime`		Use as a value in expressions	`2015-08-29T11:46:45-05:00`