→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
murpmic
Engaged Sweeper III
I use the custom20 field to hold the planned retirement year of of our computers. I'd like to be able to run the "Computer model summary" report and have the Custom20 (Retirement year) show. I think because this is consoldated report it doesn't want to do this. How would I modify the report to get this done so I could see which year a particular model is supposed to retire?
1 ACCEPTED SOLUTION
Susan_A
Lansweeper Alumni
I'm assuming you're referring to the following built-in report, which can be displayed in the chart report widget as well: "Chart: Computer model summary". To add the Custom20 field and keep the report compatible with the chart report widget, you'll need to combine the model name and custom field into a single expression, as the chart report widget only supports two columns. I've included a sample query below.
Select Top 1000000 LTrim(RTrim(Coalesce(tblAssetCustom.Manufacturer, N'') +
N' ' + Coalesce(tblAssetCustom.Model, N''))) + ' / Retirement: ' + Case
When tblAssetCustom.Custom20 Is Not Null And tblAssetCustom.Custom20 <>
'' Then tblAssetCustom.Custom20 Else 'unknown' End As Compmodel,
Count(tblAssets.AssetID) As Total
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblAssetCustom.State = 1 And tblAssets.Assettype = -1
Group By LTrim(RTrim(Coalesce(tblAssetCustom.Manufacturer, N'') + N' ' +
Coalesce(tblAssetCustom.Model, N''))) + ' / Retirement: ' + Case
When tblAssetCustom.Custom20 Is Not Null And tblAssetCustom.Custom20 <>
'' Then tblAssetCustom.Custom20 Else 'unknown' End
Order By Total Desc,
Compmodel

View solution in original post

1 REPLY 1
Susan_A
Lansweeper Alumni
I'm assuming you're referring to the following built-in report, which can be displayed in the chart report widget as well: "Chart: Computer model summary". To add the Custom20 field and keep the report compatible with the chart report widget, you'll need to combine the model name and custom field into a single expression, as the chart report widget only supports two columns. I've included a sample query below.
Select Top 1000000 LTrim(RTrim(Coalesce(tblAssetCustom.Manufacturer, N'') +
N' ' + Coalesce(tblAssetCustom.Model, N''))) + ' / Retirement: ' + Case
When tblAssetCustom.Custom20 Is Not Null And tblAssetCustom.Custom20 <>
'' Then tblAssetCustom.Custom20 Else 'unknown' End As Compmodel,
Count(tblAssets.AssetID) As Total
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblAssetCustom.State = 1 And tblAssets.Assettype = -1
Group By LTrim(RTrim(Coalesce(tblAssetCustom.Manufacturer, N'') + N' ' +
Coalesce(tblAssetCustom.Model, N''))) + ' / Retirement: ' + Case
When tblAssetCustom.Custom20 Is Not Null And tblAssetCustom.Custom20 <>
'' Then tblAssetCustom.Custom20 Else 'unknown' End
Order By Total Desc,
Compmodel