It's all about the answers!

Ask a question

Numeric value variable type changes to date and time after editing query in JRS 6.0.2


hani zaidi (74822) | asked Nov 10 '16, 6:17 a.m.

 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



permanent link
Kevin Cornell (5411) | answered Nov 10 '16, 9:29 a.m.
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


Register or to post 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.