OLAP Cube - Calculated Member

[Wajid55]

I am using XI, created an OLAP Cube from SQL Server 2005 cube.

I am trying to add a Calculated Member in the cube with an IIF function. It is a simple IIF statement:

IIF([Measures].[Hours]<=40,[Measures].[Hours],40)

I have column in cube called Hours I am trying to insert a member which shows 40 if Hours is >40 otherwise it gives me the value from Hours.

Thats all I am trying to do but it is only showing values if it is <=40, not showing 40 if it is >40.

Any idea what could be the problem.
Thanks

 

[lbass]

I wonder if you are running into a null. Try using a regular if/then like this:

if isnull({Measures.Hours}) then 0 else
if {Measures.Hours} <= 40 then
{Measures.Hours} else
40

-LB

 

[Wajid55]

I am using OLAP Cube, normal IF..then..else doesn't work in it.

There are seperate functions for OLAP Cubes and it works similar to XL or Access IF then else.

 

[lbass]

Sorry, I didn't realize it took different functions. Maybe someone else can help.

-LB