Jeje - EXISTS() with COUNT works. Needs to add SET to it.

[Prasad‏]

Hi Jeje,

I am able to get the count working now. See the MDX below. I have one filter
in my "WHERE" clause - Local Market with "NE - North East" region.

WITH
MEMBER[Measures].[Count] AS
'count(exists([Servicing Provider].[Hierarchy].[TAX_ID_NBR].members,
{[Time].[Hierarchy].[Year].&[2006]}, "SALES MSR GRP"))'

SELECT
{[Measures].[Count]}ON COLUMNS FROM [SALES Model]
WHERE
([Local Market].[Hierarchy].[REGION_CD].&[NE])

Now, i want to add one more region "SE - South East" to the 'WHERE CLAUSE".
I am getting the following error.

Error: The 'Hierarchy' hierarchy appears more than once in the tuple.

WITH
MEMBER[Measures].[Count] AS
'count(exists([Servicing Provider].[Hierarchy].[TAX_ID_NBR].members,
{[Time].[Hierarchy].[Year].&[2006]}, "SALES MSR GRP"))'

SELECT
{[Measures].[Count]}ON COLUMNS FROM [SALES Model]
WHERE
([Local Market].[Hierarchy].[REGION_CD].&[NE], [Local
Market].[Hierarchy].[REGION_CD].&[SE])

Please help with the syntax. I tried creating a set for the Local Market
with 'SE' & 'NE' and included it in my "WHERE". But the stmt returned 0.

Thanks,

Prasad.

[ZULFIQAR SYED‏]

Hi Prasad,

Here is a sample query based on adventure works where it adds couple of
promotion member items to the where clause. This might you in adding
extra region in your where clause.

Thanks

WITH
MEMBER MEASURES.X AS
[Promotion].[Promotion].&[1]
SELECT
(
[Date].[Calendar].[Calendar Semester].&[2003]&[2]
.CHILDREN
)
ON 1,
(
[Measures].[Reseller Order Quantity]
,

[Product].[Product Categories].[Category]
)
ON 0
FROM
[Adventure Works]

WHERE
{
[Promotion].[Promotion].&[1]
, [Promotion].[Promotion].&[2]

}

HTH..

ZULFIQARSYED

HTTP://ZULFIQAR.TYPEPAD.COM

[Prasad‏]

Hi Syed,

Your suggestion works. Thank You.

I found the problem in my statement. I was referring to multiple members
(more than one) from the same dimension in the tuple. That is why i got the
error.

Once i made it a SET, the problem is resolved.

"ZULFIQAR SYED" wrote:

> Hi Prasad,
>
> Here is a sample query based on adventure works where it adds couple of
> promotion member items to the where clause. This might you in adding
> extra region in your where clause.
>
> Thanks
>
> WITH
> MEMBER MEASURES.X AS
> [Promotion].[Promotion].&[1]
> SELECT
> (
> [Date].[Calendar].[Calendar Semester].&[2003]&[2]

> ..CHILDREN