Report that displays more than one software and version number

[JohnLewis]

Is there a way to list more than one application in a report?
Looking to show Office with version # and Internet Explorer with version # for AD OUs' in one report....

Current report is:
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tblAssetCustom1.Model,
tblAssetCustom1.Serialnumber,
tsysOS.OSname,
tSoftware.softwareName,
tSoftware.softwareVersion,
tsysIPLocations.IPLocation,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssetCustom1.Warrantydate,
tblAssetCustom1.PurchaseDate,
tblAssetCustom1.Manufacturer,
tblADComputers.OU
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID And
tblADComputers.OU In
('OU=Examlpe 1,DC=com',
'OU=Example 2,DC=com',
'OU=Example 3,DC=com',
'OU=Example 4,DC=com')
Inner Join tblAssetCustom tblAssetCustom1 On tblAssets.AssetID =
tblAssetCustom1.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Left Join (Select tblSoftware.AssetID,
tblSoftwareUni.softwareName,
tblSoftwareUni.SoftwarePublisher,
tblSoftware.softwareVersion
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where (tblSoftwareUni.softwareName Like '%2003%') Or
(tblSoftwareUni.softwareName Like '%2007%') Or
(tblSoftwareUni.softwareName Like '%2010%') Or
(tblSoftwareUni.softwareName Like '%2013%')) tSoftware
On tSoftware.AssetID = tblAssets.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tblState On tblState.State = tblAssetCustom.State
Where (tSoftware.softwareName Like '%Microsoft Office standard%') Or
(tSoftware.softwareName Like '%Microsoft Office Professional%')
Order By tblAssets.AssetName,
tSoftware.softwareName


And have tried this but no luck...

Where (tSoftware.softwareName Like '%Microsoft Office standard%') Or
(tSoftware.softwareName Like '%Microsoft Office Professional%' And
tSoftware.softwareName Like '%Windows Internet Explorer 11%')

[RCorbeil · Accepted solution]

For simplification, you might consider consolidating your software filtering: either do it all in the sub-SELECT or all on the main SELECT. From your phrasing, the split filtering appears to be what's giving you trouble.

In your sub-SELECT, you're filtering for any software names that contain "2003", "2007", "2010" or "2013". You've eliminated Internet Explorer right there.

In the outer SELECT, you're taking the results of the 2003/2007/2010/2013 filter and further filtering that for software names that contain "Microsoft Office Standard" or "Microsoft Office Professional". Fair enough.

What you might want to consider doing instead -- either in the sub-SELECT or in the main SELECT, but for simplicity, not both:

  (
    ( 'Microsoft Office' ) AND ( one of select years )
  )
  OR
  ( '%Windows Internet Explorer 11%' )

If you were to replace the sub-SELECT with joins to tblSoftware and tblSoftwareUni, you could put something like this in the main WHERE clause:

  (
    (
      (tblSoftwareUni.softwareName Like '%Microsoft Office standard%') Or
      (tblSoftwareUni.softwareName Like '%Microsoft Office Professional%')
    )
    AND
    (
      (tblSoftwareUni.softwareName Like '%2003%') Or
      (tblSoftwareUni.softwareName Like '%2007%') Or
      (tblSoftwareUni.softwareName Like '%2010%') Or
      (tblSoftwareUni.softwareName Like '%2013%')
    )
  )
  OR
  (
    tblSoftwareUni.softwareName Like '%Windows Internet Explorer 11%'
  )

You could do the same thing in the sub-SELECT instead of the outer SELECT and achieve the same results.

Order of operations gives AND precedence over OR, so be careful how you parenthesize your conditions, but it should work.