Include just Office Program version with unique PC

[harringg]

Is there an easy way to include just the Version of MS Office in the existing report?

Computer1|Department|...|

SELECT TOP 1000000 tblcomputers.computername,
                   tblcomputers.computerunique,
                   web40osname.compimage        AS icon,
                   web40osname.osname,
                   tblcomputers.sp              AS [Service Pack],
                   tblcompcustom.location,
                   tblcompcustom.department     AS [Research Unit],
                   tblcompcustom.custom1        AS [User Name],
                   tsyschassistypes.chassisname AS Type,
                   tblcompcustom.barcode        AS [Inventory Number],
                   tblbios.releasedate,
                   tblsystemenclosure.serialnumber
FROM   tblcomputers
       LEFT OUTER JOIN web40processorcapacity
                    ON tblcomputers.computername =
                       web40processorcapacity.computername
       INNER JOIN web40activecomputers
               ON tblcomputers.computername = web40activecomputers.computername
       LEFT JOIN web40correctmemory
              ON web40correctmemory.computername = tblcomputers.computername
       LEFT JOIN web40osname
              ON web40osname.computername = tblcomputers.computername
       LEFT JOIN tblcomputersystemproduct
              ON tblcomputers.computername =
                 tblcomputersystemproduct.computername
       LEFT JOIN tblcompcustom
              ON tblcomputers.computername = tblcompcustom.computername
       LEFT JOIN tblbios
              ON tblcomputers.computername = tblbios.computername
       LEFT JOIN tblsystemenclosure
              ON tblcomputers.computername = tblsystemenclosure.computername
       LEFT JOIN tsyschassistypes
              ON tsyschassistypes.chassistype = tblsystemenclosure.chassistypes
WHERE  tblcompcustom.department LIKE '%Unit%'
ORDER  BY dbo.tblcomputers.computername  

The following gives me any instance of "Office"

Computer1|Department|...|Office 2007
Computer1|Department|...|Office File Validation Add-In
Computer1|Department|...|Compatibility Pack for Office 2007

SELECT TOP 1000000 tblcomputers.computername,
                   tblcomputers.computerunique,
                   web40osname.compimage        AS icon,
                   web40osname.osname,
                   tblcomputers.sp              AS [Service Pack],
                   tblcompcustom.location,
                   tblcompcustom.department     AS [Research Unit],
                   tblcompcustom.custom1        AS [User Name],
                   tsyschassistypes.chassisname AS Type,
                   tblcompcustom.barcode        AS [Inventory Number],
                   tblbios.releasedate,
                   tblsystemenclosure.serialnumber,
                   tblsoftware.softwarename
FROM   tblcomputers
       LEFT OUTER JOIN web40processorcapacity
                    ON tblcomputers.computername =
                       web40processorcapacity.computername
       INNER JOIN web40activecomputers
               ON tblcomputers.computername = web40activecomputers.computername
       LEFT JOIN web40correctmemory
              ON web40correctmemory.computername = tblcomputers.computername
       LEFT JOIN web40osname
              ON web40osname.computername = tblcomputers.computername
       LEFT JOIN tblcomputersystemproduct
              ON tblcomputers.computername =
                 tblcomputersystemproduct.computername
       LEFT JOIN tblcompcustom
              ON tblcomputers.computername = tblcompcustom.computername
       LEFT JOIN tblbios
              ON tblcomputers.computername = tblbios.computername
       LEFT JOIN tblsystemenclosure
              ON tblcomputers.computername = tblsystemenclosure.computername
       LEFT JOIN tsyschassistypes
              ON tsyschassistypes.chassistype = tblsystemenclosure.chassistypes
       INNER JOIN tblsoftware
               ON tblcomputers.computername = tblsoftware.computername
WHERE  tblcompcustom.department LIKE '%Unit%'
       AND tblsoftware.softwarename LIKE '%office%'
ORDER  BY dbo.tblcomputers.computername 

[harringg]

Perfect. Thanks for the assistance.

[Hemoco · Accepted solution]

Could you try running the report as shown below instead.

