Jazz Forum Welcome to the Jazz Community Forum Connect and collaborate with IBM Engineering experts and users

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! 

0 votes



One answer

Permanent link
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.

1 vote

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. 

Your answer

Register or log in 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.

Search context
Follow this question

By Email: 

Once you sign in you will be able to subscribe for any updates here.

By RSS:

Answers
Answers and Comments
Question details
× 360
× 28

Question asked: Nov 10 '16, 6:17 a.m.

Question was seen: 2,821 times

Last updated: Nov 10 '16, 9:38 a.m.

Confirmation Cancel Confirm