Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ISNULL USING MDX IN SSRS


ISNULL USING MDX IN SSRS

Author
Message
samata.putumbaka.ext
samata.putumbaka.ext
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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!!!
Bob Griffin
Bob Griffin
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1167 Visits: 702
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
samata.putumbaka.ext
samata.putumbaka.ext
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 37
Then how should the MDX be framed to display date2 when date1 is null?
Bob Griffin
Bob Griffin
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1167 Visits: 702
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)....Smile
andymay-496937
andymay-496937
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 197
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?
erdem1973
erdem1973
SSC-Enthusiastic
SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)SSC-Enthusiastic (101 reputation)

Group: General Forum Members
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
Peter Smart
Peter Smart
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 58
Try COALESCEEMPTY(date1, date2). Returns the first nonempty field
davoscollective
davoscollective
SSChasing Mays
SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)

Group: General Forum Members
Points: 603 Visits: 1000
Ah thanks this one works well
IIF(CoalesceEmpty(Field,0) = 0, True,False)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search