How to Group By an Integer field

Last update:
Created :
Written by Thomas Speekenbrink

Overview:

Normally, it isnt possible to use the Group By functionality using an integer / numeric field from the database. This will be possible by converting it to a string value. Find out how below:

How to:

Example of SQL View:
SELECT TOP (100) PERCENT dbo.View_TmpTicketOwnerProd.Product, crm7.EJUSER.firstname, crm7.EJUSER.lastname, crm7.EJUSER.id
FROM dbo.View_TmpTicketOwnerProd INNER JOIN
crm7.EJUSER ON dbo.View_TmpTicketOwnerProd.owned_by = crm7.EJUSER.id
 
In the above example, you're able to create a Panel chart by using the group By with all the field, except for the crm7.EJUSER.id field, becuase this field is an integer / numeric type.
 
To be able to use the Group By functionality, we can use the CAST function from SQL. So we have to change the SQL script a bit, so it looks like this:
 
Example of a SQL View where in the Infopanels, we can group by the ID field:
SELECT TOP (100) PERCENT dbo.View_TmpTicketOwnerProd.Product, crm7.EJUSER.firstname, crm7.EJUSER.lastname, CAST(crm7.EJUSER.id AS varchar(10)) AS ID
FROM dbo.View_TmpTicketOwnerProd INNER JOIN
crm7.EJUSER ON dbo.View_TmpTicketOwnerProd.owned_by = crm7.EJUSER.id