Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

ISNULL USING MDX IN SSRS Expand / Collapse
Author
Message
Posted Monday, February 9, 2009 9:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 14, 2009 4:30 AM
Points: 3, Visits: 37
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!!!
Post #652971
Posted Monday, February 9, 2009 11:45 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 7, 2014 10:01 AM
Points: 1,136, Visits: 697
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
Post #653081
Posted Tuesday, February 10, 2009 3:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 14, 2009 4:30 AM
Points: 3, Visits: 37
Then how should the MDX be framed to display date2 when date1 is null?

Post #653526
Posted Tuesday, February 10, 2009 10:28 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 7, 2014 10:01 AM
Points: 1,136, Visits: 697
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)....:)
Post #653986
Posted Tuesday, January 11, 2011 5:45 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:18 PM
Points: 18, Visits: 143
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?
Post #1046213
Posted Thursday, January 13, 2011 6:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 1, 2013 3:48 AM
Points: 101, Visits: 402
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
Post #1047232
Posted Monday, July 25, 2011 12:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 9, 2013 5:27 PM
Points: 1, Visits: 58
Try COALESCEEMPTY(date1, date2). Returns the first nonempty field
Post #1147310
Posted Wednesday, September 28, 2011 1:29 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 3:42 PM
Points: 452, Visits: 848
Ah thanks this one works well
IIF(CoalesceEmpty(Field,0) = 0, True,False)
Post #1182237
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse