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 09, 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 09, 2009 11:45 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 3:53 PM
Points: 1,136, Visits: 690
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: Thursday, April 17, 2014 3:53 PM
Points: 1,136, Visits: 690
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: Monday, April 07, 2014 9:24 PM
Points: 18, Visits: 142
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 01, 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 09, 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: Tuesday, April 01, 2014 2:57 PM
Points: 443, Visits: 822
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