This article was created in response to a support issue logged with K2. The content may include typographical errors and may be revised at any time without notice This article is not considered official documentation for K2 software and is provided "as is" with no warranties.

Issue

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.

SELECT
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:

SELECT
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

The CAST() is the bit you need.