Notification

Icon
Error

Total by Asset State - Total and Group by Asset State

Posted: Thursday, August 13, 2020 10:37:38 PM(UTC)
compuag

compuag

Member Original PosterPosts: 4
0
Like
I have the following query that I'd like to run in a report, click a selected statename and have it generate another detailed report with all assets that match that state.
Code:

SELECT
ts.Statename,
ts.State,
count(ta.AssetID) AS Total
FROM
tblAssets AS ta
INNER JOIN tblAssetCustom AS tac ON tac.AssetID = ta.AssetID
INNER JOIN tblState AS ts ON ts.State = tac.State
GROUP BY
ts.Statename,
ts.State
ORDER BY ts.Statename
_______________________________
Output:
Statename    State    Total
Active        1    3994
Harvested    15    2
Inventoried    12    38
Non-active    2    540
Salvaged    10    119
Suspended    14    2


For example, if you clicked "Harvested" you'd see the details of the two assets that match that state. Is this possible?
compuag
#1compuag Member Original PosterPosts: 4  
posted: 8/14/2020 12:14:13 AM(UTC)
For those interested, here is how I was able to get it to work:

Code:

Select Top 1000000
'http://SERVERNAME:81/Report/report.aspx?det=Web50getdevicebystate&title=All '
+ ts.Statename + ' Assets&@state=' + Convert(NVARCHAR(2),ts.State) As
hyperlink_hyp,
ts.Statename As hyperlink_name_hyp,
Count(ta.AssetID) As Total
From tblAssets As ta
Inner Join tblAssetCustom As tac On tac.AssetID = ta.AssetID
Inner Join tblState As ts On ts.State = tac.State
Group By
'http://SERVERNAME:81/Report/report.aspx?det=Web50getdevicebystate&title=All '
+ ts.Statename + ' Assets&@state=' + Convert(NVARCHAR(2),ts.State),
ts.Statename,
ts.State
Order By hyperlink_name_hyp



Thanks.

Active Discussions

Action Open Teams/SfB Chat with user
by  marceman   Go to last post Go to first unread
Last post: 9/10/2020 4:10:18 PM(UTC)
Lansweeper Report showing app even after it has gone
by  TimHolmes1973  
Go to last post Go to first unread
Last post: 9/9/2020 11:50:17 PM(UTC)
Action Remote Device Manager
by  steveb   Go to last post Go to first unread
Last post: 9/4/2020 9:52:01 PM(UTC)
Action View Windows Defender detections remotely
by  steveb  
Go to last post Go to first unread
Last post: 9/4/2020 9:37:48 PM(UTC)
Action Remote Uninstaller
by  steveb   Go to last post Go to first unread
Last post: 9/4/2020 9:27:17 PM(UTC)
Lansweeper Filter Assets' groups by WinSystemLocale
by  Alex Beaumier  
Go to last post Go to first unread
Last post: 8/20/2020 4:17:17 PM(UTC)
Action Chrome History
by  csys   Go to last post Go to first unread
Last post: 8/14/2020 2:14:21 PM(UTC)
Action Offer remote assistance
by  FixitDave  
Go to last post Go to first unread
Last post: 8/4/2020 9:57:51 AM(UTC)