select
{[Measures].[Reseller Sales Amount] } on 0,[Business Type].members on 1
from
[Adventure Works]
where
[Category].members
You Get
| Reseller Sales Amount | |
| All Resellers | $80,450,596.98 |
| Specialty Bike Shop | $6,756,166.18 |
| Value Added Reseller | $34,967,517.33 |
| Warehouse | $38,726,913.48 |
Alright so this is the All Resellers total Value ($80,450,596.98)
Now lets fire the next MDX but this time with simple SUB CUBE
CREATE SUBCUBE [Adventure Works] AS
Select
{[Category].Accessories, [Category].Clothing} on 0,
{[Business Type].[Value Added Reseller], [Business Type].[Warehouse]}
on 1
from
[Adventure Works]
with
member
Measures.VisualSum As
([Category].Accessories,[Measures].[Reseller Sales Amount])+([Category].Clothing,[Measures].[Reseller Sales Amount])
select
{[Measures].[Reseller Sales Amount] ,Measures.VisualSum} on 0,
[Business Type].
members on 1
from
[Adventure Works]
where
[Category].members
| Reseller Sales Amount | VisualSum | |
| All Resellers | $2,031,079.39 | $2,031,079.39 |
| Value Added Reseller | $767,388.52 | $767,388.52 |
| Warehouse | $1,263,690.86 | $1,263,690.86 |
You would see that Visual total is eaqul to Reseller total which is much less than the actual total. Why does this happen?
This is because when you try to do a total on SUB CUBE with considers only VISUAL TOTALS , the other values ( of Reseller Sales here) will not be aggregated. Sometimes this is what is required in a business scenarion but many times you will find your customers want to see total figure . In this case the query below will show the SUB CUBE total and the NON VISUAL total . For this we will create a sub cube with NON VISUAL mode.(this is available from AS 2008)
CREATE
SUBCUBE [Adventure Works] AS
NON VISUAL (Select {[Category].Accessories, [Category].Clothing} on 0,
{[Business Type].[Value Added Reseller], [Business Type].[Warehouse]} on 1
from [Adventure Works])
select [Category].members on 0,
[Business Type].members on 1
from
[Adventure Works]
where
[Measures].[Reseller Sales Amount]
with
member
Measures.VisualSum As
([Category].Accessories,[Measures].[Reseller Sales Amount])+([Category].Clothing,[Measures].[Reseller Sales Amount])
select
{[Measures].[Reseller Sales Amount] ,Measures.VisualSum} on 0,
[Business Type].
members on 1
from [Adventure Works]
where
[Category].members
this is what you get
| Reseller Sales Amount | VisualSum | |
| All Resellers | $80,450,596.98 | $2,349,138.77 |
| Value Added Reseller | $34,967,517.33 | $767,388.52 |
| Warehouse | $38,726,913.48 | $1,263,690.86 |
See that the NON VISUAL total $80,450,596.98 is without considering the sub cube and this is eaqul to the first total that we had , which was on the complete cube , but the Visual total is same as the Total we had for the SUB CUBE in Visual Mode.
I have tried my best to explain that Visual and Nonb Visual totals are how can Create SUB CUBE be used in both modes. For more do go to
http://msdn.microsoft.com/en-us/library/ms144916.aspx
Code Perfect 