February 9, 2009 at 9:49 am
I am trying to using ISNULL equivalent in MDX using ISEMPTY.
I want to compare 2 dates and display the not null date value in the report.
The MDX expression I am using is:
IIF(ISEMPTY(DATE1),DATE3,DATE2)
DATE1 = [DATE1].[DT Date]
DATE2 = [DATE1].[DT Date].ALLMEMBERS
DATE3 = [DATE3].[DT DATE].ALLMEMBERS
The result always displays the LAST DATE specified in the condition i.e. DATE2 in he above scenario, irrespective of the IIF Condition.
Could you please help me in resolving the issue lease!!!
February 9, 2009 at 11:45 am
IsEmpty doesn't check for null...
from BOL:
Dealing with Empty Values in MDX Statements and Cubes
In Multidimensional Expressions (MDX) statements, you can look for empty values and then perform certain calculations on cells with valid (that is, not empty) data. Eliminating empty values when performing calculations can be important because certain calculations, such as an average, can be inaccurate if empty cell values are included.
If empty values may be stored in your underlying fact table data, and you do not want empty cell values appearing in your cube, you should create queries and data-modification statements that either eliminate empty values or coalesce empty values into some other value. Additionally, you can use the Null Processing option on a measure to modify null facts so that the null is converted into 0, converted to an empty value, or even throws an error during processing.
null processing
http://msdn.microsoft.com/en-us/library/ms170707(SQL.90).aspx
February 10, 2009 at 3:30 am
Then how should the MDX be framed to display date2 when date1 is null?
February 10, 2009 at 10:28 am
I would probably change the query to produce something other than null that you can use your iif statement on...also you could set the null processing or unknown member settings as per the article or you could find a function besides isempty to use in the iif statement(not sure if this exists)....:)
January 11, 2011 at 5:45 pm
I am going to change the query so that the null is not returned, but it would be nice to have the option when nulls are encountered and changing the cube structure is not an option. Is there no way of replicating the isnull functionality?
January 13, 2011 at 6:51 am
If you want to use this in SSRS then you should use ISNOTHING statement.
such as:
IIIF( ISNOTHING([saleunit].value)=true, 1,0)
You can also use the following code:
IIF ( saleunit.value IS NOTHING,1,0)
I hope it helps
July 25, 2011 at 12:00 am
Try COALESCEEMPTY(date1, date2). Returns the first nonempty field
September 28, 2011 at 1:29 am
Ah thanks this one works well
IIF(CoalesceEmpty(Field,0) = 0, True,False)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply