Tuesday, August 31, 2010

Sum with empty(null) measures

When I was working on a report I ran into a problem when I tried to sum a measure that contained an empty cell.  I expected the empty cells to be treated as zero but that wasn’t the case.  The result returned was null.  Here’s the sample data.
DistrictAttendance Rate
A(null)
A0.0%
A94.5%
My first solution was just to apply a filter to remove the empty cell from the set.
With Member Total AS   
SUM(EXISTING(FILTER([Attending School].[District], NOT ISEMPTY(Measures.[Attendance Rate])),Measures.[Attendance Rate])




The result was still null. I tried a variety of things to remove the empty cells from the calculated measure or transform them into zero but with no success.  The result was always the same, null for the sum instead of 94.5.

Consequently I moved away from manipulating the calculated member itself and came up with a solution in a subcube.  I was able to remove those cells without an attendance rate from the cube before summing.

WITH MEMBER Total AS 
SUM(Existing([Attending School].[District]),Measures.[Attendance Rate]))

SELECT Measures.Total ON COLUMNS,
[Attending School].[District] ON ROWS
FROM (SELECT  [Enrolled From Date].[Academic Calendar].[Academic Year] ON COLUMNS,
Filter([Attending School].[District] NOT ISEMPTY([Measures].[Attendance Rate])) ON ROWS
FROM [EdDW])
WHERE [Enrolled From Date].[Academic Calendar].[Academic Year].&[20082009]


 

Thursday, August 26, 2010

Intro

I'm a MCITP DBA and MCTS in Business Intelligence who's been working with SQL Server for over 10 years, starting as a VB programmer becoming a SQL Analyst graduating to a SQL Developer and finally a SQL DBA.  I'm currently employed by Mariner in Charlotte, NC a company specializing in BI where I hope to gain competancy in Analysis Services to add to my SQL portfolio.  While I worked with SSIS and SSRS in my former position SSAS was not a direction they wished to go.

I've decided to start this blog with the hope that others can learn from my experiences (good and bad).  I plan to post once a week.