Advanced MDX - Microsoft SQL Server 2008 Analysis Services - page 183

Let's go back to our query that we want to execute in the context of a subcube that
restricts our space to just two members on the third level— Q2 and Q3 of the years 1998 .
You can see the result of our query in Figure 11.29. The result has a visual totals value for
the members 1998 and ALL :

SELECT {[Time].[Time].[All], [Time].[Time].[Year].members,
[Time].[Time].[Quarter].members} ON COLUMNS FROM
(SELECT {[Time].[Time].[Quarter].&[Q2]&[1998],[Time].[Time].[Quarter].&[Q3]&[1998]}
ON COLUMNS FROM [Warehouse and Sales])
WHERE [Measures].[Unit Sales]

+

All 1998 Q2 Q3

275157 275157 135745 139412

FIGURE 11.29

The cells for the members 1998 and ALL show visual totals of Q2 and Q3.

When designing Analysis Services 2005, we thought that applying visual totals on
SubSelects produces the most natural results—results you want to see for your query when
it is executed in a context of a SubSelect. However, practice showed that often customers
want to see those values without applying visual totals. For example, you want to mini-
mize size of your result and see sales values only for Q2 and Q3, but you still need to see
actual value of sales in the whole year. To provide this functionality, Analysis Services
2008 supports NON VISUAL keyword for SubSelects and subcube expression, which turns off
visual total behavior. For example, we can add the NON VISUAL keyword to our preceding
query and it will return value for member All and 1998 as the sum of all of their children,
as shown in Figure 11.30.

SELECT {[Time].[Time].[All], [Time].[Time].[Year].members,
[Time].[Time].[Quarter].members} ON COLUMNS FROM
NON VISUAL (SELECT
{[Time].[Time].[Quarter].&[Q2]&[1998],[Time].[Time].[Quarter].&[Q3]&[1998]}
ON COLUMNS FROM [Warehouse and Sales])
WHERE [Measures].[Unit Sales]