Advanced BQL commands

DATEDIFF

Use the DATEDIFF function to determine the difference between two Date or two Datetime attributes.

DATEDIFF(YEAR|QUARTER|MONTH|WEEK|DAY, start_date_attribute, end_date_attribute)

DATEDIFF(YEAR|QUARTER|MONTH|WEEK|DAY|HOUR|MINUTE|SECOND, start_datetime_attribute, end_datetime_attribute)

For example to get the difference in years between two datetime columns, Birthday and Hire date:

FIND

FIND works the same way as LOOKUPVALUE, with the exception that the lookup value does not have to have an exact match. If the match is exact then that row is returned, otherwise, the first row that is greater than the lookup value is used:

FIND(index_of_column_ to_lookup, value_to_lookup, index_of_column_to_return, BQL_query)

FORMAT

The BQL FORMAT function formats a measure or date attribute to a specified string format.

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

In a SELECT statement, surround the FORMAT function in parenthesis.

SELECT (FORMAT([Orders.Order_Date], 'EEEE, d MMM yyyy HH:mm:ss Z')) FROM [All]

The FORMAT function returns a varchar.

For example, without formatting:

SELECT [Order_Date: Sum: Quantity] FROM [All]

Returns: 944,592

With formatting:

SELECT (FORMAT([Order_Date: Sum: Quantity], '##,###,##0.00')) FROM [All]

Returns: 944,592.00

FUNCTION and FUNCTIONLOOKUP

FUNCTION and FUNCTIONLOOKUP run a script that does a calculation over the result set and returns a single value for a column on a row.

You declare a return data type (the type of the function) and provide both an ETL script and a BQL query. The script is executed for each row of the result set. The variable [Result] is populated with the return value.

FUNCTION(return_data_type,{parameters}, ETL_script, BQL_query)

FUNCTIONLOOKUP adds a lookup expression when iterating over a result set:

FUNCTIONLOOKUP(return_data_type, lookup_index, lookup_expression, script, BQL_query)

GETPROMPTVALUE

GETPROMPTVALUE syntax uses either a column filter (called a prompt in Original Dashboards) or a parameter filter.

GETPROMPTVALUE('filter_column_name|parameter_name', 'optional_default_value')

GETPROMPTFILTER

Use GETPROMPTFILTER to pass a filter (prompt) name and its user-selected value or values from a dashboard into an expression. GETPROMPTFILTERS passes all filter names and values for that specific dashboard.

[measure] WHERE GETPROMPTFILTER('filter_column_name')...

GETPROMPTFILTERS is also useful in situations where the set of filters may change, and the measure expression would not have to change.

[measure] WHERE GETPROMPTFILTERS()...

For example:

SELECT [Order_Date: Sum: Quantity] WHERE GETPROMPTFILTER('Time.Year')...
SELECT [Order_Date: Sum: Quantity] WHERE GETPROMPTFILTERS()...

LET

LET allows you to reuse logic in complex expressions. You can declare expression-level variables using the DIM statement, initialize them, and reuse them.

LET(variable_declarations, expression)

The following example creates two variables, [AvgUnitPrice] and [TotalQuantity]. Each is initialized with a LOOKUPVALUE expression. The variables are then used in a resulting expression. The lookup expressions only need to be declared once, instead of repeated multiple times in the IIF block.

LET(
DIM [AvgUnitPrice] AS FLOAT = LOOKUPVALUE(0,[Products.CategoryName],1,SELECT [Products.CategoryName],[OrderDate: Avg: UnitPrice] FROM [All])
DIM [TotalQuantity] AS FLOAT = LOOKUPVALUE(0,[Products.CategoryName],1,SELECT [Products.CategoryName],[OrderDate: Sum: Quantity] FROM [All]),
IIF([AvgUnitPrice]<25,25,[AvgUnitPrice])*[TotalQuantity])

LOOKUPROW

LOOKUPROW looks up a value in a row of another result set using a specific row index, and returns the value in a specified column in that row.

LOOKUPROW(index_of_row_to_return, index_of_column_to_return, optional_parameter, optional_parameter,... , BQL_query)

Optional parameters can be expressions, constants, columns from the main query, or a combination. Using parameters, the lookup query can depend on expressions derived from the main query itself. This technique allows multi-pass calculations.

LOOKUPVALIE

