MDX Filter Problem

hi,

i using as 2000 foodmart 2000 db. problem when run below query getting error.

query 01

select  {[measures].[unit sales]} on columns,
            {[product].[all products]}  on rows
from
            [sales]
where
            [product].[all products].[drink]

“duplicate dimension across (independent) axes when calculating query axis”

but understood can use other slicing dimension where clause filter instead of product dimension, below query:

select      {[measures].[unit sales]} on columns,
                {[product].[all products]}  on rows
from
                [sales]
where       [customers].[all customers].[usa].[ca]       

this query works fine.

my point know 2000 not use same dimension in 2 axes, @ same time have requirement filter selected dimension same dimension's members. refer query 01.

for example, when run below query 266,773.00 unit sale all products.

select          {[measures].[unit sales]} on columns,
                    {[product].[all products]}  on rows
from
                    [sales]

ok, suppose have these figures on custom hierarchical grid control , not pivot table, can drill down , analyze product dimension. (just cube browser in analysis manager, have selected sales cube , product dimension on grid)

so when drill down all products, break down of products similar below example:

all product             266,773.00
drink                       24,597.00
food                      191,940.00
non – consumable     50, 236.00

but according my requirement have feature in grid filter levels of product dimension, apart slicer dimension.

so according suppose have selected drink level, when drill down drink member respective value 24,597 unit sale measure.

but product still  266,773. 00 , grid looks below..

all products  266, 773. 00
drink           24, 597, 00

i want product display 24,597, since have selected drink level.

all products  24, 597.00
drink           24, 597.00

any workaround highly appreciated.

summa.

when need described in as2000, best way put relevant members on axis (using visualtotals) , ignore clause.

i.e.

select  {[measures].[unit sales]} on columns,
            visualtotals({[product].[all products].[all product], [product].[all products].[drink]})  on rows
from
            [sales]

this, of course, applies when need felter dimension specified on axis.

SQL Server  >  SQL Server Analysis Services