→ 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: 
mhammond
Champion Sweeper
I am working on a custom report, modified from the built-in "low toner" report. I am working on trying generate the report based on 30% toner remaining or less. However, one part really bothers me .... in the "RemainingPercentage" column, not only am I seeing assets that have above 30%, but some that are reporting THOUSANDS of percent left. What is calculating the percent and WHY is it so high? I have one asset reporting over 1,000,000% remaining! What have I done wrong???

Below is the code: (screenshot attached)

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName As [Printer Name],
tblAssetCustom.Custom11 As [MT Asset Tag],
tblAssets.IPAddress As [IP Address],
tsysIPLocations.IPLocation As [Facility Location],
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
Cast(tblCustDevPrinter.TonerRemaining As nvarchar) +
'%' As RemainingPercentage,
tblCustDevPrinter.Tonername As [Toner Type],
tblCustDevPrinter.TonerColorName As [Toner Color]
From tblCustDevPrinter
Inner Join tblAssets On tblCustDevPrinter.AssetID = tblAssets.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where (tblAssets.IPAddress Like '10.1.2.%' And tsysAssetTypes.AssetTypename =
'Printer' And tblCustDevPrinter.TonerMaximum <> 0 And
tblCustDevPrinter.TonerMaximum > 0 And tblCustDevPrinter.TonerRemaining <> 0
And Floor(tblCustDevPrinter.TonerRemaining / (Case
When tblCustDevPrinter.TonerMaximum = 0 Then 1
Else tblCustDevPrinter.TonerMaximum
End) * 100) <= 30 And Floor(tblCustDevPrinter.TonerRemaining / (Case
When tblCustDevPrinter.TonerMaximum = 0 Then 1
Else tblCustDevPrinter.TonerMaximum
End) * 100) >= 0 And tblState.Statename = 'Active') Or
(tblAssets.IPAddress Like '10.2.2.%') Or
(tblAssets.IPAddress Like '10.6.2.%')
Order By [Facility Location],
[Printer Name]
7 REPLIES 7
mhammond
Champion Sweeper
HOLY FRIJOLES BATMAN!!!

That just cleaned up that report A TON! WOW

That's really impressive - here's to hard work! :cheers:

It's so much cleaner and easier to read .. and it whittles it down to the NEEDED information.

MAGNAFIQUE!!!
RCorbeil
Honored Sweeper II
Another view:
Select Top 1000000
tblAssets.AssetID,
tblAssets.AssetName As [Printer Name],
tblAssetCustom.Custom11 As [MT Asset Tag],
tblAssets.IPAddress As [IP Address],
tsysIPLocations.IPLocation As [Facility Location],
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
Cast(Floor(c.TonerRemaining / (Case
When c.TonerMaximum = 0
Then 1
Else c.TonerMaximum
End) * 100) As nvarchar) + '%' As Cyan,
Cast(Floor(m.TonerRemaining / (Case
When m.TonerMaximum = 0
Then 1
Else m.TonerMaximum
End) * 100) As nvarchar) + '%' As Magenta,
Cast(Floor(y.TonerRemaining / (Case
When y.TonerMaximum = 0
Then 1
Else y.TonerMaximum
End) * 100) As nvarchar) + '%' As Yellow,
Cast(Floor(k.TonerRemaining / (Case
When k.TonerMaximum = 0
Then 1
Else k.TonerMaximum
End) * 100) As nvarchar) + '%' As Black
From
tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode

