Error with MDX expression

1
votes

I have the following expression MDX :

isnull([Measures].[Available Hours],0)

After processing cube I got the following error in reporting :

No appropriate overload function was found for the stored procedure .The parameters are incorrect .MdxScript Execution of the managed stored procedure isnull failed with the following error :Microsoft AnalysisServices AdomdServer AdomdException

How to resolve the error ?

2
ssasmdx

2 Answers

2
votes

isnull is not an mdx function.

To test for null in mdx try using iif

 iif(  
  [Measures].[Available Hours] = 0,
  0,
  [Measures].[Available Hours]
 )

It looks a little strange as we look for 0 and then if it is 0 change it to 0!!

But here is an example:

WITH 
  MEMBER [Measures].[Internet Sales Amount 2] AS 
    IIF
    (
      [Measures].[Internet Sales Amount] = 0
     ,0
     ,[Measures].[Internet Sales Amount]
    ) 
SELECT 
  {
    [Measures].[Internet Sales Amount]
   ,[Measures].[Internet Sales Amount 2]
  } ON 0
 ,
    [Customer].[Customer Geography].[Country].MEMBERS
  * 
    [Product].[Category].MEMBERS ON 1
FROM [Adventure Works];

This is what happens:

enter image description here

Edit

Alternative by @MarcPolizzi does the same thing and his code is more compact:

WITH 
  MEMBER [Measures].[Internet Sales Amount 2] AS 
    IIF
    (
      [Measures].[Internet Sales Amount] = 0
     ,0
     ,[Measures].[Internet Sales Amount]
    ) 
  MEMBER [Measures].[Internet Sales Amount 3] AS 
    CoalesceEmpty
    (
      [Measures].[Internet Sales Amount]
     ,0
    ) 
SELECT 
  {
    [Measures].[Internet Sales Amount]
   ,[Measures].[Internet Sales Amount 2]
   ,[Measures].[Internet Sales Amount 3]
  } ON 0
 ,
    [Customer].[Customer Geography].[Country].MEMBERS
  * 
    [Product].[Category].MEMBERS ON 1
FROM [Adventure Works];
1
votes

You could use the CoalesceEmpty function to replace null/empty values. To replace them with a 0 value you can do the following:

CoalesceEmpty([Measures].[Available Hours], 0)

Hope that helps.