null value expression help

[#1]

Hi,

I have a field called Resolved Date, which has null and non-null values. I want to count all cases where Resolved Date is null

My SQL Server expression is

SELECT  Count([Case ID])

FROM [DB].[dbo].[Table]

  WHERE [Assigned Group] ='Service Desk'

AND (isnull([Resolved Date/Time],'')='')

My QV expression which is not working properly is

=count({<[Assigned Group] = {'Service Desk'} >}

if(IsNull(ResolvedDate)=''), [Case ID]))

I'm unable to properly formulate the null expression?

I know that the values for Resolved Date are not blank strings because when I use Case Id and Resolved Date in a Straight table chart and check Suppress when Null, all the records with Resolved Date as null/empty values get filtered out.

I would highly appreciate any help.

Thanks,

AM

[kaushiknsolanki]

Hi,

   You can do one change in datamodel itself.

   Create a field which will tell you that the Resoved date is null or not.

   Something like this

    if(isnull(ResolvedDate),1,0) as NullCheck

    How you can use this as filter.

Regards,

Kaushik Solanki

[#3]

Thanks Kaushik,

I did as you suggested. However, when I pulled in ResolvedDate and Null Check in a table box, I got a 0 value under Null Check field corresponding to a Blank value under Resolved Date.

This makes me wonder if it is not a null but a blank string issue.

What made me think it was a null value issue in the first place was the test that I had done as I mentioned in my initial post.

Can you tell me how I would modify my expression to accomodate for a blank string.

(When researching cases similar to this issue I found the expressions which used

if(len(trim(Expr) = 0)), but was unable to get it working properly. 

Thanks,

AM

[kaushiknsolanki · Accepted solution]

Hi,

    First thing create a normal textbox of that field and see if you can see any blank values. if there then copy that and then use expression in load as

    if(Resolveddate = ' ',1,0)

Regards,

Kaushik Solanki

[#5]

Hi Kaushik,

It worked like a charm.

Thanks,

AM