LEFT JOIN tblCustDevPrinter AS c ON c.AssetID = tblAssets.AssetID
AND (c.TonerColorName LIKE '%cyan%'
OR c.Tonername LIKE '%cyan%')
AND c.Tonername NOT LIKE '%fuser%'
AND c.Tonername NOT LIKE '%drum%'
AND c.Tonername NOT LIKE '%image%'
AND c.Tonername NOT LIKE '%maintenance%'
AND c.Tonername NOT LIKE '%transfer%'
AND c.Tonername NOT LIKE '%waste%'
AND c.Tonername NOT LIKE '%feed roller%'
AND c.Tonername NOT LIKE '%filter%'
And c.TonerMaximum <> 0
And c.TonerMaximum > 0
And c.TonerRemaining <> 0
AND Floor(c.TonerRemaining / (Case
When c.TonerMaximum = 0
Then 1
Else c.TonerMaximum
End) * 100) <= 30
AND Floor(c.TonerRemaining / (Case
When c.TonerMaximum = 0
Then 1
Else c.TonerMaximum
End) * 100) >= 0
LEFT JOIN tblCustDevPrinter AS M ON m.AssetID = tblAssets.AssetID
AND (m.TonerColorName LIKE '%magenta%'
OR m.Tonername LIKE '%magenta%')
AND m.Tonername NOT LIKE '%fuser%'
AND m.Tonername NOT LIKE '%drum%'
AND m.Tonername NOT LIKE '%image%'
AND m.Tonername NOT LIKE '%maintenance%'
AND m.Tonername NOT LIKE '%transfer%'
AND m.Tonername NOT LIKE '%waste%'
AND m.Tonername NOT LIKE '%feed roller%'
AND m.Tonername NOT LIKE '%filter%'
And m.TonerMaximum <> 0
And m.TonerMaximum > 0
And m.TonerRemaining <> 0
AND Floor(m.TonerRemaining / (Case
When m.TonerMaximum = 0
Then 1
Else m.TonerMaximum
End) * 100) <= 30
AND Floor(m.TonerRemaining / (Case
When m.TonerMaximum = 0
Then 1
Else m.TonerMaximum
End) * 100) >= 0
LEFT JOIN tblCustDevPrinter AS Y ON y.AssetID = tblAssets.AssetID
AND (y.TonerColorName LIKE '%yellow%'
OR y.Tonername LIKE '%yellow%')
AND y.Tonername NOT LIKE '%fuser%'
AND y.Tonername NOT LIKE '%drum%'
AND y.Tonername NOT LIKE '%image%'
AND y.Tonername NOT LIKE '%maintenance%'
AND y.Tonername NOT LIKE '%transfer%'
AND y.Tonername NOT LIKE '%waste%'
AND y.Tonername NOT LIKE '%feed roller%'
AND y.Tonername NOT LIKE '%filter%'
And y.TonerMaximum <> 0
And y.TonerMaximum > 0
And y.TonerRemaining <> 0
AND Floor(y.TonerRemaining / (Case
When y.TonerMaximum = 0
Then 1
Else y.TonerMaximum
End) * 100) <= 30
AND Floor(y.TonerRemaining / (Case
When y.TonerMaximum = 0
Then 1
Else y.TonerMaximum
End) * 100) >= 0
LEFT JOIN tblCustDevPrinter AS K ON k.AssetID = tblAssets.AssetID
AND (k.TonerColorName LIKE '%black%'
OR k.Tonername LIKE '%black%'
OR (k.TonerColorName = 'Toner' AND k.Tonername = 'Toner') )
AND k.Tonername NOT LIKE '%fuser%'
AND k.Tonername NOT LIKE '%drum%'
AND k.Tonername NOT LIKE '%image%'
AND k.Tonername NOT LIKE '%maintenance%'
AND k.Tonername NOT LIKE '%transfer%'
AND k.Tonername NOT LIKE '%waste%'
AND k.Tonername NOT LIKE '%feed roller%'
AND k.Tonername NOT LIKE '%filter%'
And k.TonerMaximum <> 0
And k.TonerMaximum > 0
And k.TonerRemaining <> 0
AND Floor(k.TonerRemaining / (Case
When k.TonerMaximum = 0
Then 1
Else k.TonerMaximum
End) * 100) <= 30
AND Floor(k.TonerRemaining / (Case
When k.TonerMaximum = 0
Then 1
Else k.TonerMaximum
End) * 100) >= 0
Where
( tblAssets.IPAddress Like '10.1.2.%'
And tsysAssetTypes.AssetTypename = 'Printer'
-- only records with at least one low toner/ink
AND ( c.AssetID IS NOT NULL
OR m.AssetID IS NOT NULL
OR y.AssetID IS NOT NULL
OR k.AssetID IS NOT NULL)
And tblState.Statename = 'Active')
Or (tblAssets.IPAddress Like '10.2.2.%')
Or (tblAssets.IPAddress Like '10.6.2.%')
Order By
[Facility Location],
[Printer Name]

I've probably gone a little overboard on the non-toner/ink filters, but I show instances in my inventory of TonerName LIKE %fuser% and TonerColorName = 'black', so I figure better to over-filter than under.
RCorbeil
Honored Sweeper II
Where
( tblAssets.IPAddress Like '10.1.2.%'
And tsysAssetTypes.AssetTypename = 'Printer'
And tblCustDevPrinter.TonerMaximum <> 0
And tblCustDevPrinter.TonerMaximum > 0
And tblCustDevPrinter.TonerRemaining <> 0
And Floor(tblCustDevPrinter.TonerRemaining / (Case
When tblCustDevPrinter.TonerMaximum = 0
Then 1
Else tblCustDevPrinter.TonerMaximum
End) * 100) <= 30
And Floor(tblCustDevPrinter.TonerRemaining / (Case
When tblCustDevPrinter.TonerMaximum = 0
Then 1
Else tblCustDevPrinter.TonerMaximum
End) * 100) >= 0
-- include explicitly-named toner/ink
AND ( tblCustDevPrinter.TonerColorName LIKE '%cyan%'
OR tblCustDevPrinter.TonerColorName LIKE '%magenta%'
OR tblCustDevPrinter.TonerColorName LIKE '%yellow%'
OR tblCustDevPrinter.TonerColorName LIKE '%black%'
OR tblCustDevPrinter.Tonername LIKE '%cyan%'
OR tblCustDevPrinter.Tonername LIKE '%magenta%'
OR tblCustDevPrinter.Tonername LIKE '%yellow%'
OR tblCustDevPrinter.Tonername LIKE '%black%'
)
-- exclude things that aren't toner/ink
AND tblCustDevPrinter.Tonername NOT LIKE '%fuser%'
AND tblCustDevPrinter.Tonername NOT LIKE '%drum%'
AND tblCustDevPrinter.Tonername NOT LIKE '%image%'
AND tblCustDevPrinter.Tonername NOT LIKE '%maintenance%'
AND tblCustDevPrinter.Tonername NOT LIKE '%transfer%'
AND tblCustDevPrinter.Tonername NOT LIKE '%waste%'
AND tblCustDevPrinter.Tonername NOT LIKE '%feed roller%'
AND tblCustDevPrinter.Tonername NOT LIKE '%filter%'
And tblState.Statename = 'Active')
mhammond
Champion Sweeper
THANK YOU!!!!

That definitely eliminated the "THOUSANDS" and "MILLIONS" percent entries in the report.

Now, all I have to do is figure a way to only show "black", "cyan", "magenta", and "yellow" as the toner name that is low (or exclude toner types waste toner or fuser or maintenance kit); because now, it's hitting several MFPs, but it is including those extra parts.

I'm not too worried about getting TOO granular, as in MFPs: say, a yellow is at or below 30% but the rest of the colors are well above, so they show on the report as above 30% remaining; it makes sense why those entries duplicate - since you can't really separate them from the machine.
RCorbeil
Honored Sweeper II
Substitute for your RemainingPercentage.
Select Top 1000000
tblAssets.AssetID,
tblAssets.AssetName As [Printer Name],
tblAssetCustom.Custom11 As [MT Asset Tag],
tblAssets.IPAddress As [IP Address],
tsysIPLocations.IPLocation As [Facility Location],
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
Convert( VarChar(10),
Cast( Case
When tblCustDevPrinter.TonerMaximum > 0
Then tblCustDevPrinter.TonerRemaining / tblCustDevPrinter.TonerMaximum * 100
End
As Decimal(6,2))) + '%' As RemainingPercentage,
...

The funny thing is, you've handled it in your WHERE clause. You could just as easily copy your code from there to use as the RemainingPercentage value.
Select Top 1000000
tblAssets.AssetID,
tblAssets.AssetName As [Printer Name],
tblAssetCustom.Custom11 As [MT Asset Tag],
tblAssets.IPAddress As [IP Address],
tsysIPLocations.IPLocation As [Facility Location],
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
Cast(Floor(tblCustDevPrinter.TonerRemaining / (Case
When tblCustDevPrinter.TonerMaximum = 0
Then 1
Else tblCustDevPrinter.TonerMaximum
End) * 100) As nvarchar) + '%' As RemainingPercentage,
...
mhammond
Champion Sweeper
Apologies for not following the logic, but where would I place this code? Or is this replacing a string?
RCorbeil
Honored Sweeper II
Unfortunately, not all printers report their consumables as percentages. Here's how I've handled it on a few of my reports:

Convert( VarChar(10),
Cast( Case
When tblCustDevPrinter.TonerMaximum > 0
Then tblCustDevPrinter.TonerRemaining / tblCustDevPrinter.TonerMaximum * 100
End
As Decimal(6,2))) + '%' As Pct