Report with Dynamic Groups

[jproust]

Hi,

I would like the same report with Dynamic Groups which I customized by group. For example, I grouped laptop, portable, notebook by "Laptops". And if it's possible to count them, it will be perfect 😉

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
TsysChassisTypes.ChassisName,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Serialnumber,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Expiration],
tblAssets.Username,
tblAssets.Userdomain,
tsysAssetTypes.AssetTypename As Type,
tblAssets.IPAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysIPLocations.IPLocation,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblADusers.Department
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblADusers On tblAssets.Username = tblADusers.Username
Inner Join tblSystemEnclosure On
tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join TsysChassisTypes On tblSystemEnclosure.ChassisTypes =
TsysChassisTypes.Chassistype
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Where (TsysChassisTypes.ChassisName = 'Laptop' Or TsysChassisTypes.ChassisName =
'Notebook' Or TsysChassisTypes.ChassisName = 'Portable' Or
TsysChassisTypes.ChassisName = 'Desktop')
Order By TsysChassisTypes.ChassisName Desc

[RCorbeil]

According to the response here, in June 2015 it was not possible to create reports based on dynamic asset groups. I'm assuming that continues to be the case.

[RCorbeil]

On the other hand, if you're saying that you've defined dynamic groups and you're wanting to report based on them, in theory something like this should work:

Select Top 1000000
  ...
  tblAssetGroups.AssetGroup,
  ...
From
  ...
  Inner Join tblAssetGroupLink On tblAssetGroupLink.AssetID = tblAssets.AssetID
  Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID = tblAssetGroupLink.AssetGroupID
Where
  tblAssetGroups.Dynamic = 1
  AND tblAssetGroups.AssetGroup IN ('Desktop', 'Laptop')
Order By
  tblAssetGroups.AssetGroup,
  TsysChassisTypes.ChassisName Desc

I say "in theory" because when I run a report against asset groups, I get precisely zero dynamic groups reported despite having a few defined. Static groups show up fine, just no dynamic.

[RCorbeil]

If I'm correctly reading what you wrote, you're just looking for a way of creating your own category name for a selection of chassis types, right? If so, add the following CASE to your query:

  CASE
    WHEN TsysChassisTypes.ChassisName IN ('Desktop',
                                          'Low Profile',
                                          'Mini Tower',
                                          'Tower',
                                          'Space-Saving',
                                          'All in One',
                                          'Mini PC',
                                          'Pizza Box',
                                          'Sealed-Case') THEN 'Desktop'
    WHEN TsysChassisTypes.ChassisName IN ('Portable',
                                          'Laptop',
                                          'Notebook',
                                          'Convertible',
                                          'Sub Notebook',
                                          'Hand Held',
                                          'Lunch Box') THEN 'Laptop'
    ELSE 'Other'
  END AS ChassisGroup,

and add the new field to your ORDERing.

I populated the CASE values from the contents of TsysChassisTypes. Unless you're certain that you don't have (or won't someday have) any of the other ChassisName values, you may want to consider expanding your WHERE filter.

Speaking of the WHERE clause, you can simplify it if you like:

Where
  TsysChassisTypes.ChassisName IN ('Laptop', 'Notebook', 'Portable', 'Desktop')

As far as counting them,

Select Top 1000000
  COUNT(*) AS [Count],
  CASE
    WHEN TsysChassisTypes.ChassisName IN ('Desktop',
                                          'Low Profile',
                                          'Mini Tower',
                                          'Tower',
                                          'Space-Saving',
                                          'All in One',
                                          'Mini PC',
                                          'Pizza Box',
                                          'Sealed-Case') THEN 'Desktop'
    WHEN TsysChassisTypes.ChassisName IN ('Portable',
                                          'Laptop',
                                          'Notebook',
                                          'Convertible',
                                          'Sub Notebook',
                                          'Hand Held',
                                          'Lunch Box') THEN 'Laptop'
    ELSE 'Other'
  END AS [ChassisGroup]
From
  tblAssetCustom
  Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
  Inner Join tblSystemEnclosure On tblAssets.AssetID = tblSystemEnclosure.AssetID
  Inner Join TsysChassisTypes On tblSystemEnclosure.ChassisTypes = TsysChassisTypes.Chassistype
Where
  TsysChassisTypes.ChassisName IN ('Laptop', 'Notebook', 'Portable', 'Desktop')
Group By
  CASE
    WHEN TsysChassisTypes.ChassisName IN ('Desktop',
                                          'Low Profile',
                                          'Mini Tower',
                                          'Tower',
                                          'Space-Saving',
                                          'All in One',
                                          'Mini PC',
                                          'Pizza Box',
                                          'Sealed-Case') THEN 'Desktop'
    WHEN TsysChassisTypes.ChassisName IN ('Portable',
                                          'Laptop',
                                          'Notebook',
                                          'Convertible',
                                          'Sub Notebook',
                                          'Hand Held',
                                          'Lunch Box') THEN 'Laptop'
    ELSE 'Other'
  END

should do the trick.