→ 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: 
Mlacombe
Engaged Sweeper III
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
1 ACCEPTED SOLUTION
Mlacombe
Engaged Sweeper III
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

View solution in original post

14 REPLIES 14
RCorbeil
Honored Sweeper II
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
Engaged Sweeper III
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
Honored Sweeper II
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
Honored Sweeper II
Argh. I need to stop and read all the words. Disregard the above.
RCorbeil
Honored Sweeper II
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
Engaged Sweeper III
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
Honored Sweeper II
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
Engaged Sweeper III
Awesome, that's what I needed. Thank you for the help!
RCorbeil
Honored Sweeper II
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'".