Report: Printers

[EtienneHB]

Hi all,

My boss asked if it was possible to have a report that will do the following:
List of printer with serial number by ip location sorted by location with a total by location and a total off all printer
EX

IPLOC GSO

Printer 1 --- serial number --- LOC a
Printer 2 --- serial number --- LOC b
Printer 3 --- serial number --- LOC c
Total printer for the ip location (3)

IPLOC SOM
Printer 4 --- serial number --- LOC d
Printer 5 --- serial number --- LOC e
Printer 6 --- serial number --- LOC f
Total printer for the ip location (3)

Printer Total (6)

[EtienneHB]

Hi all if you are interested my colleague found how to do that (the following code is for computer but i've done it for printers too) !
We use the asset name since we have a pattern.

Select Top 1000000 Totaux,
Nombres,
AssetID,
AssetName,
IPAddress,
Location,
Serialnumber,
Model,
Description,
From (Select tsysIPLocations.IPLocation As 'Totaux',
0 As 'Ordre',
Null As 'Nombres',
Null As 'AssetID',
'' As 'AssetName',
'' As 'IPAddress',
'' As 'Location',
'' As 'Serialnumber',
Null As 'Model',
Null As 'Description',
tsysIPLocations.LocationID As 'LocationID'
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Where tblAssets.Assettype = -1 And tblAssets.AssetName Like '%ORD%' And
tblAssetCustom.State = 1
Group By tsysIPLocations.IPLocation,
tsysIPLocations.LocationID
Union All
Select '' As 'Totaux',
1 As 'Ordre',
Null As 'Nombres',
tblAssets.AssetID As 'AssetID',
tblAssets.AssetName As 'AssetName',
tblAssets.IPAddress As 'IPAddress',
tblAssetCustom.Location As 'Location',
tblAssetCustom.Serialnumber As 'Serialnumber',
tblAssetCustom.Model As 'Model',
tblAssets.Description As 'Description',
tsysIPLocations.LocationID As 'LocationID'
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Where tblAssets.AssetName Like '%ORD%' And tblAssets.Assettype = -1 And
tblAssetCustom.State = 1
Union All
Select 'Sous-total' As 'Totaux',
2 As 'Ordre',
(Select Count(a.AssetName) From tblAssets a
Where tsysIPLocations.LocationID = a.LocationID And a.Assettype = -1 And
a.AssetName Like '%ORD%') As 'Nombres',
Null As 'AssetID',
'' As 'AssetName',
'' As 'IPAddress',
'' As 'Location',
'' As 'Serialnumber',
Null As 'Model',
Null As 'Description',
tsysIPLocations.LocationID As 'LocationID'
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Where tblAssets.Assettype = -1 And tblAssets.AssetName Like '%ORD%' And
tblAssetCustom.State = 1
Group By tsysIPLocations.LocationID
Union All
Select 'Total' As 'Totaux',
3 As 'Ordre',
count(*) As 'Nombres',
Null As 'AssetID',
Null As 'AssetName',
Null As 'IPAddress',
Null As 'Location',
Null As 'Serialnumber',
Null As 'Model',
Null As 'Description',
999999999 As 'IPLocation'
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Where tblAssets.Assettype = -1 And tblAssets.AssetName Like '%ORD%' And
tblAssetCustom.State = 1) As t
Order By LocationID,
Ordre,
Totaux