gelöst!! Version 8.0 Dashboard SQL String nur Aktive Assets

[jlies]

Hallo Leute,
Ich habe grad die neue Version installiert und wollte mit das Dashboard mal anzeigen, da sehe ich das mit alle Gerätetypen angezeigt werden. Kann mir vielleicht einer einen Tipp geben, wie ich den SQL.String anpassen kann, sodass ich nur alle aktiven Assets sehe?
Computer je TYP

Code: Select all

				SELECT TOP 8
				CASE
				WHEN exists(SELECT * FROM [SPSAssetPickupType-CI] ci WHERE ci.Owner = cop.[ID] AND LCID = @LCID_long)
				THEN
				(
				SELECT TOP 1 ci.DisplayString FROM [SPSAssetPickupType-CI] ci WHERE ci.Owner = cop.[ID] and LCID = @LCID_long
				)
				WHEN exists(SELECT * FROM [SPSAssetPickupType-CI] ci WHERE ci.Owner = cop.[ID] AND LCID = @LCID_short)
				THEN
				(
				SELECT TOP 1 ci.DisplayString FROM [SPSAssetPickupType-CI] ci WHERE ci.Owner = cop.[ID] and LCID = @LCID_short
				)
				ELSE cop.DisplayString END AS Expr_Name,
				Count(cop.DisplayString) AS Expr_Count
				FROM SPSComputerClassBase coc
				inner join SPSAssetClassBase ass on ass.[Expression-ObjectID] = coc.[Expression-ObjectID]
				inner join SPSStockKeepingUnitClassBase sku on sku.[ID] = ass.[sku]
				INNER JOIN SPSAssetPickupType cop
				ON cop.Value = sku.Type
				GROUP BY cop.DisplayString, cop.[ID]
				ORDER BY Expr_Count DESC

Danke JAN

[Sven_Puth]

SELECT TOP 8
CASE
WHEN exists(SELECT * FROM [SPSAssetPickupType-CI] ci WHERE ci.Owner = cop.[ID] AND LCID = @LCID_long)
THEN
(
SELECT TOP 1 ci.DisplayString FROM [SPSAssetPickupType-CI] ci WHERE ci.Owner = cop.[ID] and LCID = @LCID_long
)
WHEN exists(SELECT * FROM [SPSAssetPickupType-CI] ci WHERE ci.Owner = cop.[ID] AND LCID = @LCID_short)
THEN
(
SELECT TOP 1 ci.DisplayString FROM [SPSAssetPickupType-CI] ci WHERE ci.Owner = cop.[ID] and LCID = @LCID_short
)
ELSE cop.DisplayString END AS Expr_Name,
Count(cop.DisplayString) AS Expr_Count
FROM SPSComputerClassBase coc
inner join SPSAssetClassBase ass on ass.[Expression-ObjectID] = coc.[Expression-ObjectID]
inner join SPSStockKeepingUnitClassBase sku on sku.[ID] = ass.[sku]
INNER JOIN SPSAssetPickupType cop
ON cop.Value = sku.Type
INNER JOIN SPSCommonClassBase COM on COM.[Expression-ObjectID] = ass.[Expression-ObjectID]
WHERE COM.State = 710
GROUP BY cop.DisplayString, cop.[ID]
ORDER BY Expr_Count DESC

[jlies]

Danke!! Hat super geklappt