After spending some time Moving Sms / Sccm Reports to SQL Reporting Services,
I decided that it would be much better to create the reports from scratch with Visual Studio.
 
Indeed, moving the Sql reports is easy, but you won’t be able to edit them via the Report Builder or Visual Studio,
so customization and ‘Look and feel’ are at their minimum.
 
Although it took me a couple of days to Configure Reporting Services, give appropriate permissions,
create the Datasets, then finally check how to use the Reporting Wizard to create the reports,
I ended up impressed by the quality of reports that you can get in no time.
 
Here is the first report I did about Inventoried Software in the company: I chose to group the rows by Full version,
with subtotals and you can simply click on a header row to get the full list by minor version.
 
I will try to post more information about the use of VS for creating the reports when I get time.

Of course, the most important point in the report is the query, so here is the one i created based
on the exceptions in my company.
SELECT DISTINCT 
LEFT(v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedName, 13) AS ‘Product’,
v_R_System_Valid.Netbios_Name0, 
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.VersionMajor0,
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher, 
v_R_System_Valid.Resource_Domain_OR_Workgr0,
DATEDIFF(day, v_GS_WORKSTATION_STATUS.LastHWScan, GETDATE()) AS Last_Scan, 
v_R_System_Valid.User_Name0, v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ResourceID,      v_GS_INSTALLED_SOFTWARE_CATEGORIZED.RegisteredUser0, 
v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ARPDisplayName0
FROM        
v_R_System_Valid INNER JOIN
v_GS_INSTALLED_SOFTWARE_CATEGORIZED ON v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ResourceID =
v_R_System_Valid.ResourceID INNER JOIN
v_GS_WORKSTATION_STATUS ON v_R_System_Valid.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID
WHERE    
(v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ProductName0 LIKE ‘%Acrobat%’) AND 
(v_GS_INSTALLED_SOFTWARE_CATEGORIZED.NormalizedPublisher LIKE ‘Adobe%’) AND 
(NOT (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ProductName0 LIKE ‘%tuner%’)) AND 
(NOT (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ProductName0 LIKE ‘%reader%’)) AND (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.VersionMajor0 > ‘4’) AND 
(v_R_System_Valid.Resource_Domain_OR_Workgr0 = ‘MYDOMAIN’) AND
(NOT (v_GS_INSTALLED_SOFTWARE_CATEGORIZED.RegisteredUser0 IS NULL))
AND (v_R_System_Valid.Netbios_Name0 NOT LIKE ‘OTHERCOMPANYPC%’)
ORDER BY
v_R_System_Valid.Netbios_Name0, v_GS_INSTALLED_SOFTWARE_CATEGORIZED.ARPDisplayName0
Advertisements