Computer model summary with Custom Field

[murpmic]

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?

[Susan_A · Accepted solution]

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