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

[RCorbeil]

If you want to tighten it up a little, since it's how you're grouping the machines anyway, you could reduce the class-assignment to

  WHEN tblAssetCustom.Model LIKE 'Latitude E64%' THEN 'Standard Laptop (Latitude E64xx)'
  WHEN tblAssetCustom.Model LIKE 'Latitude E65%' THEN 'Eng Laptop (Latitude E65xx)'
  WHEN tblAssetCustom.Model LIKE 'Optiplex 7%'   THEN 'Standard Desktop (Optiplex 7xxx)'
  WHEN tblAssetCustom.Model LIKE 'Precision Workstation T3%' THEN 'Eng Desktop (Precision T3xxx)'

Just a thought.

Also, you can eliminate some redundancy in the WHERE clause

Where
  tblAssets.Assettype = -1
  And tblAssetGroups.AssetGroup = 'DLS'
  And ( (tblAssetCustom.Model Like 'Latitude E6420%')
        Or (tblAssetCustom.Model Like 'Latitude E6430%')
        Or (tblAssetCustom.Model Like 'Latitude E6520%')
        Or (tblAssetCustom.Model Like 'Latitude E6530%')
        Or (tblAssetCustom.Model Like 'Optiplex 790%')
        Or (tblAssetCustom.Model Like 'Optiplex 7010%')
        Or (tblAssetCustom.Model Like 'Precision Workstation T3500%')
        Or (tblAssetCustom.Model Like 'Precision Workstation T3600%')
      )

You don't need to include "And tblAssetGroups.AssetGroup = 'DLS'" on every machine model OR since you've already got that covered outside the group of ORs.

[Mlacombe · Accepted solution]

Excellent. That does just what I need. Again, thank you very much for the assistance.

This is what I ended up with:

Select Top 1000000 Case
When tblAssetCustom.Model Like 'Latitude E6420%' Then
'Standard Laptop (Latitude E64xx)'
When tblAssetCustom.Model Like 'Latitude E6430%' Then
'Standard Laptop (Latitude E64xx)'
When tblAssetCustom.Model Like 'Latitude E6520%' Then
'Eng Laptop (Latitude E65xx)'
When tblAssetCustom.Model Like 'Latitude E6530%' Then
'Eng Laptop (Latitude E65xx)'
When tblAssetCustom.Model Like 'Optiplex 790%' Then
'Standard Desktop (Optiplex 7xxx)'
When tblAssetCustom.Model Like 'Optiplex 7010%' Then
'Standard Desktop (Optiplex 7xxx)'
When tblAssetCustom.Model Like 'Precision Workstation T3500%' Then
'Eng Desktop (Precision T3xxx)'
When tblAssetCustom.Model Like 'Precision Workstation T3600%' Then
'Eng Desktop (Precision T3xxx)' End As AssetClass,
Sum(Case When tblState.State = 9 Then 1 Else 0 End) As Stock,
Sum(Case When tblState.State = 7 Then 1 Else 0 End) As Spare
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
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Where tblAssets.Assettype = -1 And tblAssetGroups.AssetGroup = 'DLS' And
((tblAssetCustom.Model Like 'Latitude E6420%' And tblAssetGroups.AssetGroup =
'DLS') Or (tblAssetCustom.Model Like 'Latitude E6430%' And
tblAssetGroups.AssetGroup = 'DLS') Or (tblAssetCustom.Model Like
'Latitude E6520%' And tblAssetGroups.AssetGroup = 'DLS') Or
(tblAssetCustom.Model Like 'Latitude E6530%' And tblAssetGroups.AssetGroup =
'DLS') Or (tblAssetCustom.Model Like 'Optiplex 790%' And
tblAssetGroups.AssetGroup = 'DLS') Or (tblAssetCustom.Model Like
'Optiplex 7010%' And tblAssetGroups.AssetGroup = 'DLS') Or
(tblAssetCustom.Model Like 'Precision Workstation T3500%' And
tblAssetGroups.AssetGroup = 'DLS') Or (tblAssetCustom.Model Like
'Precision Workstation T3600%' And tblAssetGroups.AssetGroup = 'DLS'))
Group By Case
When tblAssetCustom.Model Like 'Latitude E6420%' Then
'Standard Laptop (Latitude E64xx)'
When tblAssetCustom.Model Like 'Latitude E6430%' Then
'Standard Laptop (Latitude E64xx)'
When tblAssetCustom.Model Like 'Latitude E6520%' Then
'Eng Laptop (Latitude E65xx)'
When tblAssetCustom.Model Like 'Latitude E6530%' Then
'Eng Laptop (Latitude E65xx)'
When tblAssetCustom.Model Like 'Optiplex 790%' Then
'Standard Desktop (Optiplex 7xxx)'
When tblAssetCustom.Model Like 'Optiplex 7010%' Then
'Standard Desktop (Optiplex 7xxx)'
When tblAssetCustom.Model Like 'Precision Workstation T3500%' Then
'Eng Desktop (Precision T3xxx)'
When tblAssetCustom.Model Like 'Precision Workstation T3600%' Then
'Eng Desktop (Precision T3xxx)' End,
tsysAssetTypes.AssetTypename

