Custom Report based on Model/State

[Mlacombe]

Hi,

If possible, I'd like some assistance on creating a custom report. I have no idea if this is possible and am open to suggestions, but what I am looking for is something like this:


Computer Model| State1(Non-Active)| State2(Stock)| Total

OptiPlex| 5| 3| 8|
Latitude| 1| 1| 2|
Precision| 6| 4| 10|

**NOTE**The above table didn't copy over very well, but it should essentially be 4 separate columns.

Specifically what I need is a report that shows specific computer models with specific states. So this way my manager can open 1 report that will show him how many OptiPlex's are in stock, how many Latitude's are in stock, etc. The reason I added 2 extra column's is because it would be an added bonus for him to be able to see how many OptiPlex's are currently active, how many are non-active, how many are in stock, and what the total number of OptiPlex's is. I would also like to be able to specify which models to include, and not show all computer models (we have a lot of legacy hardware we are trying to phase out, so I would prefer not to have them included in this list).

Again, I'm not sure if this is possible, and if anyone has any other suggestions for a similar arrangement, I'm open to it, but ideally I'd like to try to squeeze this all into one report.

Thank you in Advance,

Mike

[Mlacombe]

Awesome, and now last but not least, how can I either:

1) Only show models whose vendor is Dell Inc.

OR

2) Show only specific models (ie. OptiPlex 790, Latitude e6420, Precision Workstation T3500, etc.)

This is the code I have so far:

Select Top 1000000 Coalesce(tblAssetCustom.Manufacturer, N'Unknown') As Vendor,
Coalesce(tblAssetCustom.Model, N'Unknown') As AssetModel,
Sum(Case When tblState.State = 9 Then 1 Else 0 End) As Stock,
Sum(Case When tblState.State = 2 Then 1 Else 0 End) As [Non-Active]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblState On tblState.State = tblAssetCustom.State
Where tblAssets.Assettype = -1
Group By Coalesce(tblAssetCustom.Manufacturer, N'Unknown'),
Coalesce(tblAssetCustom.Model, N'Unknown'),
tsysAssetTypes.AssetTypename

[RCorbeil]

I misread your original request, looking at the 1 and 2, not the descriptions of the states. Sorry about that.

As far as the total goes, you can make the CASE statement either

CASE
  WHEN tblstate.State IN (1, 9)
  THEN 1
  ELSE 0
END

or

CASE
  WHEN (tblstate.State = 1)
       OR (tblstate.State = 9)
  THEN 1
  ELSE 0
END

Six of one, half-a-dozen of the other.

[Mlacombe]

Awesome, thank you for the reply. That is almost exactly what I need, with a couple minor tweaks:

1. I need to limit it to only include models where Dell Inc. is the vendor.

2. After looking at it more, I think the columns I really need are: Stock and Non-Active. I changed your code from

Sum(CASE WHEN tblstate.State = 1 THEN 1 ELSE 0 END) AS Active,

to

Sum(CASE WHEN tblstate.State = 9 THEN 1 ELSE 0 END) AS Stock,

, so now how to I adjust the totals column to reflect the sum of only these 2 columns?

Again, thank you very much for the assistance!

[RCorbeil]

If yes to the above, then this should pare things down to just the two states you want in the column layout you requested:

SELECT Top 1000000
  tsysAssetTypes.AssetTypename,
  Coalesce(tblAssetCustom.Manufacturer, N'Unknown') AS Vendor,
  Coalesce(tblAssetCustom.Model, N'Unknown')        AS AssetModel,
  Sum(CASE WHEN tblstate.State =  1 THEN 1 ELSE 0 END) AS Active,
  Sum(CASE WHEN tblstate.State =  2 THEN 1 ELSE 0 END) AS [Non-Active],
  Sum(CASE WHEN tblstate.State <= 2 THEN 1 ELSE 0 END) AS Total
FROM
  tblAssets
  INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
  INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
  INNER JOIN tblstate       ON tblstate.State = tblAssetCustom.State
WHERE
  tblAssets.Assettype = -1
GROUP BY
  tsysAssetTypes.AssetTypename,
  Coalesce(tblAssetCustom.Manufacturer, N'Unknown'),
  Coalesce(tblAssetCustom.Model, N'Unknown')

[RCorbeil]

Using "Computer: Model by vendor" as a starting point, does this produce the base data you're looking for, i.e. a count of each state for each make/model?

SELECT Top 1000000
  tsysAssetTypes.AssetTypename,
  Coalesce(tblAssetCustom.Manufacturer, N'Unknown') AS Vendor,
  Coalesce(tblAssetCustom.Model, N'Unknown')        AS AssetModel,
  tblstate.State,
  tblstate.Statename,
  Count(tblAssets.AssetID) AS Total
FROM
  tblAssets
  INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
  INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
  INNER JOIN tblstate       ON tblstate.State = tblAssetCustom.State
WHERE
  tblAssets.Assettype = -1
GROUP BY
  tsysAssetTypes.AssetTypename,
  Coalesce(tblAssetCustom.Manufacturer, N'Unknown'),
  Coalesce(tblAssetCustom.Model, N'Unknown'),
  tblstate.State,
  tblstate.Statename