4 Replies Latest reply on May 20, 2020 5:22 AM by Sinisa Mikor

    Why does "CI = Null" yields to no result?

    Annie Dueck
      Share This:

      I'm trying to develop a report from Smart Reporting to display all "CI" that is null for data integrity.

       

      Yet when I use the filter "in Null", it does not display asset records with "CI" null.

      I've also tried to use "Simple" formula as a calculated field, no luck.

       

       

      suggestions ?

       

      Thank you !

        • 1. Re: Why does "CI = Null" yields to no result?
          Joseph Ruiz

          Assuming your DB is SQL, its hard to query on null since its by definition nothing. Try pulling in all records on that form and Filtering on a custom field case statement that sets null entries to N/A. Or maybe drop down to the view level and set the nulls to "N/A" there?

          • 2. Re: Why does "CI = Null" yields to no result?
            Sinisa Mikor

            Are you literally using "in Null"?  If so, you're probably using operand In List and value Null when you should be using operand Is Null; replace those and you'll be able to prepare reports such as this one which shows how many CIs per class lack AssetID (also known as CI ID+ in class-specific AST:% forms):

             

                 SELECT DISTINCT

                      `AST:Attributes`.`ClassId`,

                      COUNT(`AST:Attributes`.`ClassId`)

                 FROM

                      `AR System Schema`.`AST:Attributes`

                 WHERE (

                      `AST:Attributes`.`AssetID` IS NULL

                 )

                 GROUP BY

                      `AST:Attributes`.`ClassId`

            1 of 1 people found this helpful
            • 3. Re: Why does "CI = Null" yields to no result?
              Annie Dueck

              this is the path for CI ID+

              Does this change the formula? the above did not work for me.

               

              and, yes, I was using Is Null, still no luck.

               

               

               

              Thank you

              • 4. Re: Why does "CI = Null" yields to no result?
                Sinisa Mikor

                In your case, `AST:BaseElement`.`AssetID` (labeled CI ID+ on the form) isn't mapped to CI ID in report; instead, it is mapped to `AST:BaseElement`.`Instance Id` which is local representation of `BMC.CORE:BMC_BaseElement`.`InstanceId` and every record in latter form "must" have this attribute populated (technically, its Entry Mode is set to Optional instead of Required, so it shouldn't have to, but InstanceId is functionally a translation of RequestId to UUID format and is always set in any form it is used on).  In other words, SQL statement of your report contains ˙AST:BaseElement`.`Instance Id` IS NULL instead of `AST:BaseElement`.`AssetID` IS NULL; replace those and report will display CIs lacking CI ID+.

                2 of 2 people found this helpful