Can we edit variable type after manually editing JRS query?
Hi, I am using JRS 6.0.2 with SQL as DB and data warehouse as my datasource. To apply a custom formula to find percentage I've edited the JRS query manually. One field here is computed by using the datedifference() formula. Although the result of this field should be a numeric/integer value, JRS is taking this field as variable type 'date and time'. Changing the variable type from the drop down is not working, as on every recurring execution of report the data type of this computed field is reassigned to 'date and time'.
Is this a known issue/bug or there is a workaround for this problem? Any help in this regard is highly welcome.
Thanks!
3 answers
Hi Kevin, thankyou for reminding me
1) Change the variable in the SQL to be completely different than it was before and does not correspond to any other variable (previous or now). In the Format section it should default to String (not Date) so you should be able to change it to Integer, save the report and it should stay as integer.
Tried this. Firstly I cannot change a date value save as string by default because I need to do date difference calculations in query which is not possible if I save date by data type string. Secondly saving the report from date time to integer donot keeps it that way. Its important to remember here that I've enabled custom editing of SQL query so none of other GUI options are available.
2) If that does not work, create a new simple DW report (e.g., for Work Item, no projects selected, no conditions, and just the default columns). Delete the WI project area and ID columns (leave only the name and URL). Save the report, edit it manually and paste in your custom SQL from the report that does not work. After you validate your SQL query, you will need to set the variable types accordingly.
2) If that does not work, create a new simple DW report (e.g., for Work Item, no projects selected, no conditions, and just the default columns). Delete the WI project area and ID columns (leave only the name and URL). Save the report, edit it manually and paste in your custom SQL from the report that does not work. After you validate your SQL query, you will need to set the variable types accordingly.
This seems to be no different than that I did
Hi Hani,
You can use a simpler function to do a date difference, Below is the function that I use to populate a Aging_in_days column which is a difference between Resolved date and Creation date (These are in Timestamp format in RTC). I was able to select the type as integer from the drop down for this field and it remains the same.
This might not be an answer to your question but I though it might help if you change the formula.
You can use a simpler function to do a date difference, Below is the function that I use to populate a Aging_in_days column which is a difference between Resolved date and Creation date (These are in Timestamp format in RTC). I was able to select the type as integer from the drop down for this field and it remains the same.
This might not be an answer to your question but I though it might help if you change the formula.
SELECT DISTINCT T1.REFERENCE_ID,
T1.NAME,
T2_1.LITERAL_NAME,
T1.CREATION_DATE,
T1.RESOLVED_DATE,
CASE
WHEN T1.RESOLVED_DATE is NOT NULL THEN DAYS(T1.RESOLVED_DATE) - DAYS(T1.CREATION_DATE)
ELSE DAYS(CURRENT TIMESTAMP) - DAYS(T1.CREATION_DATE)
END AS Aging_in_days
FROM RIDW.VW_REQUEST T1
Comments
Hi Rohit,
Thankyou for your answer. I tried changing the formula but it didn't work and gave the following error 'DAYS is not a built-in function' which is true as I am using SQL Database with DW datasource and SQL don't support DAYS function. Instead these functions are supported:
Datedifference being the most appropriate for my query.
https://www.tutorialspoint.com/sql/sql-date-functions.htm
Datedifference being the most appropriate for my query.
https://www.tutorialspoint.com/sql/sql-date-functions.htm
Thanks again buddy. Let me know if you've any other suggestion.