Request 2 values from one attribute

[erankave]

I need to show two different values of the same attribute in one report.

To be more specific. In the report we want to see

• the currently installed Office Client

LIKE 'Microsoft Office Professional%' OR
'Microsoft Office Standard%' OR
'Microsoft Office 365%')

as well as the currently installed

• Microsoft Dynamics Addon

= 'Microsoft Dynamics 365 for Microsoft Office Outlook'

I would really appreciate it, if someone can help us.

[Esben_D · Accepted solution]

Easiest method would be to create a subquery for Microsoft dynamics. The first part is the built-in software report with the first criteria you mentioned.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysAssetTypes.AssetTypename As AssetType,
  tblAssets.Username,
  tblAssets.Userdomain,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblSoftwareUni.softwareName As Software,
  tblSoftware.softwareVersion As Version,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  MSDynamic.[Microsoft Dynamics],
  tblSoftware.Lastchanged
From tblAssets
  Inner Join (Select Top 1000000 tblAssets.AssetID,
        tblSoftwareUni.softwareName As [Microsoft Dynamics]
      From tblAssets
        Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
        Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
      Where
        tblSoftwareUni.softwareName =
        'Microsoft Dynamics 365 for Microsoft Office Outlook') As MSDynamic On
    MSDynamic.AssetID = tblAssets.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where (tblSoftwareUni.softwareName Like 'Microsoft Office Professional%' Or
    tblSoftwareUni.softwareName Like 'Microsoft Office Standard%' Or
    tblSoftwareUni.softwareName Like 'Microsoft Office 365%') And
  tblAssetCustom.State = 1

[erankave]

Thank you very much for your effort. I really appreciate it. That is exactly what I had in mind.

Charles.X wrote:
Easiest method would be to create a subquery for Microsoft dynamics. The first part is the built-in software report with the first criteria you mentioned.