Varying Results with Extract Function

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I have a problem when using the extract function:

If I use a multiple dimension nonemptycrossjoin to extract the Customer
Chain dimension using the following
set
[rowfilter] as
'extract({nonemptycrossjoin({[Time_Partial].[All
Time_Partial].[2006].[Qtr2 - 2006].[Period 06 - 2006].[Week 26 -
2006]},{[Customer].[Trade].[sMajTrade].&[26]},{[Customer].[Type].[sType].&[VHVW]},
{[Customer].[Chain].[sState].Members})},[Customer].[Chain])'

I get a completely different set of information than if I do not use
the extract function at all. However, I need two dimension display with
Sales info on my columns and Customers by State on my rows with the
specific criteria.

However, if I put must of my criteria in my Where Clause and only have
two dimension and use the extract, I always get the correct results.

Why does extract work in this manner?



////////////////////////////////////////////////////////////////////////////////////////
//Query One that gives me the correct results
//////////////////////////////////////////////////////////////////////////////////////////
with
set [main]
as
'{[Measures].[cGrossSales],
[Measures].[cPYGrossSales],
[Measures].[pGrossSalesGrowth%],
[Measures].[cCYPtdGrossSales],
[Measures].[cPYPtdGrossSales],
[Measures].[pPtdGrossSalesGrowth%],
[Measures].[cCYQtdGrossSales],
[Measures].[cPYQtdGrossSales],
[Measures].[pQtdGrossSalesGrowth%],
[Measures].[cCYYtdGrossSales],
[Measures].[cPYYtdGrossSales],
[Measures].[pYtdGrossSalesGrowth%]}'



set
[rowfilter] as
'order({extract({nonemptycrossjoin({[Customer].[Type].[sType].&[VHVW]},
{[Customer].[Chain].[sState].Members})}, [Customer].[Chain])},
[Customer].[Chain], BASC) '
member [Customer].[Chain].[Total] as 'sum({rowfilter})'

select
[main]
on columns,
{[rowfilter], [Customer].[Chain].[Total]}
on rows

FROM CusItmWk_Dev
WHERE([Customer].[Trade].[sMajTrade].&[26], [Time_Partial].[All
Time_Partial].[2006].[Qtr2 - 2006].[Period 06 - 2006].[Week 26 - 2006])



////////////////////////////////////////////////////////////////////////////////////////
//Query Two that gives me different results
//////////////////////////////////////////////////////////////////////////////////////////

with
set [main]
as
'{[Measures].[cGrossSales],
[Measures].[cPYGrossSales],
[Measures].[pGrossSalesGrowth%],
[Measures].[cCYPtdGrossSales],
[Measures].[cPYPtdGrossSales],
[Measures].[pPtdGrossSalesGrowth%],
[Measures].[cCYQtdGrossSales],
[Measures].[cPYQtdGrossSales],
[Measures].[pQtdGrossSalesGrowth%],
[Measures].[cCYYtdGrossSales],
[Measures].[cPYYtdGrossSales],
[Measures].[pYtdGrossSalesGrowth%]}'



set
[rowfilter] as
'extract({nonemptycrossjoin({[Time_Partial].[All
Time_Partial].[2006].[Qtr2 - 2006].[Period 06 - 2006].[Week 26 -
2006]},{[Customer].[Trade].[sMajTrade].&[26]},{[Customer].[Type].[sType].&[VHVW]},
{[Customer].[Chain].[sState].Members})},[Customer].[Chain])'


select
[main]
on columns,
[rowfilter]
on rows

FROM CusItmWk_Dev


////////////////////////////////////////////////////////////////////////////////////////

Ignore the Order function.

.

Relevant Pages