Is Not Null in SSRS Expression?

dkim18
dkim18 used Ask the Experts™
on
Hi,

I have a long sql where a lot of is not null and is null are using.
I am writing an expression in Grouping.
How can I do write this up?

 field1 is not null and (filed2 = "None"  OR filed2 is NULL)  and (field3 is Null and field4 is null)


some of these fileds are date type.
Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
=iif(
IsNothing(Fields!Field1.Value) and
(IsNothing(Fields!Field2.Value)
or Fields!Field.Value = "None")
and IsNothing(Fields!Field3.Value
and IsNothing(Fields!Field4.Value)
,"NULL"
,"else")

Author

Commented:
How can I do field1 is not null?
In your SQL you need to control the nulls there. It makes it easier to code the report that way.
Example:      isnull(Mydata,'')      as       Column1Mydata

Author

Commented:
What I am doing in my logic is I am checking if there is any  value in the field.
Some of these fields might be date fields.

So like if orderdate is filled or shippeddate is empty/null.

So I need a way to check IS NOT NULL in my long sql.
Basically I have this where statement I want to use in one of grouping in SSRS.
I know how to handle one filed.

But how can i do for a long sql? It will be longer than this.
 field1 is not null and (filed2 = "None"  OR filed2 is NULL)  and (field3 is Null and field4 is null)

Commented:
Do you want to do this in SQL code or SSRS?

Author

Commented:
I am doing in SSRS's grouping,expression section.

I am grouping using one field and also subgrouping one of parent groups using different fields.
Say I have Type field to group first.
Type1

Type2

TYpe 3

I also group Type2 into subgroups but my condition is pretty long and not straight forward like field21.
I believe I can use a long sql like statments in this expresson of this grouping.
But I need to be able to check if fields are either empty, null or certain values.
My above example represents it.


Isnothing doesn't do for me.
I might have null and strng none for no selection.

I need to check if is null, is not null, or is something string.

Author

Commented:
The problem is IIF is, it returns both true and false.
I only want true to go to my expression.
Hope that make any sense.
Commented:
The IIF can return whatever you wish

=IIF(A=A,"PRINT","NOPRINT")
so you can group on that

then add a filter that

=IIF(A=A,"PRINT","NOPRINT") = "PRINT"




Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial