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

Help w/ Calculation

I need to calculate the overall GPA for a student in a particular class.

YEAR SCHOOL STUDENT IDENT GRADE TEACHER CLASS GPA
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 0.0000
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 1.6700
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 3.3300
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 3.6700
2007 Snow Canyon High Student1 321649 10 Teacher2 Elementary Algebra 0.0000
2007 Snow Canyon High Student1 321649 10 Teacher2 Elementary Algebra 0.6700
2007 Snow Canyon High Student1 321649 10 Teacher2 Elementary Algebra 1.0000

The problem I'm having is that a student may not taken the class for four terms (as in the Elementary Algebra example above). So I can't hard code it to sum the gpa and divide by 4; it needs to be the number of terms the student took the class.

Here's my sql:

select
trnscrpt.schyear as [Year],
school.schname as School,
rtrim(stugrp_active.lastname) + ', ' + rtrim(stugrp_active.firstname) as Student,
trnscrpt.suniq as suniq,
stugrp_active.graden as Grade,
trnscrpt.teachname as Teacher,
trnscrpt.descript as Class,
gpamarks.gpavallvl0 AS GPA

from
dbo.trnscrpt
inner join dbo.stugrp_active on trnscrpt.suniq = stugrp_active.suniq INNER JOIN
school ON stugrp_active.schoolc = school.schoolc INNER JOIN
gpamarks ON trnscrpt.marksetc1 = gpamarks.marksetc AND trnscrpt.markawd1 = gpamarks.mark

where
trnscrpt.graden >= 6 and
trnscrpt.markawd1 not in ('NC','NG','P','W','WA','WF','WI','WP') and
trnscrpt.subjectc in ('LA', 'MA', 'CP', 'CB') and
trnscrpt.schyear = 2007 and
stugrp_active.schoolc = 725

order by
school.schname,
student,
grade,
class

Have you considered a AVG() and a GROUP BY?

|||

That worked. In the beginning the averages weren't what I expected but it was mistake on my part. Thanks and I like your signature. So true.