
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-28-2016 02:40 PM
Hello,
When we set asset state to Repair we also add a comment about what the issue was. I have made a report wich finds the assets with the correct asset ID and then shows the comments sorted by asset ID since i didnt mange to sort on date. But how do i make it only show the last comment ? Maybe also hide the commentID if thats possible ?
My current report :
EDIT : is it also possible to send a alert if there havent been a comment the last 14 days ? Eventually a modified report.
When we set asset state to Repair we also add a comment about what the issue was. I have made a report wich finds the assets with the correct asset ID and then shows the comments sorted by asset ID since i didnt mange to sort on date. But how do i make it only show the last comment ? Maybe also hide the commentID if thats possible ?
My current report :
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssetCustom.State,
tblAssetCustom.Warrantydate,
tblAssetComments.Comment,
tblAssetComments.Added,
tblAssetComments.AddedBy,
tblAssetComments.CommentID
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetComments On tblAssets.AssetID = tblAssetComments.AssetID
Where tblAssetCustom.State = 13
Order By tblAssetComments.CommentID Desc
EDIT : is it also possible to send a alert if there havent been a comment the last 14 days ? Eventually a modified report.
Solved! Go to Solution.
Labels:
- Labels:
-
Report Center
1 ACCEPTED SOLUTION
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-30-2016 03:10 PM
The report below lists "repair" assets whose last comment is from more than 14 days ago and the details of that last comment. To remove the date filter, clear the Criteria column of the tblAssetComments.Added expression. You can have the results of any report in the Reports tab sent via email on a scheduled basis by following the instructions in this knowledge base article.
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssetCustom.State,
tblAssetCustom.Warrantydate,
tblAssetComments.Comment,
tblAssetComments.AddedBy,
tblAssetComments.Added
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetComments On tblAssets.AssetID = tblAssetComments.AssetID
Inner Join (Select tblAssetComments.AssetID,
Max(tblAssetComments.Added) As Max
From tblAssetComments
Group By tblAssetComments.AssetID) SubQuery On SubQuery.AssetID =
tblAssetComments.AssetID And SubQuery.Max = tblAssetComments.Added
Where tblAssetCustom.State = 13 And tblAssetComments.Added < GetDate() - 14
Order By tblAssetComments.Added
1 REPLY 1
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-30-2016 03:10 PM
The report below lists "repair" assets whose last comment is from more than 14 days ago and the details of that last comment. To remove the date filter, clear the Criteria column of the tblAssetComments.Added expression. You can have the results of any report in the Reports tab sent via email on a scheduled basis by following the instructions in this knowledge base article.
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssetCustom.State,
tblAssetCustom.Warrantydate,
tblAssetComments.Comment,
tblAssetComments.AddedBy,
tblAssetComments.Added
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetComments On tblAssets.AssetID = tblAssetComments.AssetID
Inner Join (Select tblAssetComments.AssetID,
Max(tblAssetComments.Added) As Max
From tblAssetComments
Group By tblAssetComments.AssetID) SubQuery On SubQuery.AssetID =
tblAssetComments.AssetID And SubQuery.Max = tblAssetComments.Added
Where tblAssetCustom.State = 13 And tblAssetComments.Added < GetDate() - 14
Order By tblAssetComments.Added
