Hi There,
I have used the SELECT NON EMPTY ON ROWS or COLUMNS, but
this only suppress empty rows or columns. Zeroes rows or
columns are still returned.

Is there a MDX function to exclude rows or columns if it
has all zeroes?

Thanks,
Dao.

Not exactly. However you can use the Filter function to filter a set, like
this foodmart example...

Filter( [Store Name].Members, [Store Sales] <>0 )

tom @ the domain below
www.tomchester.net

"Dao Tring" <dao.trinh (AT) solution6 (DOT) com> wrote

Appreciate for a quick reply. It helps.
The filter does help me to suppress zeroes rows. I think I
need to check the column cellset to exclude zeroes columns.
Thanks,
Dao

"Dao Tring" <dao.trinh (AT) solution6 (DOT) com> wrote

One technique is to put calculated cell over entire cube which converts
zeros to NULLs, i.e.

WITH CELL CALULATION ZerosToNULL FOR '(Measures.ALLMEMBERS)' AS
'IIF(Measures.CurrentMember.Value = 0, NULL,
CalculationPassValue(Measures.CurrentMember, -1, RELATIVE)'
SELECT NON EMPTY ...

--
==================================================
Mosha Pasumansky - www.mosha.com/msolap
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==================================================

Hi Mosha,
Thanks for your help but I have problem execute the MDX
WITH CELL CALCULATION ZerosToNULL
FOR '(Measures.ALLMEMBERS)' AS
'IIF(Measures.CurrentMember = 0, NULL,CalculationPassValue
(Measures.CurrentMember, -1, RELATIVE)'
SELECT NON EMPTY { [Measures].[Net Fees Earned Ytd],
[Measures].[Bud Net Fees Earned Ytd], [Measures].[Gross
Contribution Mtd] } ON COLUMNS,
{ Descendants ( [Matter Resp Partner].[All Partner].
[Accounting Departments], 1 , SELF_AND_BEFORE) } ON ROWS
From [Matter Responsible Partner Performance] WHERE
([Currency Type].[Global : USD], [Industry].[All
Industry], [Matter].[All Matter], [Period].[All Period].
[2003].[2003 - 03])

The error is "unable open cellset - Formula error,
expected end of expression Formula member name ZerosToNULL
is not valid.

Thanks,
Dao.

"Dao Trinh" <dao.trinh (AT) solution6 (DOT) com> wrote

I think I forgot to close one parethesis - here is the updated version

WITH CELL CALULATION ZerosToNULL FOR '(Measures.ALLMEMBERS)' AS
'IIF(Measures.CurrentMember.Value = 0, NULL,
CalculationPassValue(Measures.CurrentMember, -1, RELATIVE))'


--
==================================================
Mosha Pasumansky - www.mosha.com/msolap
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==================================================

Hi Mosha,
It still complains about the Value, ie.
Measures.CurrentMember.^Value^ is an inavalid token.

If I remove the value I can execute the MDX, but it
returns #ERR

Thanks,
Dao.

"Dao Trinh" <dao.trinh (AT) solution6 (DOT) com> wrote

OK - here is the one that worked for me

WITH CELL CALCULATION ZerosToNULL FOR '(Measures.ALLMEMBERS)' AS
'IIF(CalculationPassValue(Measures.CurrentMember, -1, RELATIVE) = 0, NULL,
CalculationPassValue(Measures.CurrentMember, -1, RELATIVE))'

--
==================================================
Mosha Pasumansky - www.mosha.com/msolap
Development Lead in the Analysis Server team
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==================================================

Thanks muchly for your help and your patience. It works!
Regards,
Dao.