Export to Excel with ADO.NET query could result in wrong column types
kbt164366
PRODUCTIssue
When you have a SmartObject based on an SQL Server Service instance, and you use the K2 API to execute an ADO.NET query on that SmartObject, it will then return the data from the SQL table.
Custom Service Brokers (like the K2NE Service Broker) use this functionality to export to Excel for example.
In this case, when decimal columns in SQL are returned via the ADO.NET provider, then these values might be returned as text (or string).
Symptoms
In the SQL table you would have a column of type "Decimal", for example a column with the name "CertaintyPercentage" or "FlexibilityPercentage".
You could use the below ADO.NET query to return this data, but the columns of type "Decimal" would return as type "Text" in K2.
Subsequently, if you then export that data in K2 to an Excel spreadsheet, that column would result in being of type "General" or "Text" in Excel.
P.Id AS PlanningId,
P.TaskId,
CertaintyPercentage,
FlexibilityPercentage,
.
.
.
FROM K2_SmartObject1 AS P
LEFT JOIN K2_SmartObject2 AS PD ON P.Id = PD.PlanningId
INNER JOIN K2_SmartObject3.MethodName AS xxx ON xxx.yyy = P.yyy
Resolution
The solution here is to use the CAST statement in the ADO.NET query to cast the data type from one to another. For example, this query will cast the type correctly to decimal:
P.Id AS PlanningId,
P.TaskId,
CAST(P.CertaintyPercentage AS INT) AS CertaintyPercentage,
CAST(P.FlexibilityPercentage AS INT) AS FlexibilityPercentage,
.
.
.
FROM K2_SmartObject1 AS P
LEFT JOIN K2_SmartObject2 AS PD ON P.Id = PD.PlanningId
INNER JOIN K2_SmartObject3.MethodName AS xxx ON xxx.yyy = P.yyy