Select Top 1000000 tblComputers.Computername, tblComputers.ComputerUnique, Web40OSName.Compimage As icon, Web40OSName.OSname, tblComputers.SP As [Service Pack], tblCompCustom.Location, tblCompCustom.Department As [Research Unit], tblCompCustom.Custom1 As [User Name], TsysChassisTypes.ChassisName As Type, tblCompCustom.BarCode As [Inventory Number], Convert(Varchar(10),tblBIOS.ReleaseDate,20) As ReleaseDate, tblSystemEnclosure.SerialNumber, tblSoftware.softwareName, tblSoftware.softwareName As softwareName1 From tblComputers Left Outer Join web40ProcessorCapacity On tblComputers.Computername = web40ProcessorCapacity.Computername Inner Join web40ActiveComputers On tblComputers.Computername = web40ActiveComputers.Computername Left Join web40CorrectMemory On web40CorrectMemory.Computername = tblComputers.Computername Left Join Web40OSName On Web40OSName.Computername = tblComputers.Computername Left Join tblComputerSystemProduct On tblComputers.Computername = tblComputerSystemProduct.Computername Left Join tblCompCustom On tblComputers.Computername = tblCompCustom.Computername Left Join tblBIOS On tblComputers.Computername = tblBIOS.Computername Left Join tblSystemEnclosure On tblComputers.Computername = tblSystemEnclosure.Computername Left Join TsysChassisTypes On TsysChassisTypes.Chassistype = tblSystemEnclosure.ChassisTypes Inner Join tblSoftware On tblComputers.Computername = tblSoftware.ComputerName Where tblCompCustom.Department Like '%Unit%' And tblSoftware.softwareName Like 'microsoft office%' And tblSoftware.softwareName Like '%20%' Order By dbo.tblcomputers.computername

[harringg]

When in the Report builder Results tab, it's the date, when in the Web Interface, it's the date/time and when I export to Excel it's date/time

[Hemoco]

You could try something like this:

Select Top 1000000 tblComputers.Computername, tblComputers.ComputerUnique, Web40OSName.Compimage As icon, Web40OSName.OSname, tblComputers.SP As [Service Pack], tblCompCustom.Location, tblCompCustom.Department As [Research Unit], tblCompCustom.Custom1 As [User Name], TsysChassisTypes.ChassisName As Type, tblCompCustom.BarCode As [Inventory Number], tblBIOS.ReleaseDate, tblSystemEnclosure.SerialNumber, tblSoftware.softwareName From tblComputers Left Outer Join web40ProcessorCapacity On tblComputers.Computername = web40ProcessorCapacity.Computername Inner Join web40ActiveComputers On tblComputers.Computername = web40ActiveComputers.Computername Left Join web40CorrectMemory On web40CorrectMemory.Computername = tblComputers.Computername Left Join Web40OSName On Web40OSName.Computername = tblComputers.Computername Left Join tblComputerSystemProduct On tblComputers.Computername = tblComputerSystemProduct.Computername Left Join tblCompCustom On tblComputers.Computername = tblCompCustom.Computername Left Join tblBIOS On tblComputers.Computername = tblBIOS.Computername Left Join tblSystemEnclosure On tblComputers.Computername = tblSystemEnclosure.Computername Left Join TsysChassisTypes On TsysChassisTypes.Chassistype = tblSystemEnclosure.ChassisTypes Inner Join tblSoftware On tblComputers.Computername = tblSoftware.ComputerName Where tblCompCustom.Department Like '%Unit%' And tblSoftware.softwareName Like 'microsoft office%' And tblSoftware.softwareName Like '%20%' Order By dbo.tblcomputers.computername

[harringg]

That got me closer. I can dump to Excel and Filter from there to show only the titles I need.


Quick followup to that script. It does show the date field in the BIOS with the time as well.

2009-8-25 00:00

Is there a way to show only:

2009-8-25 on export?

[Hemoco]

harringg wrote:
Quick followup to that script. It does show the date field in the BIOS with the time as well.

Time should not be included in the tblBIOS.ReleaseDate field. Could you double-check the report results.