Sum a Case Statement

[goofaholix]

I'm trying to Sum a conditional count in Analysis Services.

This is how I'd do it in SQL;

Sum(case when dbo.Dim_TaskResult.SpeciesName is not null
and dbo.Dim_TaskResult.SpeciesName <>'Unknown'
then 1 else 0 end) as Measure.

I've created a Named Calculation in the dsv;
case when dbo.Dim_TaskResult.SpeciesName is not null
and dbo.Dim_TaskResult.SpeciesName <>'Unknown'
then 1 else 0 end

I've then created a Measure in the cube that Sums this Named Calculation.

When I put it in my report every value of a dimension selected has the same total.

Thanks

Bruce

http://homepages.woosh.co.nz/bf/