Created :
Written by Support InfoBridge
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
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
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