Numeric value variable type changes to date and time after editing query in JRS 6.0.2
Hi,
I've to edit a JRS query manually to find date difference. The value of date difference is a numeric variable type. I've casted it to numeric and considered choosing numeric/integer value from variable type once I've to execute the query. But each time the query is executed the variable type value is reassigned to date and time in the 'Format as Table' section.
I need to have this custom created field (by editing the query manually) to have a numeric/integer data type to populate the field in graph format against workitems. Any help in this regard would be highly appreciated. Below is my query for reference:
SELECT DISTINCT T1.NAME,
T1.CREATION_DATE,
T1.DUE_DATE,
T1.CLOSED_DATE,
case when T1.CLOSED_DATE is null then getdate() else T1.CLOSED_DATE end as closed_date,
cast(
case when (T1.CLOSED_DATE is null) then cast( (cast(DATEDIFF(day,T1.DUE_DATE,getdate())as decimal(4,2))/cast(DATEDIFF(day,T1.CREATION_DATE,T1.DUE_DATE) as decimal(4,2)))*100 as numeric)
else cast(cast(DATEDIFF(day,T1.CLOSED_DATE,T1.DUE_DATE) as decimal(4,2))/cast(DATEDIFF(day,T1.DUE_DATE,T1.CREATION_DATE) as decimal(4,2)) *100 as numeric) end
as numeric)
AS Project_Schedule_Variance
FROM RIDW.VW_REQUEST T1
WHERE T1.PROJECT_ID = 0 AND
( T1.REQUEST_TYPE = 'Change Request'
) and T1.CREATION_DATE>0 and
T1.DUE_DATE>0 and
DATEDIFF(day,T1.CREATION_DATE,T1.DUE_DATE)>0 and
(T1.ISSOFTDELETED = 0) AND
(T1.REQUEST_ID <> -1 AND T1.REQUEST_ID IS NOT NULL)
Thanks!
One answer
Here are 2 things you might try:
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.
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.
Comments
Hi Kevin, thankyou for your answer. You are right I created a query using UI and then entered the manual edit mode. Each time I enter the manually edit mode the data variable type is not getting changed. I've tried the solutions you proposed here. The new field I've created make use of two existing date attributes and I am guessing it gets the data variable type from the variables used to compute this new value.