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
I assume you used the report editor UI to create the initial SQL query and then put that report in manual edit mode. RB keeps meta-data in the report which describes the original columns (before manual edit) and I'm guessing the variable being used for the computed date difference (integer) is the same one original variable used for the date.
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
hani zaidi
commented Nov 10 '16, 9:38 a.m.
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. |
Your answer
Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.