PC Age and color formatting

[mrdisco]

I have a report that I'm using to list devices in a certain Asset Group. I'm wanting to highlight devices that are older than 5 years to help managers visually see what needs budgeted to be replaced.


In the report below, I'm using a string I found to take the Purchase Date, add 5 years, and then display that as a number.
Cast(DateDiff(dd, tblAssetCustom.PurchaseDate, GetDate()) / 365.2425 As numeric(8,1)) As Age

I would love for that number to be displayed in red, if the date is older than today.




I've also seen this line used:
Case When tblAssetCustom.Warrantydate < GetDate() Then 'red' End As foregroundcolor,


If I could take the tablAssetCustom.PurchaseDate + 5 years, IF older than today, make it red. That also works. Thanks in advance!



Select Top 1000000 tblAssets.AssetName,
tblADComputers.Description,
tsysAssetTypes.AssetTypename,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.Username As [Last Login],
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
Cast(DateDiff(dd, tblAssetCustom.PurchaseDate, GetDate()) / 365.2425 As numeric(8,1)) As Age
From tblAssets
Inner Join tblADComputers On tblADComputers.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssetCustom.State = 1 And tblAssetGroups.AssetGroup Like 'DEPARTMENT'
Order By tsysAssetTypes.AssetTypename,
tblAssets.AssetName

[rader]

I found that thread about 1/2hr of posting the request. I think what was screwing up my logic was the missing , in the previous lines before the CASE command. SQL's not my lingo, but I'm adapting my programming logic to it's nuances. I was able to get some basic colors figured out thanks to your code and the other thread.

Now I'm figuring out how to do math in SQL statements. Then I'll add colors to the results to make the results needed stand out better.

Thank you.

[RCorbeil]

You can do the same thing with both foreground (text) and background colours. See this thread.

[mrdisco]

Oh, man. That's wonderful. Thank you so much!

[rader]

Josh R Smith wrote:
Oh, man. That's wonderful. Thank you so much!

Would you mind posting the complete code here. I'm trying to get the color to work so I can use the structure in another report, and I'm having a heck of a time getting it to work. I've tried adding the snippet code from RC62N to your report, but all I get is Invalid "Select" statement. Unexpected token "Case" at line xx, pos x.

Thanks.

[mrdisco]

rader wrote:

Josh R Smith wrote:
Oh, man. That's wonderful. Thank you so much!

Would you mind posting the complete code here. I'm trying to get the color to work so I can use the structure in another report, and I'm having a heck of a time getting it to work. I've tried adding the snippet code from RC62N to your report, but all I get is Invalid "Select" statement. Unexpected token "Case" at line xx, pos x.

Thanks.

I hope this helps!

Select Top 10000000 tblAssets.AssetName,
  tblADComputers.Description,
  tsysAssetTypes.AssetTypename,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssets.Username As [Last Login],
  tblAssetCustom.PurchaseDate,
  tblAssetCustom.Warrantydate,
  Cast(DateDiff(dd, tblAssetCustom.PurchaseDate, GetDate()) / 365.2425
  As numeric(8,1)) As Age,
  Case
    When tblAssetCustom.PurchaseDate Is Null Then '#ff00ff'
    When DateAdd(dd, 5 * 365.2425, tblAssetCustom.PurchaseDate) < GetDate() Then
      '#ff0000'
  End As foregroundcolor
From tblAssets
  Inner Join tblADComputers On tblADComputers.AssetID = tblAssets.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
  Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
    tblAssetGroupLink.AssetGroupID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssetCustom.State = 1 And tblAssetGroups.AssetGroup Like 'HCA School'
Order By tsysAssetTypes.AssetTypename,
  tblAssets.AssetName

[RCorbeil · Accepted solution]

Add one more column that conditionally defines the foreground colour, e.g.

  CASE
    WHEN tblAssetCustom.PurchaseDate IS NULL THEN '#ff00ff'  -- no purchase date: magic pink
    WHEN DateAdd(dd, 5*365.2425, tblAssetCustom.PurchaseDate) < GetDate() THEN '#ff0000' -- more than 5yo: red
  END AS foregroundcolor