Creating a custom function to convert text to decimal
22 June, 2012
I am trying to create a custom function that will take a text argument, read from an xml file and based of what it reads either return the argument as text or converted to a decimal.
So far i have not been able to even get a custom function working that converts text to decimal, i have found the documentation on creating custom function a little confusing.
Based on the documentation i have tried adding the following code to C:MentorReportsappserver.
Convert String to Decimal
1
String
text
convert(decimal(15,0),$1)
SQLServer
numeric
From what i had read on creating custom functions Convert String to Decimal should show up under formula templates in calculated fields, however my changes to this file seem to do nothing.
Any help on this issue would be much appreciated.
So far i have not been able to even get a custom function working that converts text to decimal, i have found the documentation on creating custom function a little confusing.
Based on the documentation i have tried adding the following code to C:MentorReportsappserver.
Convert String to Decimal
1
String
text
convert(decimal(15,0),$1)
SQLServer
numeric
From what i had read on creating custom functions Convert String to Decimal should show up under formula templates in calculated fields, however my changes to this file seem to do nothing.
Any help on this issue would be much appreciated.
Sorry i mean i tried adding that code to C:MentorReportsappserverwebappsROOTWEB-INFcustom-functions.xml
My custom function now shows up in the formula templates section, but when i run a test report using it i am told:
Error retrieving results
An error occurred retrieving the results from the database.
Please check the SQL syntax and try again.
sql syntax
SELECT DISTINCT
"WorkOrder View"."35",
cast ( ISNULL(NULLIF("WorkOrder View"."35", ''), '0.0') as numeric(18,4))
FROM (
Select OrderNumber, StartDate, EndDate,
MAX(CASE FieldNumber WHEN '4' THEN FieldValue END) as "4",
MAX(CASE FieldNumber WHEN '5' THEN FieldValue END) as "5",
MAX(CASE FieldNumber WHEN '6' THEN FieldValue END) as "6",
MAX(CASE FieldNumber WHEN '18' THEN FieldValue END) as "18",
MAX(CASE FieldNumber WHEN '19' THEN FieldValue END) as "19",
MAX(CASE FieldNumber WHEN '20' THEN FieldValue END) as "20",
MAX(CASE FieldNumber WHEN '21' THEN FieldValue END) as "21",
MAX(CASE FieldNumber WHEN '22' THEN FieldValue END) as "22",
MAX(CASE FieldNumber WHEN '23' THEN FieldValue END) as "23",
MAX(CASE FieldNumber WHEN '24' THEN FieldValue END) as "24",
MAX(CASE FieldNumber WHEN '25' THEN FieldValue END) as "25",
MAX(CASE FieldNumber WHEN '26' THEN FieldValue END) as "26",
MAX(CASE FieldNumber WHEN '27' THEN FieldValue END) as "27",
MAX(CASE FieldNumber WHEN '28' THEN FieldValue END) as "28",
MAX(CASE FieldNumber WHEN '29' THEN FieldValue END) as "29",
MAX(CASE FieldNumber WHEN '30' THEN FieldValue END) as "30",
MAX(CASE FieldNumber WHEN '31' THEN FieldValue END) as "31",
MAX(CASE FieldNumber WHEN '32' THEN FieldValue END) as "32",
MAX(CASE FieldNumber WHEN '33' THEN FieldValue END) as "33",
MAX(CASE FieldNumber WHEN '34' THEN FieldValue END) as "34",
MAX(CASE FieldNumber WHEN '35' THEN FieldValue END) as "35",
MAX(CASE FieldNumber WHEN '36' THEN FieldValue END) as "36",
MAX(CASE FieldNumber WHEN '37' THEN FieldValue END) as "37",
MAX(CASE FieldNumber WHEN '38' THEN FieldValue END) as "38",
MAX(CASE FieldNumber WHEN '39' THEN FieldValue END) as "39",
MAX(CASE FieldNumber WHEN '40' THEN FieldValue END) as "40"
FROM
(Select WorkOrder.WorkOrderKey, WorkOrder.OrderNumber, WorkOrder.StartDate, WorkOrder.EndDate, WorkOrderCustomFields.FieldNumber, WorkOrderCustomFields.FieldValue from WorkOrder
INNER JOIN WorkOrderCustomFields ON WorkOrderCustomFields.WorkOrderKey = WorkOrder.WorkOrderKey) as WorkOrders
group by OrderNumber, StartDate, EndDate
) AS "WorkOrder View"
custom function
Convert String to Decimal
1
String
text
cast ( ISNULL(NULLIF($1, ''), '0.0') as numeric(18,4))
SQLServer
numeric
Error retrieving results
An error occurred retrieving the results from the database.
Please check the SQL syntax and try again.
sql syntax
SELECT DISTINCT
"WorkOrder View"."35",
cast ( ISNULL(NULLIF("WorkOrder View"."35", ''), '0.0') as numeric(18,4))
FROM (
Select OrderNumber, StartDate, EndDate,
MAX(CASE FieldNumber WHEN '4' THEN FieldValue END) as "4",
MAX(CASE FieldNumber WHEN '5' THEN FieldValue END) as "5",
MAX(CASE FieldNumber WHEN '6' THEN FieldValue END) as "6",
MAX(CASE FieldNumber WHEN '18' THEN FieldValue END) as "18",
MAX(CASE FieldNumber WHEN '19' THEN FieldValue END) as "19",
MAX(CASE FieldNumber WHEN '20' THEN FieldValue END) as "20",
MAX(CASE FieldNumber WHEN '21' THEN FieldValue END) as "21",
MAX(CASE FieldNumber WHEN '22' THEN FieldValue END) as "22",
MAX(CASE FieldNumber WHEN '23' THEN FieldValue END) as "23",
MAX(CASE FieldNumber WHEN '24' THEN FieldValue END) as "24",
MAX(CASE FieldNumber WHEN '25' THEN FieldValue END) as "25",
MAX(CASE FieldNumber WHEN '26' THEN FieldValue END) as "26",
MAX(CASE FieldNumber WHEN '27' THEN FieldValue END) as "27",
MAX(CASE FieldNumber WHEN '28' THEN FieldValue END) as "28",
MAX(CASE FieldNumber WHEN '29' THEN FieldValue END) as "29",
MAX(CASE FieldNumber WHEN '30' THEN FieldValue END) as "30",
MAX(CASE FieldNumber WHEN '31' THEN FieldValue END) as "31",
MAX(CASE FieldNumber WHEN '32' THEN FieldValue END) as "32",
MAX(CASE FieldNumber WHEN '33' THEN FieldValue END) as "33",
MAX(CASE FieldNumber WHEN '34' THEN FieldValue END) as "34",
MAX(CASE FieldNumber WHEN '35' THEN FieldValue END) as "35",
MAX(CASE FieldNumber WHEN '36' THEN FieldValue END) as "36",
MAX(CASE FieldNumber WHEN '37' THEN FieldValue END) as "37",
MAX(CASE FieldNumber WHEN '38' THEN FieldValue END) as "38",
MAX(CASE FieldNumber WHEN '39' THEN FieldValue END) as "39",
MAX(CASE FieldNumber WHEN '40' THEN FieldValue END) as "40"
FROM
(Select WorkOrder.WorkOrderKey, WorkOrder.OrderNumber, WorkOrder.StartDate, WorkOrder.EndDate, WorkOrderCustomFields.FieldNumber, WorkOrderCustomFields.FieldValue from WorkOrder
INNER JOIN WorkOrderCustomFields ON WorkOrderCustomFields.WorkOrderKey = WorkOrder.WorkOrderKey) as WorkOrders
group by OrderNumber, StartDate, EndDate
) AS "WorkOrder View"
custom function
Convert String to Decimal
1
String
text
cast ( ISNULL(NULLIF($1, ''), '0.0') as numeric(18,4))
SQLServer
numeric
Hi,
I have tried both of your custom functions and they seemed to be working fine for me, so in order to help me with further investigation could you please email across the yellowfin.log file that is stored under YellowfinappserverLOGS.
thanks,
David
I have tried both of your custom functions and they seemed to be working fine for me, so in order to help me with further investigation could you please email across the yellowfin.log file that is stored under YellowfinappserverLOGS.
thanks,
David