how to sort a Report by server then workstation

[bmbarnett]

Trying to find the best way (easiest) to sort a report that is sorted by asset type to keep servers together, workstations, and maybe even laptops/tablets/notebooks. I do not know of a way to create a server asset type.

[RCorbeil]

The server or workstation class is already collected. You can use tblComputersystem.DomainRole to obtain a numeric representation of the machine's role. Link in tblDomainRoles if you want a description.

SELECT
  ...
  Case
    When tblComputersystem.Domainrole <= 1
    Then 'Not server'
    Else 'Server'
  End As isServer,
  tbldomainroles.Domainrolename,   -- if you want to know more than "is/isn't server"
  ...
FROM
  ...
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  -- optional: if you want to know more than "is/isn't server"
  Inner Join tbldomainroles On tbldomainroles.Domainrole = tblComputersystem.Domainrole
  ...

As far as identifying laptops/notebooks, you can use the chassis type, but that's a bit of a mish-mash. From my own inventory, some are identified as notebooks, some as laptops, some as docking stations... it's a bit of a mess. I found it easier to do a left join against tblPortableBattery and check whether or not there's a battery associated with the asset.

SELECT
  ...
  Case
    When tblPortableBattery.Name Is Not Null
    Then 'Laptop'
    Else 'Not laptop'
  End As isLaptop,
  TsysChassisTypes.ChassisName  -- if you want to know the chassis type
  ...
FROM
  ...
  Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
  -- optional: if you want to know the chassis type
  Inner Join tblSystemEnclosure On tblAssets.AssetID = tblSystemEnclosure.AssetID
  Inner Join TsysChassisTypes On tblSystemEnclosure.ChassisTypes = TsysChassisTypes.Chassistype
  ...