Category
Report Builder
Product Version
7.1
How to get the division quotiant and remainder using calculated field?
Answered
Hello Experts,
I am wondering and could not find a way to find the number of days, hours , minutes and seconds from two date difference or from available seconds. If there is a pre-defined function, please direct me ( I am aware of formating the seconds field to select Days Hours Minutes Seconds, to get the result in one field). I am looking basically to calculate in separate fields.
I know there are predefined formula to calculate the Days, Seconds but I want to calculate the days and then hours from remainder second's and then minutes from hours remainder seconds and remainder seconds.
TIA
Mohammad
The same question
Hi Mohammad,
Unfortunately these functions do not exist at the report level, though it should be relatively easy to create your own custom functions to achieve this.
I would recommend finding the current date-diff functions in the custom functions file and editing these to suit your needs. These can be found:
youYfInstall/appserver/webapps/ROOT/WEB-INF/customfunctions.xml
Let me know if this sounds like it will work.
Nathan
Hi Mohammad,
Unfortunately these functions do not exist at the report level, though it should be relatively easy to create your own custom functions to achieve this.
I would recommend finding the current date-diff functions in the custom functions file and editing these to suit your needs. These can be found:
youYfInstall/appserver/webapps/ROOT/WEB-INF/customfunctions.xml
Let me know if this sounds like it will work.
Nathan
Hi Nathan,
Thank you as always for your time and help.
I will try to play with customfunction.xml and see where i end up.
Regards,
Mohammad
Hi Nathan,
Thank you as always for your time and help.
I will try to play with customfunction.xml and see where i end up.
Regards,
Mohammad
Hi Mohammad,
Sounds good, let me know if you run into any problems or have any questions!
Nathan
Hi Mohammad,
Sounds good, let me know if you run into any problems or have any questions!
Nathan
Hi Nathan,
I did run into problem, i added the new function for mod($1/$2) when try to use it i got the following error in reporting logs. I wanted to add mod and floor function to calculate the quotient and remainder.
It seems new oracle functions except mentioned below in error.
Caused by: java.sql.SQLException: Unsupported function: mod. Supported functions are: COUNT, AVG, MIN, MAX, SUM,CURRENTDATE, DATE, TIME, DAY, MONTH, YEAR, WEEK,WEEKDAY, HOUR, MINUTE, SECOND, QUARTER, DATEDIFF, DATENAME, DATENUM, DATEADD,UPPER, LOWER, LTRIM, RTRIM, REPLACESTR, LPAD, RPAD, SUBSTR
Thanks
Mohammad
Hi Nathan,
I did run into problem, i added the new function for mod($1/$2) when try to use it i got the following error in reporting logs. I wanted to add mod and floor function to calculate the quotient and remainder.
It seems new oracle functions except mentioned below in error.
Caused by: java.sql.SQLException: Unsupported function: mod. Supported functions are: COUNT, AVG, MIN, MAX, SUM,CURRENTDATE, DATE, TIME, DAY, MONTH, YEAR, WEEK,WEEKDAY, HOUR, MINUTE, SECOND, QUARTER, DATEDIFF, DATENAME, DATENUM, DATEADD,UPPER, LOWER, LTRIM, RTRIM, REPLACESTR, LPAD, RPAD, SUBSTR
Thanks
Mohammad
Hi Mohammad,
That is odd as I have certainly used other functions not on that list in custom functions. However, I was not using Oracle.
One thing that I have seen work, is the use of DB level functions. If you create this function in the DB, you should be able to reference that function in your custom Yellowfin function, or possibly in a free-hand sql calculated field.
If not then your best bet might be to look into coding a custom advanced function (if you are familiar with Java)
Regards,
Nathan
Hi Mohammad,
That is odd as I have certainly used other functions not on that list in custom functions. However, I was not using Oracle.
One thing that I have seen work, is the use of DB level functions. If you create this function in the DB, you should be able to reference that function in your custom Yellowfin function, or possibly in a free-hand sql calculated field.
If not then your best bet might be to look into coding a custom advanced function (if you are familiar with Java)
Regards,
Nathan
Replies have been locked on this page!