The following is a subset of an MDX expression I am struggling with.

WITH
MEMBER [Employee].[Employee Department].[Total]
AS '[Employee].[Employee Department].[Department Name].&[Sales]'
MEMBER [Product].[Product Model Categories].[Total]
AS '[Product].[Product Model Categories].[Category].&[1]'

SET [Dicer Filtered Set]
AS '{Descendants ([Employee].[Employee Department].[Department
Name].&[Sales],[Employee].[Employee Department].[Title],SELF)}'
SET [Crossjoin Filtered Set]
AS '{Descendants([Product].[Product Model
Categories].[Category].&[1],[Product].[Product Model
Categories].[Subcategory],SELF)}'

SELECT
{
[Measures].[Reseller Sales Amount]
}

ON COLUMNS,
{
CrossJoin(
{[Dicer Filtered Set]},
{[Crossjoin Filtered Set],[Product].[Product Model Categories].[Total]}
)
}
ON ROWS
FROM [Channel Sales]


What I really want is

....
NonemptyCrossJoin(
{[Dicer Filtered Set]},
{[Crossjoin Filtered Set],[Product].[Product Model Categories].[Total]}
....

But when so modifed, the query does not return the subtotal rows.


--------------------
Chris Harrington
Active Interface, Inc.
http://www.activeinterface.com

NonEmptyCrossjoin is a depreciated function in AS 2005 because it does
strange things like this.

Try using the NonEmpty() function, it should do the trick.

NonEmpty(CrossJoin(
{[Dicer Filtered Set]},
{[Crossjoin Filtered Set],[Product].[Product Model
Categories].[Total]}))

--
Regards
Darren Gosbell [MVP]
Blog: http://geekswithblogs.net/darrengosbell

*** Sent via Developersdex http://www.developersdex.com ***

Thanks Darren,

I had found (after posting) that NonEmpty(CrossJoin()) worked - but was not
sure if this was the best approach.

Chris

"Darren Gosbell" <jam (AT) newsgroups (DOT) nospam> wrote

The NonEmpty() function is definitely the way to go in AS2k5, it is the
most reliable way of filtering out empty cells and is relatively fast.

As I said before NonEmptyCrossjoin has been depreciated in AS2k5 (it is
still there, but it not recommended). There are also some rare
circumstances when the NON EMPTY function on the axis does not produce
correct results. I blogged about this here
http://geekswithblogs.net/darrengosb.../17/75357.aspx

--
Regards
Darren Gosbell - SQL Server MVP
Blog: http://www.geekswithblogs.net/darrengosbell

In article <#NIzDVi0GHA.4796 (AT) TK2MSFTNGP03 (DOT) phx.gbl>, "ChrisHarrington"
<charrington-at-activeinterface.com> says...

Is this possible in AS2000?

"Darren Gosbell" wrote:

No, the NonEmpty() function was only introduced with AS 2005.

--
Regards
Darren Gosbell - SQL Server MVP
Blog: http://www.geekswithblogs.net/darrengosbell

In article <9CA483BD-CDA8-4D9E-B756-A0F52B39A298 (AT) microsoft (DOT) com>,
SAM (AT) discussions (DOT) microsoft.com says...