LOOKUPVALUE looks up a value in a row of another result set by the value of another column in the same row:

LOOKUPVALUE(column_number_from_the_query, [attribute_in_the_current_query|measure_in_the_current_query], column_number_of_the_new_query, optional_parameter, optional_parameter... , SELECT [attribute|measure],[attribute|measure]... FROM [ALL])

Optional parameters can be expressions, constants, columns from the main query, or a combination. Using parameters, the lookup query can depend on expressions derived from the main query itself. This technique allows multi-pass calculations.

MEDIAN

MEDIAN supports breaking attributes that instruct the query engine to re-calculate the median based on values changing for a particular attribute.

MEDIAN([measure] BY [attribute])

For example:

SELECT [Products.Categories], MEDIAN([Order_Date: Sum: Revenue] BY [Products.Categories]) FROM [ALL]

NOW and NOWDATE

NOW returns the current date and time. The format of the return value is:

For example, this query in Query Admin:

Generates this response:

SELECT DW_DM_TIME_DAY0_.Month_of_Year$ AS 'Month of Year'
FROM dbo.DW_DM_TIME_DAY DW_DM_TIME_DAY0_
WHERE DW_DM_TIME_DAY0_.Date$='2016-04-19 15:24:25'
GROUP BY DW_DM_TIME_DAY0_.Month_of_Year$

NOWDATE returns the current date at midnight. The format of the return value is:

For example, this query in Query Admin:

Generates this response:

SELECT DW_DM_TIME_DAY0_.Month_of_Year$ AS 'Month of Year'
FROM dbo.DW_DM_TIME_DAY DW_DM_TIME_DAY0_
WHERE DW_DM_TIME_DAY0_.Date$='2016-04-19 12:00:00'
GROUP BY DW_DM_TIME_DAY0_.Month_of_Year$

SPARSE

The SPARSE function allows you to show dimension column values for which there is no fact data. SPARSE is a middle tier function which enables it to be used with OUTER JOINS and to be used at the database level.

SPARSE([dimension])

SPARSE works with multiple dimensions in a query. For example:

SELECT USING OUTER JOIN [Products.ProductName] 'F8',[OrderDate: Sum: Quantity] 'F1',[Time.Year/Quarter],SPARSE([Time.Year/Quarter]) 'F5' FROM [ALL] WHERE ([Products.ProductName]=Longlife Tofu OR [Products.ProductName]=Chocolade) AND [Time.Year/Quarter]>=1993 AND [Time.Year/Quarter]<=1997 ORDER BY [Time.Year/Quarter]  ASC

STAT

STAT generates aggregated statistics based on another result set:

STAT([aggregation], index_of_column_to_aggregate, optional_parameter, optional_parameter... , BQL_query)

Allowable aggregations include: MEDIAN, AVG, MIN, MAX, SUM, COUNT, COUNTDISTINCT, and STDDEV.

Optional parameters can be expressions, constants, columns from the main query, or a combination. Using parameters, the lookup query can depend on expressions derived from the main query itself. This technique allows multi-pass calculations.

Reference each parameter expression in the lookup query using %index syntax. The first parameter is numbered %0, the second %1, etc.

TRANSFORM

Report-level transformations provide a way of directly manipulating or even creating from scratch the report-level result set. TRANSFORM operates row-by-row over the original result set from the query, using WRITERECORD to output new rows as results. TRANSFORM functions just like an ETL Services script. In essence, the entire result set is re-written as a result of the TRANSFORM statement. All the input and output columns are identical to those from the original result set.

TRANSFORM(script)

This example is a very basic transform that writes out odd row numbers:

TRANSFORM(

DIM [Odd] AS INTEGER = 0

IF (Integer([Odd]/2)*2 = [Odd]) THEN

       [Sum: Quantity]=[Sum: Quantity]

       WriteRecord

END IF

[Odd] = [Odd] + 1

)

TREND

The TREND function uses the least squares method to extrapolate values based on a linear trend.

TREND(index_of_column_ to_return, index_of_column_to_look_up, value_to_look_up, BQL_query)

For example, to get the linear trend of quantity over a period of time:

TREND(1,0,[Time.Month Seq Number], SELECT [Time.Month Seq Number], [Order_Date: Sum: Quantity]FROM [ALL] WHERE [Time.Month Seq Number] >= 1330 AND [Time.Month Seq Number] <= 1342)