Dimensional Expressions

Dimensional expressions are a type of expression used for report expressions in Designer or in Visualizer. Dimensional expressions can be used to create an expression covering the aggregation that’s different than the grain of the main query. The syntax is similar to positional calculations but more general.

Important: The “Push Down Expressions to Database” feature supports all report expressions except those that include positional calculations and dimensional expressions. To use these, you must disable “Push Down Expressions to Database”. See Pre-Aggregate Report Expressions.

Use a left curly bracket and the at sign ({@) to begin and a right curly bracket (}) to end a dimensional expression.

For example, this is a new version of a classic share calculation using a dimensional expression:

SELECT [Products.Products],[Order_Date: Sum: Quantity]/[Order_Date: Sum: Quantity]{@[Products.Categories]='Tablets'}FROM [ALL]

Notice that in this case there is an at sign ({@) to denote a dimensional expression and it looks generally the same. The big difference is that the expression can be any expression, meaning it may contain columns outside of the dimensions used on the report. For example, if you have an expression based on Time and you add columns from the Product dimension to the main report, the expression will still work. Drilling on report columns outside of the dimensional expression will continue to work as well.

A common scenario for its use includes cumulative measures – showing the contribution of a measure across time. For example, a measure calculating Prior YTD could be expressed as the following where Quantity is summed quarterly on a report:

[Order_Date: Sum: Quantity]{@[Time.Quarter]<=CurrentValue([Time.Quarter]) AND [Time.Year]=CurrentValue([Time.Year])-1}

This dimensional expression will tie the summary results of the Quantity measure to the Quarter of the transaction, and sum Quantity up to the Quarter attribute on the report for the prior year.

For example if you run the following query in Query Admin:

SELECT USING OUTER JOIN [Time.Year],[Time.Quarter] , [Order_Date: Sum: Quantity], [Order_Date: Sum: Quantity]{@[Time.Quarter]<=CurrentValue([Time.Quarter]) AND [Time.Year]=CurrentValue([Time.Year])-1} FROM [ALL] ORDER BY [Time.Year] ASC

You see the following results:

CurrentValue Operator

The real power comes with the use of the CurrentValue operator. This allows you to compare the current value in the output with the values in the aggregation. If you wanted to calculate “year ago, year to date" for example, this aggregation used to cause issues with custom fiscal time. Now, using a dimensional expression, the following works:

SELECT [Time.Year],[Time.Year/Month],[Order_Date: Sum: Quantity],[Order_Date: Sum: Quantity]{@[Time.Month of Year]<=CurrentValue([Time.Month of Year]) AND [Time.Year]=CurrentValue([Time.Year])-1}FROM [ALL] ORDER BY [Time.Year/Month]

Dimensional expressions make it easier to handle all the arbitrary permutations of time and custom time (utilizing saved expressions as necessary). This calculation is basically creating a query saying to aggregate all months where the month number is less than the one in the current month and the year is previous. This also drills, as when you add any additional dimension columns to the parent query, the expression works.

Drilling and Level Specifiers

Dimensional expressions also allow for level specifiers. This means that you can actually specify different expressions to run depending on the level of the query. This allows you to use the same dimensional expression when drilling, even when different expressions are required for different levels on the drill path.

Consider the following query:

SELECT [Categories.CategoryName],[Time.Year/Month],[OrderDate: Sum: Quantity]{@[Time.Year/Month]<=CurrentValue([Time.Year/Month]) AND [Time.Year]=CurrentValue([Time.Year])} FROM [ALL] ORDER BY [Categories.CategoryName],[Time.Year/Month]

This query will return the year-to-date amount sold for each product category for each month. If a user drills on the Product category (that is, adds the Product column to the query to take it down to the product level) this query will still work as the dimension expression refers only to [Time.Year/Month] and [Time.Year].

However, if the user drills into the month, to take it down to the week or day (that is, they add a column at the week or the day level) this query will not return the year-to-date sales at that level because the expression doesn’t say anything about how to handle individual days.

At this level, we would need a new dimensional expression, one that looked something like:

SELECT [Categories.CategoryName],[Time.Year/Month],[Time.Date],[OrderDate: Sum: Quantity]{@[Time.Date]<=CurrentValue([Time.Date]) AND [Time.Year]=CurrentValue([Time.Year])} FROM [ALL] ORDER BY [Categories.CategoryName],[Time.Year/Month],[Time.Date]

However, now we have two different expressions. If you wanted to create a generic saved expression, this wouldn’t work. Level specifiers allow you to put both versions of the formula in an expression and specify when to use each, for example:

SELECT [Categories.CategoryName],[Time.Year/Month],[Time.Date],[OrderDate: Sum: Quantity]{@[Time.Year/Month]<=CurrentValue([Time.Year/Month]) And [Time.Year]=CurrentValue([Time.Year])}{@[Time.Date]:[Time.Date]<=CurrentValue([Time.Date])AND [Time.Year]=CurrentValue([Time.Year])} FROM [ALL] ORDER BY [Categories.CategoryName],[Time.Year/Month],[Time.Date]

In this example, {@[Time.Date]: to begin the expression specifies that this version is to be used whenever [Time.Date] is present in the query. Multiple level specifiers can be used together as in the following example:

SELECT [Categories.CategoryName],[Time.Year/Month],[Time.Date],[OrderDate: Sum: Quantity]{@[Time.Year/Month]<=CurrentValue([Time.Year/Month]) AND [Time.Year]=CurrentValue([Time.Year])}{@[Time.Date][Categories.CategoryName]:[Time.Date]<=CurrentValue([Time.Date]) And [Time.Year]=CurrentValue([Time.Year])} FROM [ALL] ORDER BY [Categories.CategoryName],[Time.Year/Month],[Time.Date]

In the example above, the second variant will only come into play if both [Time.Date] and [Categories.CategoryName] are in the query. If no level specifier is given, then that version is considered the default – the one to be used if no other level specifier exactly matches. If no default is provided and there are no matches, then the first version is used.

Filtering

There are two ways to perform filtering in dimensional expressions. Filtering can be done inside the dimension expression {@} using AND and OR as in the first example below or you can use a WHERE clause outside of the dimensional expression as in the second example below.

[Implementation Date: # Account ID]{@[Time.Year/Quarter]<=CurrentValue([Time.Year/Quarter])AND ([Account.Account Status]='Active' OR [Account.Account Status]='Closing' OR [Account.Account Status]='Terminated') AND [Account.Record Type ID]='012A0000000HQd4IAG'

[Implementation Date: # Account ID] {@[Time.Year/Quarter]<=CurrentValue([Time.Year/Quarter])} {@[Time.Year/Month]: [Time.Year/Month]<=CurrentValue([Time.Year/Month])} {@[Time.Date]: [Time.Date]<=CurrentValue([Time.Date])}WHERE ([Account.Account Status]='Active' OR [Account.Account Status]='Closing' OR [Account.Account Status]='Terminated') AND [Account.Record TypeID]='012A0000000HQd4IAG'

Measures

Dimensional expressions can also carry measures.

For example:

Select [Time.Year],[Time.Year/Month],[OrderDate: Sum: Quantity],[OrderDate: Sum: Quantity]{@[OrderDate: Sum: Freight]>CurrentValue([OrderDate: Sum: Freight]) and [Time.Year]=CurrentValue([Time.Year])}from [All] Order By [Time.Year/Month]

This query calculates, for each month, the sum of all other months where freight was greater than freight in the current month.

Important: Dimensional expressions are not supported for measure aggregations using count distinct (# Distinct) or average (Avg).