Use As Navigates

Use the As Navigates feature 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.

Note: This feature must be enabled for the account. Contact your Insights administrator to enable it. After As Navigates is enabled for the account, you must also enable it on the Admin Modify Properties page.

Tip: As a best practice, the Push Down Expressions to Database feature should also be enabled if you need to filter reports when As Navigates is enabled.

When As Navigates is enabled, Visualizer allows the generated query to navigate a report for which the columns are not joined in the data model.

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_

Example of applying a filter to the report with As Navigates enabled:

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

This 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_