Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

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

0 views
Skip to first unread message

Prasad

unread,
Aug 1, 2006, 2:16:01 PM8/1/06
to
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

unread,
Aug 19, 2006, 1:35:58 PM8/19/06
to
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

unread,
Aug 21, 2006, 1:13:01 PM8/21/06
to
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

0 new messages