Need asst with report - Workstation Warranty status

[Chris1]

I am attempting to create a report to show all active workstations and each workstations warranty status and warranty end date.

ex: Domain Assetname OSname Username Displayname Warranty expired (y/n) WarrantyEndDate


I am able to generate a decent report but each workstation is showing up several times. How can I narrow my scope to show just the active workstations and the warranty status\date?

Select Top 1000000 tblAssets.Domain,
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon16 As icon,
tsysOS.OSname,
tblAssets.Username,
tblWarrantyDetails.WarrantyStartDate,
tblWarrantyDetails.WarrantyEndDate
From tblWarranty
Inner Join tblWarrantyDetails On tblWarranty.WarrantyId =
tblWarrantyDetails.WarrantyId
Inner Join tblAssets On tblWarranty.AssetId = tblAssets.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblComputersystem.Domainrole < 2 And tblAssetCustom.State = 1 And
Not (tblWarranty.LastWarrantySuccess Is Null)
Order By tblAssets.AssetName,
tblWarrantyDetails.WarrantyEndDate Desc

[Susan_A · Accepted solution]

I moved this from the Report Center section of the forum to here. Please keep in mind that the Report Center is only for posting finished reports, not questions. To answer your question: there are multiple lines per machine in your report output because your reports is listing warranty products. If a machine has multiple warranty products, it will therefore be listed multiple times. It would better to:

• Remove tblWarranty and tblWarrantyDetails from your report, with the in the upper right corner of the tables.
• Tick the PurchaseDate and WarrantyDate fields in the tblAssetCustom table instead. PurchaseDate is the purchase date of the asset or the start date of the oldest warranty product. WarrantyDate is the end date of the newest warranty product. Each asset only has one PurchaseDate and WarrantyDate value in tblAssetCustom.