Result Sets and Display Operations

There is sometimes a difference between the results that must be analyzed and the results that should be displayed. BQL separates those two concepts. You might want to analyze data and return a subset using display operations. These operations control the “display” of the results generated from the subset that has been specified.

In short the difference between regular data operations and display operations is this:
  • Data operations are carried out at the database level and return results that meet the criteria.
  • Display operations are carried out after the database returns a result set for the report query.

Display operations include DISPLAY WHERE, DISPLAY BY, RANK, PTILE (percentile calculations) and expressions.

Filtering Results - DISPLAY WHERE

One basic display operation is the display filter, DISPLAY WHERE. Similar to a normal hierarchy column or measure WHERE filter, you can filter on given columns:

DISPLAY WHERE [Products.Categories]='Desktops'

DISPLAY WHERE [Order_Date: Sum: Quantity]>=100000

Other than the column names specified, there is no difference between a display filter on measure columns and dimension columns. This is because the grain of the result set is already determined by the definition of dimension columns in the query. Hence, for a display filter to work properly, the column must be included in the SELECT portion of the logical query. Display filters might seem redundant, but they are quite powerful when used in conjunction with other display functions. Additionally, by controlling and leveraging caching, display filters can result in significant performance improvements.

You can combine display filters using AND and OR.

SELECT [Products.Categories], [Products.Products], [Time.Year/Quarter], [Order_Date: Sum: Revenue] FROM [ALL] DISPLAY WHERE [Time.Year/Quarter]='2014/Q1' AND ([Order_Date: Sum: Revenue]>=10000000 OR [Products.Categories]<>'Desktop') ORDER BY [Order_Date: Sum: Revenue] DESC

Display Ordering - DISPLAY BY

Display ordering is like ORDER BY, it sorts by attribute orderings or measure orderings. However, DISPLAY BY sorting is applied after the result set is generated and any other display operations are performed.

DISPLAY BY [dimension.attribute]

DISPLAY BY [date_type: aggregation: measure]

Ranking Calculations - RANK

In addition to calculating various business measurements, it is often useful to understand how those measurements relate to an overall population. You can rank a measure based on its value versus the result set created as an extension of specifying a measure. For example, this query lists each product category and its rank in terms of revenue:

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

Table 1. Query Results
Categories RANK([Order_Date: Sum: Revenue])
Desktops 3
Mobile Phones 4
MP3 Players 5
Notebooks 2
Tablets 1

In this example the tablets have the highest revenue therefore the rank is 1.

Using RANK with or without a Display Filter

Display filters can affect the results of a RANK operation. For example, without a display filter:

SELECT [Products.Categories], RANK([Order_Date: Sum: Revenue]) FROM [ALL] WHERE [Time.Year]=2014

Table 2. Query Results
Categories RANK([Order_Date: Sum: Revenue])
Desktops 4
MP3 Players 5
Mobile Phones 3
Notebooks 2
Tablets 1

The query without a display filter creates a result set that includes all product categories for the year 2014 and then ranks each product category in terms of unit sales.

With a display filter, the sort column must be included in the SELECT.

For example:

SELECT [Time.Year], [Products.Categories], RANK([Order_Date: Sum: Revenue]) FROM [ALL] DISPLAY WHERE [Time.Year]=2014

Table 3. Query Results
Year (See note below) Categories RANK([Order_Date: Sum: Revenue])
2,014 Desktops 13
2,014 Mobile Phones 11
2,014 MP3 Players 20
2,014 Notebooks 10
2,014 Tablets 2
Note: The Query Admin > Queries tab displays Time.Year as integers.

The query with a display filter considers the revenue for all product categories for all years and ranks the unit sales for those products by year across all time, and presents only the results for 2014.

The second query gives the rank of 2014 sales for the product category across all time, and the first one ranks only within 2014.

Using RANK with Display Ordering

You can sort or order results based on the rank, using display ordering.

SELECT [Products.Categories], RANK([Order_Date: Sum: Revenue]) 'Revenue Rank' FROM [ALL] WHERE [Time.Year]='2014' DISPLAY BY [Revenue Rank] ASC

Table 4. Query Results
Categories Revenue Rank
Tablets 1
Notebooks 2
Mobile Phones 3
Desktops 4
MP3 Players 5

The display order is applied to the Revenue Rank column.

Percentile Ranking Calculations - PTILE

PTILE functions are similar to RANK, except they return the percentile rank of a value (rank vs. size of population).

SELECT [Products.Categories], PTILE([Order_Date: Sum: Revenue]) FROM [ALL] WHERE [Time.Year]=2014
Table 5. Query Results
Categories PTILE([Order_Date: Sum: Revenue])
Desktops 0.2
Mobile Phones 0.4
MP3 Players 0
Notebooks 0.6
Tablets 0.8

As with RANK, you can use DISPLAY WHERE and DISPLAY ORDER with PTILE calculations. Remember to create a column name so that the display filter column is in the SELECT column list.

SELECT [Products.Categories], PTILE([Order_Date: Sum: Revenue]) 'Percentile Revenue' FROM [ALL] WHERE [Time.Year]=2014 DISPLAY BY [Percentile Revenue] ASC

Table 6. Query Results
Categories Percentage Revenue
MP3 Players 0
Desktops 0.2
Mobile Phones 0.4
Notebooks 0.6
Tablets 0.8

Ranks or Percentile Ranks with Breaking Attributes

If you want to calculate ranks or percentiles at a finer grain, you can group by some other result in the query. For example, if you wanted to rank products within each year and show two years:

SELECT [Time.Year], [Products.Categories], RANK([Order_Date: Sum: Revenue] BY [Time.Year]) 'Revenue Rank' FROM [ALL] WHERE [Time.Year]=2013 OR [Time.Year]=2014 DISPLAY BY [Time.Year] DESC, [Revenue Rank] ASC

Table 7. Query Results
Year (See note below) Categories Revenue Rank
2,014 Tablets 1
2,014 Notebooks 2
2,014 Mobile Phones 3
2,014 Desktops 4
2,014 MP3 Players 5
2,013 Tablets 1
2,013 Notebooks 2
2,013 Mobile Phones 3
2,013 Desktops 4
2,013 MP3 Players 6
Note: The Query Admin > Queries tab displays Time.Year as integers.

You could also use a display filter to narrow down the results (similar to TOP). For example:

SELECT [Time.Year], [Products.Categories], RANK([Order_Date: Sum: Revenue] BY [Time.Year]) 'Revenue Rank' FROM [ALL] WHERE [Time.Year]=2013 OR [Time.Year]=2014 DISPLAY WHERE [Sales Rank]<=3 DISPLAY BY [Time.Year] DESC, [Revenue Rank] ASC

Table 8. Query Results
Year Categories Revenue Rank
2,014 Tablets 1
2,014 Notebooks 2
2,014 Mobile Phones 3
2,013 Tablets 1
2,013 Notebooks 2
2,013 Mobile Phones 3

Nested Ranks and Percentiles

BQL also supports nested RANK and PTILE calculations to do ranks of rank calculations. For example:

RANK(RANK([Order_Date: Avg: UnitPrice]+10)+RANK([Order_Date: Sum: Quantity]))

RANK(RANK([Order_Date: Avg: UnitPrice]+10)+RANK([Order_Date: Sum: Quantity BY [Products.CategoryName]]))