开发者

MDX Query - 0 if Empty

Having a hard time doing this simple thing: return 0 instead of "null"

SELEC开发者_C百科T
{
    [Measures].[Count]
} ON COLUMNS 
, {
CASE WHEN  
        [TimeDMY].[Month] = 0
    Then 0
    Else 
    [TimeDMY].[Month]
    End

} ON ROWS 
FROM [Views]
WHERE {
    (
    [TimeDMY].[Year -  Month -  Date].[Month].&[2011-07-01T00:00:00]

This returns correct value if Count is not null for a given month.

If the value is null for a given month, i get:

Query (20, 1) The function expects a tuple set expression for the 1 argument. A string or numeric expression was used.

I've tried to make this work with IIF, and IsEmpty functions.. with similar success as this.


I would use a calculated measure:

with member [Measures].[count-x] as 
      IIF( isEmpty( ([Measures].[Count], [TimeDMY].[Month].currentMember) ), 
           0, 
           ( [Measures].[Count], [TimeDMY].[Month].currentMember ) 
      )

SELECT 
  [Measures].[count-x]  ON 0,
  [TimeDMY].[Month].members on 1  
FROM [Views]
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