4 Replies Latest reply: May 1, 2012 10:53 PM by Amitesh Modi RSS

    null value expression help

      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

        • null value expression help
          Kaushik Solanki

          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

            • null value expression help

              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