SQL query is fine but Jasper returns an error

[joaoflima.6]

Hi there,

I'm just started with Jaspersoft Studio and I'm facing a really annoying issue.

I want to create a report with a line chart to show the number of the tickets created per hour, for the last 7 hours. 

I wrote the SQL query, it's running well and returning the expected results when executed on SQL Server Management Studio.

When I create the report using the query as the dataset, it works fine in Preview mode but when it comes to the web browser it returns this error:

• An error has occurred. Please contact your system administrator. (6632)

Here is the SQL query:

WITH all_hours AS (
    SELECT DISTINCT number, 0 tickets 
    FROM master..spt_values
    WHERE number BETWEEN DATEPART(HOUR, dateadd(S, DATEDIFF(SECOND, '19700101', SYSDATETIME()), '1970-01-01')) - 7 AND DATEPART(HOUR, dateadd(S, DATEDIFF(SECOND, '19700101', SYSDATETIME()), '1970-01-01'))
)
SELECT
    CONCAT(CAST(ah.number AS VARCHAR), 'h') AS hora,
    SUM(CASE WHEN cr.open_date > DATEDIFF(SECOND, '19700101', SYSDATETIME()) - (3600*7) THEN 1 ELSE 0 END) AS tickets
FROM all_hours ah
LEFT JOIN call_req cr ON ah.number = DATEPART(HOUR, dateadd(S, cr.open_date, '1970-01-01'))
GROUP BY ah.number;
 

I read that this error message can be related to the SQL query, but it's running fine on SQL Server Management Studio, I think it should run fine as well on Jasper. 

Can anyone help me with this problem? 

Thanks in advance.

Greetings, 
João

[reportdev]

It could be the WITH Clause.

Jasper by default, does not support the WITH Clause.

https://community.jaspersoft.com/wiki/adding-statements-security-validator