[RCorbeil]

One more try.

SELECT Top 1000000
  CASE
    WHEN tblAssetCustom.Model LIKE 'Latitude E6420%' THEN 'notebook class 1'
    WHEN tblAssetCustom.Model LIKE 'Latitude E6430%' THEN 'notebook class 1'
    WHEN tblAssetCustom.Model LIKE 'Latitude E6520%' THEN 'notebook class 2'
    WHEN tblAssetCustom.Model LIKE 'Latitude E6530%' THEN 'notebook class 2'
    WHEN tblAssetCustom.Model LIKE 'Optiplex 790%'                THEN 'desktop class 1'
    WHEN tblAssetCustom.Model LIKE 'Optiplex 7010%'               THEN 'desktop class 1'
    WHEN tblAssetCustom.Model LIKE 'Precision Workstation T3500%' THEN 'desktop class 2'
    WHEN tblAssetCustom.Model LIKE 'Precision Workstation T3600%' THEN 'desktop class 2'
  END AS AssetClass,
  Sum(CASE WHEN tblState.State = 9 THEN 1 ELSE 0 END) AS Stock,
  Sum(CASE WHEN tblState.State = 7 THEN 1 ELSE 0 END) AS Spare
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
  INNER JOIN tblAssetGroupLink ON tblAssets.AssetID = tblAssetGroupLink.AssetID
  INNER JOIN tblAssetGroups    ON tblAssetGroups.AssetGroupID = tblAssetGroupLink.AssetGroupID
WHERE
  tblAssets.Assettype = -1
  AND tblAssetGroups.AssetGroup = 'DLS'
  AND ( (tblAssetCustom.Model LIKE 'Latitude E6420%')
        OR (tblAssetCustom.Model LIKE 'Latitude E6430%')
        OR (tblAssetCustom.Model LIKE 'Latitude E6520%')
        OR (tblAssetCustom.Model LIKE 'Latitude E6530%')
        OR (tblAssetCustom.Model LIKE 'Optiplex 790%')
        OR (tblAssetCustom.Model LIKE 'Optiplex 7010%')
        OR (tblAssetCustom.Model LIKE 'Precision Workstation T3500%')
        OR (tblAssetCustom.Model LIKE 'Precision Workstation T3600%')
      )
GROUP BY
  CASE
    WHEN tblAssetCustom.Model LIKE 'Latitude E6420%' THEN 'notebook class 1'
    WHEN tblAssetCustom.Model LIKE 'Latitude E6430%' THEN 'notebook class 1'
    WHEN tblAssetCustom.Model LIKE 'Latitude E6520%' THEN 'notebook class 2'
    WHEN tblAssetCustom.Model LIKE 'Latitude E6530%' THEN 'notebook class 2'
    WHEN tblAssetCustom.Model LIKE 'Optiplex 790%'                THEN 'desktop class 1'
    WHEN tblAssetCustom.Model LIKE 'Optiplex 7010%'               THEN 'desktop class 1'
    WHEN tblAssetCustom.Model LIKE 'Precision Workstation T3500%' THEN 'desktop class 2'
    WHEN tblAssetCustom.Model LIKE 'Precision Workstation T3600%' THEN 'desktop class 2'
  END,
  tsysAssetTypes.AssetTypename

Alter the 'notebook class' and 'desktop top class' descriptions to meet your needs.

There's probably a cleaner way of doing this, but I haven't done nearly enough work with grouping in SQL to know, so we're going with "it's not pretty, but it gets the job done". If anyone else reading cares to chime in with optimizations, I'm always ready to learn how to tighten things up.

[RCorbeil]

Argh. I need to stop and read all the words. Disregard the above.

[RCorbeil]

How about something like this?

SELECT Top 1000000
  CASE
    WHEN tblPortableBattery.AssetID IS NULL
    THEN 'standard desktop'
    ELSE 'standard laptop'
  END AS AssetClass,
  Sum(CASE WHEN tblState.State = 9 THEN 1 ELSE 0 END) AS Stock,
  Sum(CASE WHEN tblState.State = 7 THEN 1 ELSE 0 END) AS Spare
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
  INNER JOIN tblAssetGroupLink ON tblAssets.AssetID = tblAssetGroupLink.AssetID
  INNER JOIN tblAssetGroups    ON tblAssetGroups.AssetGroupID = tblAssetGroupLink.AssetGroupID
  LEFT JOIN tblPortableBattery ON tblPortableBattery.AssetID = tblAssets.AssetID
WHERE
  tblAssets.Assettype = -1
  AND tblAssetGroups.AssetGroup = 'DLS'
  AND ( (tblAssetCustom.Model LIKE 'Latitude E6420%')
        OR (tblAssetCustom.Model LIKE 'Latitude E6430%')
        OR (tblAssetCustom.Model LIKE 'Latitude E6520%')
        OR (tblAssetCustom.Model LIKE 'Latitude E6530%')
        OR (tblAssetCustom.Model LIKE 'Optiplex 790%')
        OR (tblAssetCustom.Model LIKE 'Optiplex 7010%')
        OR (tblAssetCustom.Model LIKE 'Precision Workstation T3500%')
        OR (tblAssetCustom.Model LIKE 'Precision Workstation T3600%')
      )
