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

Lansweeper Show attached USB devices
by  Dannnnooo   Go to last post Go to first unread
Last post: Today at 10:42:17 AM(UTC)
Lansweeper Lansweeper Ubiquiti AP Bullet Devices
by  Beta_Tester  
Go to last post Go to first unread
Last post: Today at 8:59:06 AM(UTC)
Lansweeper Report to find ScanServer 'not working'
by  Rocher Vincent   Go to last post Go to first unread
Last post: Today at 8:07:26 AM(UTC)
Lansweeper Security: HSTS Missing
by  Grey  
Go to last post Go to first unread
Last post: Yesterday at 9:36:49 PM(UTC)
Lansweeper Include custom ticket fields as email tags
by  brownscar   Go to last post Go to first unread
Last post: 9/29/2020 4:09:02 PM(UTC)
Lansweeper SSH - Keyboard Interactive Authentication
by  blackmoonwolf  
Go to last post Go to first unread
Last post: 9/29/2020 1:21:59 PM(UTC)
Lansweeper Lansweeper Dark Theme
by  blackmoonwolf   Go to last post Go to first unread
Last post: 9/29/2020 1:18:32 PM(UTC)
Lansweeper Drag and Drop Email
by  Chris Durham  
Go to last post Go to first unread
Last post: 9/29/2020 7:13:09 AM(UTC)