Request for help with Chart

[GMFDE]

I've got a chart report that lists all the browsers by version. What I want is to combine the different versions so that the X axis is simply

1. Internet Explorer 4721
2. Firefox 275
3. Chrome 74
4. Opera 12

As it stands now, the chart has about 35 entries in the X axis due to all the various versions. Below is the code I'm working with.

Any help is appreciated.

Select Top 1000000 tblSoftwareUni.softwareName As Software,
Count(tblSoftwareUni.softwareName) As Count
From tblSoftware
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Where (tblSoftwareUni.SoftwarePublisher Like '%Microsoft%') Or
(tblSoftwareUni.SoftwarePublisher Like '%Mozilla%') Or
(tblSoftwareUni.SoftwarePublisher Like '%Google%')
Group By tblSoftwareUni.softwareName
Having (tblSoftwareUni.softwareName Like '%Windows Internet Explorer%') Or
(tblSoftwareUni.softwareName Like '%Firefox%') Or
(tblSoftwareUni.softwareName Like '%Chrome')
Order By Count Desc

[Susan_A · Accepted solution]

I moved this topic from the Report Center section of the forum to Report Requests & Questions. Please note that the Report Center is for posting ready-to-use reports, not questions.

Your report doesn't actually include the software version field. It only lists software names. If you are seeing software versions, the version numbers are part of the software names as listed in Add/Remove Programs on the client machines. Mozilla for instance does tend to include the software version in the name. You could try using cases to "clean up" the software names and remove the version numbers, as seen below, but this may not be 100% accurate.

Select Top 1000000 Case
    When tblSoftwareUni.softwareName Like 'windows internet explorer%'
    Then 'Internet Explorer'
    When tblSoftwareUni.softwareName Like 'mozilla firefox %.%' Then 'Firefox'
    When tblSoftwareUni.softwareName Like 'google chrome%' Then 'Chrome'
    When tblSoftwareUni.softwareName Like 'opera%' Then 'Opera'
    Else tblSoftwareUni.softwareName End As Browser,
  Count(tblSoftware.SoftwareID) As Count
From tblSoftware
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblSoftware.AssetID
Where (tblSoftwareUni.softwareName Like 'windows internet explorer%' And
  tblSoftwareUni.softwareName Not Like '%administration kit%' And
  tblAssetCustom.State = 1) Or
  (tblSoftwareUni.softwareName Like 'mozilla firefox %.%' And
  tblAssetCustom.State = 1) Or
  (tblSoftwareUni.softwareName Like 'google chrome%' And tblAssetCustom.State =
  1) Or
  (tblSoftwareUni.softwareName Like 'opera%' And tblAssetCustom.State = 1)
Group By Case
    When tblSoftwareUni.softwareName Like 'windows internet explorer%'
    Then 'Internet Explorer'
    When tblSoftwareUni.softwareName Like 'mozilla firefox %.%' Then 'Firefox'
    When tblSoftwareUni.softwareName Like 'google chrome%' Then 'Chrome'
    When tblSoftwareUni.softwareName Like 'opera%' Then 'Opera'
    Else tblSoftwareUni.softwareName End
Order By Count Desc