Help with Calculated member

I'm trying to create a calculate member in SQL Server 2005 that will return a distinct count of clients year to date for all dates within my time dimension. I have figured out that if I create a measure that returns a distinct count then I can use the aggregate function and YTD function to get the distinct count YTD for any date. However, the aggregate function requires a slicer dimension (i think) and every time I put the mdx into the expression box, it does not like the syntax.

I have tried this:

With member [Authorization Service Date].[Calendar].[BlaBla] as

Aggregate(

YTD(

[Authorization Service Date].[Calendar].Current)

)

select [Authorization Service Date].[Calendar].[BlaBla] on columns

from dss

where [Measures].[Authorized Member Distinct Count]

does not like syntax

I have tried this:

Aggregate(

YTD(

[Authorization Service Date].[Calendar].Current)

)

select [Authorization Service Date].[Calendar].[BlaBla] on columns

from dss

where [Measures].[Authorized Member Distinct Count]

does not like syntax

and I have tried this:

Aggregate(

YTD(

[Authorization Service Date].[Calendar].Current)

)

returns error: Error 1 MdxScript(DSS) (67, 4) The function can be called only on a named set. 0 0

I don't know wht this means

Any help would be deeply appreciated

I think you are looking to use the CURRENTMEMBER function instead of CURRENT.

I think this will simply take you to your next challenge. If you run this query, you'll likely get NULL for your result.

I think something like this will better serve your needs:

Code Snippet

With member [Measures].[x] as

COUNT(

EXISTS(

[Customer].[Customer].[Customer].Members,

YTD([Delivery Date].[Calendar].CurrentMember),

'Internet Sales'

)

)

select

[Measures].[x] on columns

from [Adventure Works]

where [Delivery Date].[Date].[June 1, 2002]

;

|||

Bryan,

I appreciate your taking your time to assist me with this. Unfortunately, it did not work. As translated, your mdx on my system looks like

with member [Measures].[x] as

COUNT(

EXISTS(

[CCE Members].[MemberId].Members,

YTD([Authorization Service Date].[Calendar].CurrentMember),

'Authorization Service Days Count'

)

)

SELECT [Measures].[x] ON 0

FROM [dss]

WHERE [Authorization Service Date].[Full Date].&[6303]

I get xError

Just for your info however, I know that all of the underlying attributes and metrics join up properly becuase when I execute:

SELECT [Measures].[Authorization Service Days Count] ON 0,

[CCE Members].[MemberId].Members ON 1

FROM [dss]

WHERE [Authorization Service Date].[Full Date].&[6303]

it works.

Again, your assistance is trully appreciated.

Thanks,

Michael

|||

Two things I noticed in the EXISTS function. Try changing the set to include just the leaf level members:

[CCE Members].[MemberId].Members --> [CCE Members].[MemberId].[MemberId].Members

Also, is 'Authorization Service Days Count' the name of the measure or the measure group? You need to identify the measure group.

B.

sql