Report Builder Help?

scottmd's picture
scottmd
October 8th, 2008

Asset management question here - I am a report novice, and I need to make a report which will give me a report showing the primary logged on user (which is a default report) combined with a computer make and model breakdown, the purpose of which is to show who in our organization needs to have thier base systems upgraded. What is the easiest way to make a useful list?

Filed under: ,
0 votes
lotsill's picture
lotsill
1 year 6 weeks ago

Here is a quick query, it

Here is a quick query, it will give you model, serial number and primary user.

SELECT T1.[Computer Model] AS 'Computer Model', T1.[Serial Number] AS 'Serial Number', T0.[User] AS 'User' 
FROM [Inv_AeX_AC_Primary_User] T0 INNER JOIN [Inv_AeX_HW_Serial_Number] T1 ON T0.[_ResourceGuid] = T1.[_ResourceGuid] 
WHERE T1.[Serial Number] LIKE '%'
0 votes
scottmd's picture
scottmd
1 year 6 weeks ago

When I run a query with

When I run a query with that expression, it gives me an error - are there additional parameters?

Incorrect syntax near the keyword 'SELECT'.

0 votes
sfletcher's picture
sfletcher
1 year 6 weeks ago

SQL Works

Maybe you had some superfluous character or line before the code when you cut and pasted the SQL into your report window.

0 votes
Eshwar's picture
Eshwar
1 year 5 weeks ago

working perfectly

The query is simply working fine. But i think we need to display unique records. For that i used DISTINCT and also MACHINE name just in case you want see that in the report

SELECT	DISTINCT(T0.[User]) AS 'User', T2.[Name] AS 'Machine Name', T1.[Computer Model] AS 'Computer Model',T1.[Serial Number] AS 'Serial Number' 
FROM [Inv_AeX_AC_Primary_User] T0 
INNER JOIN [Inv_AeX_HW_Serial_Number] T1 ON T0.[_ResourceGuid] = T1.[_ResourceGuid] 
JOIN vComputer T2 ON T1.[_ResourceGuid]  = T2.Guid
WHERE T1.[Serial Number] LIKE '%'
AND T0.[Month] =  datename(month, getdate())

Thanks,
Eshwar

+3 (3 votes)