As Navigates

The "As Navigates" feature is an enhancement for Visualizer (and Designer) reporting that enables users to create a report that includes a measure in a logical query that only has grains available on a subset of dimensions that are included in the query.

This feature must be enabled for the account. To use the "As Navigates" feature, please contact your Birst representative to have it turned on for your account. Once enabled for the account, you will need to enable this feature for at the space level using the Admin Modify Properties page as follows:

Tip! As a best practice, the "Push Down Expressions to Database "feature should also be enabled if there is a need for filtering reports when "As Navigates" is enabled.

When "As Navigates" is enabled, Designer and Visualizer reports will allow the generated query to navigate a report for which the columns are not joined in the data model.

In the following report the chart uses the

SELECT TOP 100 USING OUTER JOIN [OrderDate: Sum: Quantity] 'COL0' , [Shippers.CompanyName] 'COL1' FROM [ALL]

The report generates the following SQL:

SELECT TOP 100 SUM(DQT0_D.COL00_) AS 'COL0',DQT1_D.COL11_ AS 'COL1'

FROM

(SELECT SUM(CAST(DW_SF_CATEGORIES_DAY_EMPLOYEES_ORDER_DETAILS_PRODUCTS0_.Quantity$ AS BIGINT)) AS 'COL00_'

FROM S_N08365898_f657_4049_af7a_1fd454557f95.DW_SF_CATEGORIES_DAY_EMPLOYEES_ORDER_DETAILS_PRODUCTS DW_SF_CATEGORIES_DAY_EMPLOYEES_ORDER_DETAILS_PRODUCTS0_) DQT0_D,

(SELECT DW_DM_SHIPPERS_SHIPPERS0_.CompanyName$ AS 'COL11_'

FROM S_N08365898_f657_4049_af7a_1fd454557f95.DW_DM_SHIPPERS_SHIPPERS DW_DM_SHIPPERS_SHIPPERS0_

GROUP BY DW_DM_SHIPPERS_SHIPPERS0_.CompanyName$) DQT1_D

GROUP BY DQT1_D.COL11_

And here is an example when applying a filter to the report with "As Navigates" feature enabled

SELECT TOP 100 USING OUTER JOIN [OrderDate: Sum: Quantity] 'COL0' , [Shippers.CompanyName] 'COL1' FROM [ALL] WHERE ( ( [Shippers.CompanyName]='Federal Shipping' ) )

Which generates the following navigable SQL.

SQL generated consists of multiple independent queries with filters applied to the appropriate sub-queries

SELECT TOP 100 SUM(ISNULL(DQT0_D.COL10_,CAST(0 AS FLOAT)) / CAST(DQT1_D.__ShipperID3_ AS FLOAT)) AS 'COL0',SUM(DQT0_D.COL10_) AS 'COL1',DQT1_D.COL22_ AS 'COL2'

FROM

(SELECT SUM(CAST(DW_SF_CATEGORIES_DAY_EMPLOYEES_ORDER_DETAILS_PRODUCTS0_.Quantity$ AS BIGINT)) AS 'COL10_',SUM(CAST(DW_SF_CATEGORIES_DAY_EMPLOYEES_ORDER_DETAILS_PRODUCTS0_.Quantity$ AS BIGINT)) AS 'OrderDate__Sum__Quantity1_'

FROM S_N08365898_f657_4049_af7a_1fd454557f95.DW_SF_CATEGORIES_DAY_EMPLOYEES_ORDER_DETAILS_PRODUCTS DW_SF_CATEGORIES_DAY_EMPLOYEES_ORDER_DETAILS_PRODUCTS0_) DQT0_D,

(SELECT DW_DM_SHIPPERS_SHIPPERS1_.CompanyName$ AS 'COL22_',COUNT_BIG(DW_SF_DAY_SHIPPERS0_.ShipperID$) AS '__ShipperID3_'

FROM S_N08365898_f657_4049_af7a_1fd454557f95.DW_SF_DAY_SHIPPERS DW_SF_DAY_SHIPPERS0_

INNER JOIN S_N08365898_f657_4049_af7a_1fd454557f95.DW_DM_SHIPPERS_SHIPPERS DW_DM_SHIPPERS_SHIPPERS1_ ON DW_SF_DAY_SHIPPERS0_.Shippers$Shippers_1598344664$=DW_DM_SHIPPERS_SHIPPERS1_.Shippers_1598344664$

WHERE DW_DM_SHIPPERS_SHIPPERS1_.CompanyName$=N'Federal Shipping'

GROUP BY DW_DM_SHIPPERS_SHIPPERS1_.CompanyName$) DQT1_D

GROUP BY DQT1_D.COL22_