AnsweredAssumed Answered

CABI report conversion to JasperReports challenge

Question asked by vchinni on Mar 28, 2017
Latest reply on Mar 29, 2017 by SteveTroy

I'm trying to convert an existing CABI report into JasperReport using Jaspersoft studio 6.3 and running into some challenges for converting the below CABI query to JasperReport query. The existing CABI report displays 4 fields Ticket Num, status, assignee and summary and users have an option to select values from 2 prompts group name and Weeks Opened for getting desired results.

 

Could anyone point me in the right direction for getting the JasperReport SQL code to execute correctly?

 

CABI Query

------------------------

SELECT
cr.ref_num,
cr.open_date,
cr.priority,
cr.summary,
cr.status_symbol,
cr.assignee_combo_name,
( cr.group_name )
FROM
cr
WHERE
(
cr.close_date Is Null
AND
( cr.group_name ) = @prompt('Group Name','A','Request Group/Analyst\Group Name',Mono,Free,Persistent,,User:1,optional)
AND
case when ( (PdmToday() - cr.open_date)/(7*24*3600) ) <= 1 then '1 Week' when ( (PdmToday() - cr.open_date)/(7*24*3600) ) > 1 AND ( (PdmToday() - cr.open_date)/(7*24*3600) ) <= 2 then '2 Weeks' when ( (PdmToday() - cr.open_date)/(7*24*3600) ) > 2 AND ( (PdmToday() - cr.open_date)/(7*24*3600) ) <= 3 then '3 Weeks' when ( (PdmToday() - cr.open_date)/(7*24*3600) ) > 3 AND ( (PdmToday() - cr.open_date)/(7*24*3600) ) <= 4 then '4 Weeks' when ( (PdmToday() - cr.open_date)/(7*24*3600) ) > 4 AND ( (PdmToday() - cr.open_date)/(7*24*3600) ) <= 8 then '30-60 Days' when ( (PdmToday() - cr.open_date)/(7*24*3600) ) > 8 then 'Over 60 Days' else '' end = @prompt('Weeks Open Symbol','A','Request Detail\Weeks Open Symbol',Mono,Free,Persistent,,User:0,optional)
AND
(
cr.type = 'R'
OR
cr.type Is Null
)
)

 

JasperReport Query

-------------------------------

 

SELECT
cr.ref_num,
cr.open_date,
cr.close_date,
cr.priority_symbol,
cr.status_symbol,
cr.assignee_combo_name,
cr.group_name,
(PdmToday() - cr.open_date)/(7*24*3600),
(PdmToday() - cr.open_date)/(24*3600),
(PdmToday() - cr.open_date)/3600,
FROM
cr
WHERE
{
cr.close_date IS NULL
AND
(cr.group_name in $P!{p_group} or '*' in $P!{p_group})
AND
(case when ((PdmToday() - cr.open_date)/(7*24*3600)) <2 then '1'
when ((PdmToday() - cr.open_date)/(7*24*3600)) >=2 AND ((PdmToday() - cr.open_date)/(7*24*3600)) <3 then '2'
when ((PdmToday() - cr.open_date)/(7*24*3600)) >=3 AND ((PdmToday() - cr.open_date)/(7*24*3600)) <4 then '3'
when ((PdmToday() - cr.open_date)/(7*24*3600)) >=4 AND ((PdmToday() - cr.open_date)/(7*24*3600)) <5 then '4'
when ((PdmToday() - cr.open_date)/(7*24*3600)) >=5 AND ((PdmToday() - cr.open_date)/(7*24*3600)) <8 then '5'
when ((PdmToday() - cr.open_date)/(7*24*3600)) >=8 then '6'
ELSE '' end ) as WeeksOpened,

case when ((PdmToday() - cr.open_date)/(7*24*3600)) < 2 THEN 1 ELSE 0 END AS "1Week",
case when ((PdmToday() - cr.open_date)/(7*24*3600)) >= 2 AND ((PdmToday() - cr.open_date)/(7*24*3600)) < 3 THEN 1 ELSE 0 END AS "2Weeks",
case when ((PdmToday() - cr.open_date)/(7*24*3600)) >= 3 AND ((PdmToday() - cr.open_date)/(7*24*3600)) < 4 THEN 1 ELSE 0 END AS "3Weeks",
case when ((PdmToday() - cr.open_date)/(7*24*3600)) >= 4 AND ((PdmToday() - cr.open_date)/(7*24*3600)) < 5 THEN 1 ELSE 0 END AS "4Weeks",
case when ((PdmToday() - cr.open_date)/(7*24*3600)) >= 5 AND ((PdmToday() - cr.open_date)/(7*24*3600)) < 8 THEN 1 ELSE 0 END AS "30-60Days",
case when ((PdmToday() - cr.open_date)/(7*24*3600)) >= 8 THEN 1 ELSE 0 END AS "Over60Days"
AND
cr.type = 'R'
OR
cr.type IS NULL
)
)

Outcomes