Home Analyst Portal

Unable to get accurate count of Asset Status in SQL Chart Widget

Michael_DuganMichael_Dugan Premier Partner IT Monkey ✭
Hello,

I'm trying to create a column chart or something similar that will show a count of assets by status. I believe the query I am using is correct, but the returned data is not displaying the correct count. Here is the query I designed:


SELECT LT.LTValue AS 'Hardware Asset Status'
      ,COUNT(LT.LTValue) AS 'Status Count'

FROM MT_Cireson$AssetManagement$HardwareAsset HW

INNER JOIN LocalizedText LT
ON LT.LTStringId = HW.HardwareAssetStatus_3019ADDF_4F3D_2C55_2024_72C22E11F4CF
AND LT.LanguageCode = 'ENU'

WHERE LT.LTValue IS NOT NULL
GROUP BY LT.LTValue
ORDER BY LT.LTValue ASC


I provided a GROUP BY clause in the query, but tried removing that to see if anything would change. If I use a SQL table widget, the dashboard is able to retrieve the data just fine. Below is an image of the data after the query is applied (in the form of a SQL Chart Widget).

Is there anything I am missing?

Answers

  • seth_coussensseth_coussens Member Ninja IT Monkey ✭✭✭✭
    Remove the spaces in your column labels (hardware asset status vs Hardware_Asset_Status, there is an issue with kendo tables / charts when the title has a space in it (just as you can't have a space in sql itself).
  • Michael_DuganMichael_Dugan Premier Partner IT Monkey ✭
    Thanks for the quick reply, Seth! I applied the name change to the column aliases and it seems that it didn't change the chart. Here's the updated code:


    SELECT LT.LTValue AS 'HardwareAssetStatus'
          ,COUNT(LT.LTValue) AS 'StatusCount'

    FROM MT_Cireson$AssetManagement$HardwareAsset HW

    INNER JOIN LocalizedText LT
    ON LT.LTStringId = HW.HardwareAssetStatus_3019ADDF_4F3D_2C55_2024_72C22E11F4CF
    AND LT.LanguageCode = 'ENU'

    WHERE LT.LTValue IS NOT NULL
    GROUP BY LT.LTValue
    ORDER BY LT.LTValue ASC


    I attached another screenshot just to be sure.

    Could it be that we need to apply a hotfix? We're currently at 7.3 (799 for a json version).
  • seth_coussensseth_coussens Member Ninja IT Monkey ✭✭✭✭
    Actually, I think the issue is that you are telling the query to group by, and the widget doesn't understand.

    We actually just added a feature in v7.4 that lets you set the horizontal and vertical fields related to a group by statement. In 7.3 when you select a column to group by we are grouping it for you.
Sign In or Register to comment.