→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
compuag
Engaged Sweeper
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.

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?
1 REPLY 1
compuag
Engaged Sweeper
For those interested, here is how I was able to get it to work:


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.