Export (0) Print
Expand All

Visual Totals and Non Visual Totals

Visual Totals are totals at the end of a column or row that add up all of the items visible in the column or row. This is the default behavior for most tables when displayed. However, there are times when the user wants to display only certain columns in a table but keep the totals for the entire row, including those that are not displayed. These are called Non Visual Totals, because the total comes from both the visible and non-visible values.

The following scenario demonstrates the behavior of Non Visual totals. The first step shows the default behavior of Visual Totals.

The following example is a query of [Adventure Works] to obtain [Reseller Sales Amount] figures in a table where the product categories are the columns and the reseller business types are the rows. Note that totals are given for both products and resellers when the following SELECT statement is issued:

select [Category].members on 0,

[Business Type].members on 1

from [Adventure Works]

where [Measures].[Reseller Sales Amount]

Produces the following results:

 

All Products

Accessories

Bikes

Clothing

Components

All Resellers

$80,450,596.98

$571,297.93

$66,302,381.56

$1,777,840.84

$11,799,076.66

Specialty Bike Shop

$6,756,166.18

$65,125.48

$6,080,117.73

$252,933.91

$357,989.07

Value Added Reseller

$34,967,517.33

$175,002.81

$30,892,354.33

$592,385.71

$3,307,774.48

Warehouse

$38,726,913.48

$331,169.64

$29,329,909.50

$932,521.23

$8,133,313.11

To produce a table with data only for the Accessories and Clothing products, the Value Added Reseller and Warehouse resellers, yet keeping the overall totals could be written as follows using NON VISUAL:

select [Category].members on 0,

[Business Type].members on 1

from NON VISUAL (Select {[Category].Accessories, [Category].Clothing} on 0,

{[Business Type].[Value Added Reseller], [Business Type].[Warehouse]} on 1

from [Adventure Works])

where [Measures].[Reseller Sales Amount]

Produces the following results:

 

All Products

Accessories

Clothing

All Resellers

$80,450,596.98

$571,297.93

$1,777,840.84

Value Added Reseller

$34,967,517.33

$175,002.81

$592,385.71

Warehouse

$38,726,913.48

$331,169.64

$932,521.23

To produce a table that visually totals the columns but for row totals brings the true total of all [Category], the following query should be issued:

select [Category].members on 0,

[Business Type].members on 1

from NON VISUAL (Select {[Category].Accessories, [Category].Clothing} on 0

from ( Select {[Business Type].[Value Added Reseller], [Business Type].[Warehouse]} on 0

from [Adventure Works])

)

where [Measures].[Reseller Sales Amount]

Note how NON VISUAL is only applied to [Category].

The above query produces the following results:

 

All Products

Accessories

Clothing

All Resellers

$73,694,430.80

$506,172.45

$1,524,906.93

Value Added Reseller

$34,967,517.33

$175,002.81

$592,385.71

Warehouse

$38,726,913.48

$331,169.64

$932,521.23

When compared with the previous results, you can observe that the [All Resellers] row now adds up to the displayed values for [Value Added Reseller] and [Warehouse] but that the [All Products] column shows the total value for all products, including those not displayed.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft