SQL Query Error in Jasperstudio 6.3

[mtrehans]

Hi,

I am Executing the below query in MS SQL 2012 and it run as success. But the same query executing on JasperStudio 6.3 throwing error.

Do let me know how to resolve this.

 

SQL Query

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

;With FirstCallAssignee
AS
(
SELECT call_req_id,description,ROW_NUMBER() OVER (PARTITION BY call_req_id ORDER BY [time_stamp]) AS Seq
FROM act_log
WHERE [Type] = 'EVT'
)
select COALESCE(Assignee,'Average') AS Assignee,
Format(dateadd(ss,Avg(case when t.sym = 'None' then timestamp_diff * 1.0 else null end),0),'HH:mm:ss') as PriorityP0,
Format(dateadd(ss,Avg(case when t.sym = 'P1' then timestamp_diff * 1.0 else null end),0),'HH:mm:ss') as PriorityP1,
Format(dateadd(ss,Avg(case when  t.sym = 'P2' then timestamp_diff * 1.0 else null end),0),'HH:mm:ss') as PriorityP2,
Format(dateadd(ss,Avg(case when  t.sym = 'P3' then timestamp_diff * 1.0 else null end),0),'HH:mm:ss') as PriorityP3,
Format(dateadd(ss,Avg(case when t.sym = 'P4' then timestamp_diff * 1.0 else null end),0),'HH:mm:ss') as PriorityP4,
Format(dateadd(ss,Avg(case when t.sym = 'P5' then timestamp_diff * 1.0 else null end),0),'HH:mm:ss') as PriorityP5,
Format(dateadd(ss,Avg(case when t.sym = 'P1'  or t.sym = 'P2' or t.sym ='P3' or t.sym ='P4' or t.sym = 'P5' or t.sym ='None' then timestamp_diff * 1.0  else null end),0),'HH:mm:ss') as AverageTime

from

(
select
p.sym,
priority,
REPLACE(LTRIM(RTRIM(LEFT(STUFF(CAST(f.description AS nvarchar(max)),1,PATINDEX('%Transfer Assignee From%',CAST(f.description AS nvarchar(max))) + 42,''),CHARINDEX('to',STUFF(CAST(f.description AS nvarchar(max)),1,PATINDEX('%and Group%',CAST(f.description AS nvarchar(max))) + 14,'')+'to')-1))),'''','') AS Assignee,
call_req.ref_num,
T.[time_stamp]-T1.[time_stamp] as timestamp_diff
from act_log T
inner join FirstCallAssignee f
ON f.call_req_id = T.call_req_id
AND f.Seq = 1
cross apply (select [time_stamp] from act_log where call_req_id=T.call_req_id and type='INIT') T1
inner join call_req on T.call_req_id = call_req.persid
inner join pri p  on call_req.priority=p.enum
and dateadd(s,[open_date],'1970-01-01') between $P{start_date} and $P{end_date}
inner join ca_contact on T.analyst = ca_contact.contact_uuid
where T.type in ('ST')and T.type = 'ST'  and T.description like '%''Open'' to ''Analyst Assigned''%'
and call_req.type ='I'
)t
group by Assignee
with cube

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

Error Image

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

 

 

[hozawa]

Are you installing your own ms sql jdbc driver to jaspersoft studio?

https://community.jaspersoft.com/documentation/tibco-jaspersoft-studio-user-guide/v60/creating-and-using-database-jdbc-connections

The screenshot you've provided seems like from JasperReports Server and not Jaspersoft Studio. If so, have you installed your own ms sql jdbc driver to JasperReports Server instead of relying on the default jdbc driver?

[reportdev]

that error is caused due to SQLValidation in the xml files.

With clause in the SQL is not supported by default. You should 

Add the With clause to security validator

Open jasperserver-pro/WEB-INF/classes/esapi/validation.properties

change the Validator.ValidSQL setting to:

Validator.ValidSQL=(?is)^\s*(select|with)\s+^;+$

[mtrehans]

I did changes Validator.ValidSQL=(?is)^\s*(select|with)\s+^;+$  but it is not working

[mtrehans]

I am using MS SQL 2012 from I have downloaded the "sqljdbc_4.0" driver but where I have to place this driver as in this folder below files present which file I have to copy and place.