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

Michael_Dugan

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?

seth_coussens

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_Dugan

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_coussens

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.