Don't Display Empty Members - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

Don't Display Empty Members

Author

Topic

ansonee
Starting Member

3 Posts

Posted - 2011-10-26 : 15:07:12

I have a couple of calculated members that while the calculations are correct, the display of te cube isn't quite right. I'm trying to get it so that only members with a value are displayed. I've included some screen shots below. The first screen shot is of a "Monthly Hour" breakdown by a particular department. Only those employees with hours within the chosen time period appear:

EmployeeHours is a "plain" measure in the cube. Here's the named calculation for NewHourPercentage:

IIF

( ISEMPTY ( [Employee Hours Time Category].[Utilization Category].[Category].[Measures].[Employee Hours] ) , 0 , [Measures].[Employee Hours] / [Measures].[NetHoursMinusPTO])

What I need to do is to create to new named calculations: one for overtime hours and one for overtime percentage. Essentially, I need to take the Total number of employee hours and subtract that from a named calculation called [Net Hours - Minus Holidays]. If the amount is less than or equal to zero, display a zero in OvertimeHours. If it's greater than zero display that amount. But also, I don't want to display any members (Employees) who do not have hours for that period. Below is a screen shot of what I am trying to avoid:

As you can see, the calculation is being run on each total, not the grand total. Also, there are employees who have no hours for that time period but the calculation is being run on them. In fact, it's being run on every employee. Here is the calculation:

IIF

( ISEMPTY ( [Employee Hours Time Category].[Utilization Category].[Category].[Measures].[Employee Hours] ) , 0 , [Measures].[Employee Hours] - [Measures].[Net Hours - Minus Holidays])

Any help / assistance anyone can render would be greatly appreciated!

Thanks!

A. M. Robinson

nicklarge
Starting Member

40 Posts

Posted - 2011-10-27 : 13:49:46

ansonee,

I am unable to see your pictures - this is probably a setting in my browser though.
Anyway, I will attempt to answer your question. What I would do if I was you is to create the member "Overtime hours" as such in your cube. That way it will be browsable:

CASE WHEN ([Measures].[Net Hours - Minus Holidays] - [Measures].[Total Number of Employee Hours]) <= 0
THEN 0
ELSE ([Measures].[Net Hours - Minus Holidays] - [Measures].[Total Number of Employee Hours])
END

Next, create a set for employees that you want to include, using the FILTER command to filter out employees who do not have hours for the given period (using Currentmember on the Date/Time dimension).

The other point to note about your IIF is that, in SSAS, 0=NULL and NULL=0. I would refrain from using IIF and ISEMPTY, and instead use Case, something like:

CASE WHEN ( ([Employee Hours Time Category].[Utilization Category].[Category], [Measures].[Employee Hours]) ) = NULL
THEN 0
ELSE ([Measures].[Employee Hours] - [Measures].[Net Hours - Minus Holidays])
END

Also, it is worth writing the MDX in SSMS before creating new measures and sets, so that you only need to process the cube when the correct MDX is written. Something like:

WITH MEMBER [Measures].[Some Measure 1]
AS
...
MEMBER [Measures].[Some Measure 2]
AS
...
SET [New Set]
AS
...
SELECT
... ON 0,
... ON 1
FROM
...

Hope this helps.
Nick.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources