→ 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: 
akira
Engaged Sweeper II
Hello,

I have created a report that involves all the PC's on the network. Is there a way I can get the total sum of the assets into the Report? I usually export my reports to Excel, so would I just find the total asset sum after I export it to Excel?

Or can LanSweeper add the sum somewhere in the report?

Thanks!
1 ACCEPTED SOLUTION
AZHockeyNut
Champion Sweeper III
this one cannot do easily when you are already grouping by etc. Aggregate functions are not a strength of SQL, so you could, export the report to excel and you can then do what you want.

alternatively, you could create look at the URL when the report is run, it will have a name like web50128338923 or something, that is the name of the VIEW in sql. You can then, in another report, query that report with a select count (distinct assetid) from VIEWNAME fill in your viewname there.

you could make a report of counts off of several other reports and then tie it to a dashboard widget I believe.
sorry I cannot help much more than that.

View solution in original post

5 REPLIES 5
AZHockeyNut
Champion Sweeper III
this one cannot do easily when you are already grouping by etc. Aggregate functions are not a strength of SQL, so you could, export the report to excel and you can then do what you want.

alternatively, you could create look at the URL when the report is run, it will have a name like web50128338923 or something, that is the name of the VIEW in sql. You can then, in another report, query that report with a select count (distinct assetid) from VIEWNAME fill in your viewname there.

you could make a report of counts off of several other reports and then tie it to a dashboard widget I believe.
sorry I cannot help much more than that.
akira
Engaged Sweeper II
YES! I meant "count" haha

I apologize - I am not too familiar with SQL and its terminology.

I would like the TOTAL COUNT of my assets in my report.
AZHockeyNut
Champion Sweeper III
oh SUM is a math function to perform Addition, what I think you are really asking for is a COUNT of records returned correct? meaning your query returns 100 rows, or 100 assets in this case.
akira
Engaged Sweeper II
Thank you for your reply!

I tried adding that function to my report, but I kept getting SELECT errors. Here is my report below:

I just want the total sum of the assets, but I cannot integrate it into my report

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName As Asset,
tblAssets.Domain,
tblAssetCustom.Manufacturer As Make,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.Username As [User],
tblAssets.Firstseen As [First Seen],
tblAssets.Lastseen As [Last Seen],
tblAssetCustom.PurchaseDate As [PC Purchase Date],
tblAssetCustom.Warrantydate As [Warranty End Date],
DateAdd(year, 5, tblAssetCustom.PurchaseDate) As [Replacement Date],
Max(tblQuickFixEngineering.Lastchanged) As [Last Patch Detected]
From tblAssets
Inner Join tblQuickFixEngineering On tblAssets.AssetID =
tblQuickFixEngineering.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Group By tsysOS.Image,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname,
tblAssets.Username,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate
Order By Asset
AZHockeyNut
Champion Sweeper III
https://www.w3schools.com/sqL/sql_count_avg_sum.asp

the function is listed there with the syntax. SQL can return it just depends how/where you want it.