GROUP BY
  CASE
    WHEN tblPortableBattery.AssetID IS NULL
    THEN 'standard desktop'
    ELSE 'standard laptop'
  END,
  tsysAssetTypes.AssetTypename

Instead of grouping on the machine model name, I've added a link to tblPortableBattery and am using that as a shortcut to determine whether the machine is a notebook or not: if it has a portable battery then it's a notebook. If you want more precision than is/isn't a notebook then a different approach will be needed.

[Mlacombe]

I appreciate the help so far, but unfortunately, I must ask for more!

What I am being asked to do now is combine the output of two lines into one line and display a custom name for the row. For example, here is a sample of the output of the current report:

Model-Stock-Spare
Latitude E6430 3- 2
Latitude E6420 2- 1

Which means we have 3 total E6430's in stock, and 2 total E6420's in stock. What my manager would like to see is something like this:

Model-Stock-Spare
Standard Laptop 5-3

Where "Standard Laptop" is the sum of the Latitude E6420 (2) + the Latitude E6430 (3). Is something like this possible? The reasoning is to simplify this for our techs, and rather than have 15-20 models listed in the report, we would only have "Standard Laptop", "Engineering Laptop", "Engineering Desktop", etc.

Here is the current code that I have:

Select Top 1000000 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 = 7 Then 1 Else 0 End) As Spare
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
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Where (tblAssets.Assettype = -1 And Coalesce(tblAssetCustom.Model,
N'Unknown') Like 'Latitude E6420%' And tblAssetGroups.AssetGroup = 'DLS') Or
(Coalesce(tblAssetCustom.Model, N'Unknown') Like 'Latitude E6430%' And
tblAssetGroups.AssetGroup = 'DLS') Or
(Coalesce(tblAssetCustom.Model, N'Unknown') Like 'Latitude E6520%' And
tblAssetGroups.AssetGroup = 'DLS') Or
(Coalesce(tblAssetCustom.Model, N'Unknown') Like 'Latitude E6530%' And
tblAssetGroups.AssetGroup = 'DLS') Or
(Coalesce(tblAssetCustom.Model, N'Unknown') Like 'Optiplex 790%' And
tblAssetGroups.AssetGroup = 'DLS') Or
(Coalesce(tblAssetCustom.Model, N'Unknown') Like 'Optiplex 7010%' And
tblAssetGroups.AssetGroup = 'DLS') Or
(Coalesce(tblAssetCustom.Model, N'Unknown') Like
'Precision Workstation T3500%' And tblAssetGroups.AssetGroup = 'DLS') Or
(Coalesce(tblAssetCustom.Model, N'Unknown') Like
'Precision Workstation T3600%' And tblAssetGroups.AssetGroup = 'DLS')
Group By Coalesce(tblAssetCustom.Model, N'Unknown'),
Coalesce(tblAssetCustom.Manufacturer, N'Unknown'),
tsysAssetTypes.AssetTypename

-Mike

[RCorbeil]

Actually, on consideration, because you know that the fields you're looking for won't be NULL, you can do away with the Coalesce() in the WHERE clause.

WHERE
  tblAssets.Assettype = -1
  AND tblAssetCustom.Manufacturer LIKE 'Dell%'

WHERE
  tblAssets.Assettype = -1
  AND tblAssetCustom.Model LIKE 'Latitude%'

WHERE
  tblAssets.Assettype = -1
  AND ( tblAssetCustom.Model LIKE 'Latitude%'
        OR tblAssetCustom.Model LIKE 'Precision%'
      )

All the Coalesce() does is

IF <field> IS NOT NULL
THEN <field>
ELSE <alt-text>

So in the case of the model, for example,

IF tblAssetCustom.Model IS NOT NULL
THEN tblAssetCustom.Model
ELSE 'Unknown'

[Mlacombe]

Awesome, that's what I needed. Thank you for the help!

[RCorbeil]

You want to expand on the WHERE clause.

To list only Dell machines

WHERE
  tblAssets.Assettype = -1
  AND Coalesce(tblAssetCustom.Manufacturer, N'Unknown') LIKE 'Dell%'

To list only machines where the model name starts with Latitude

WHERE
  tblAssets.Assettype = -1
  AND Coalesce(tblAssetCustom.Model, N'Unknown') LIKE 'Latitude%'

Build it up if you want multiple conditions

WHERE
  tblAssets.Assettype = -1
  AND ( Coalesce(tblAssetCustom.Model, N'Unknown') LIKE 'Latitude%'
        OR Coalesce(tblAssetCustom.Model, N'Unknown') LIKE 'Precision%'
      )

If you're unfamiliar with using LIKE, the percent symbol matches anything, so 'Blah%' means "starts with 'Blah'", '%Blah' means "ends with 'Blah'" and '%Blah%' means "contains 'Blah'".