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 |
Non-Visual on rows and columns
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 |
Non-Visual on rows
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.
See Also