Previously sent to support: Hello I have resolved the issue, but not happy with KACE query builder it’s a piece of junk and needs to be updated. For example if you want to pull a report on a field where field is NULL it pulls no rows. THERE IS NO ZERO LENGTH STRING OPTION IN THE QUERY BUILDER. I had to edit the where clause sql in the report to pull only rows were date (surplus date) = ""

I have substantial background using Mysql, but if someone did not have my skills they would never get it to work. Please fix the report query builder to not only test for NULL but also zero length string otherwise why even have a query builder.

Here is the report sql I have to manually change because the query builder only provides a test for NULL OR NOT NULL! NULL or NOT NULL never works because when KACE creates the new asset types it never assigns the default value to NULL and with date fields you can't even set a default value to NULL.

SELECT A10093.NAME AS FIELD10093, ASSET.NAME AS ASSETNAME, A10096.NAME AS FIELD10096, ASSETDATA18.FIELD10086 AS FIELD10086, ASSETDATA18.FIELD10095 AS FIELD10095, ASSETDATA18.FIELD10089 AS FIELD10089, ASSETDATA18.FIELD10094 AS FIELD10094, ASSETDATA18.FIELD10090 AS FIELD10090 FROM ASSETDATA18 LEFT JOIN ASSET ON ASSETDATA18.ID = ASSET.ASSETDATAID AND ASSET.ASSETTYPEID=18 LEFT JOIN ASSET ASSETLOCATION ON ASSETLOCATION.ID = ASSET.LOCATIONID LEFT JOIN ASSETASSOCIATION J10093 ON J10093.ASSETID = ASSET.ID AND J10093.ASSETFIELDID=10093
LEFT JOIN ASSET A10093 ON A10093.ID = J10093.ASSOCIATEDASSETID
LEFT JOIN ASSETDATA9 AD10093 ON AD10093.ID = A10093.ASSETDATAID
LEFT JOIN ASSETASSOCIATION J10096 ON J10096.ASSETID = ASSET.ID AND J10096.ASSETFIELDID=10096
LEFT JOIN ASSET A10096 ON A10096.ID = J10096.ASSOCIATEDASSETID
LEFT JOIN ASSETDATA1 AD10096 ON AD10096.ID = A10096.ASSETDATAID
WHERE ((ASSETDATA18.FIELD10087 = "")) ORDER BY FIELD10093, FIELD_10089 desc

OF COURSE AFTER I MADE THE CHANGE the query builder is no longer available so then my headers on the report were all changed so then I had to spend the next half hour modifying the SQL to this:

SELECT A10093.NAME AS Site, ASSET.NAME AS DocrNumber, ASSETDATA18.FIELD10086 AS AssignedTo, A10096.NAME AS Location, ASSETDATA18.FIELD10091 AS "Serial Number", ASSETDATA18.FIELD10090 AS UnitModel, ASSETDATA18.FIELD10089 AS "Invoice Date", ASSETDATA18.FIELD10095 AS "Invoice Number", ASSETDATA18.FIELD10094 AS "Item Cost", ASSETDATA18.FIELD10092 AS PSID FROM ASSETDATA18 LEFT JOIN ASSET ON ASSETDATA18.ID = ASSET.ASSETDATAID AND ASSET.ASSETTYPEID=18 LEFT JOIN ASSET ASSETLOCATION ON ASSETLOCATION.ID = ASSET.LOCATIONID LEFT JOIN ASSETASSOCIATION J10093 ON J10093.ASSETID = ASSET.ID AND J10093.ASSETFIELDID=10093
LEFT JOIN ASSET A10093 ON A10093.ID = J10093.ASSOCIATEDASSETID
LEFT JOIN ASSETDATA9 AD10093 ON AD10093.ID = A10093.ASSETDATAID
LEFT JOIN ASSETASSOCIATION J10096 ON J10096.ASSETID = ASSET.ID AND J10096.ASSETFIELDID=10096
LEFT JOIN ASSET A10096 ON A10096.ID = J10096.ASSOCIATEDASSETID
LEFT JOIN ASSETDATA1 AD10096 ON AD10096.ID = A10096.ASSETDATAID
WHERE ((ASSETDATA18.FIELD10087 = "")) ORDER BY Site, FIELD_10089 desc

GOOD GRIEF!