Concatenation in Reports

[Arjan]

Hello support,

I've been trying to merge 2 database fields into one field using the SQL operator 'CONCAT'
My goal here is to have the software name + version in one field and generate a 'chart report' for which I can put a Pie chart on my dashboard.

Select CONCAT(tblSoftwareUni.softwareName, tblSoftware.softwareVersion) From tblSoftwareUni) As name_version

When the report generator updates, it shows me the following error message.
Error while saving report: "'CONCAT' is not a recognized built-in function name."


Is there any other way I should use to achieve this?

[Arjan]

Hi Bruce,

Thanks a lot, that does the trick for me 🙂

[Bruce_B · Accepted solution]

CONCAT cannot be used in SQL Compact, but you can achieve the same thing by doing tblSoftwareUni.softwareName + ' ' + tblSoftware.softwareVersion. If the target fields aren't varchars you'll need to cast them as well though, but that isn't necessary in this case.
The report below should match the Chart report you were describing.

Select Top 1000000 tblSoftwareUni.softwareName + ' ' +
  tblSoftware.softwareVersion As Software,
  Count(tblAssets.AssetID) As AssetCount
From tblAssets
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Group By tblSoftwareUni.softwareName + ' ' + tblSoftware.softwareVersion