Can't execute sql server query in BIRT

I have recently started using BIRT for some very simple reporting in a Java\SQL Server application. Now I have a dynamic sql stmt that I'm trying to use in the DataSet on BIRT ( sorry for my english i"am Frensh ) the probleme is that BIRT can't create data set, i didn't find solution for this report. Birt told me that sql server doesn't return sql statement does not return a ResultSet object. SQL error #1, i also want to know if we can use sql variables in birt to create our data set
this is my sql query :

As far as I can relate, BIRT is generally not able to get any metadata from your dynamic query because, exactly it is dynamic, so it basically could change at runtime. There are workarounds, but your problem is that you use a pivot function in your dynsql, resulting in variable amount of columns.

This will not work for your query, BIRT needs the exact amount of columns, names and types from your dataset.

Instead shift the pivot to BIRT and make your dataset return exact metadata. BIRT is able use pivot.

You can however use dynamic sql, as long as you make sure that the resultset is consistent. First of all pack your dynamic sql into a stored procedure on the sql server, only pass the parameter.

If you don't own an SQL Server 2012 and above, turn the SET FMTLONLY ON in your StoredProc, add the SP as your dataset source, retain the metadata and finally alter your SP with SET FMTONLY OFF.
FMTLONLY ON will return only metadata to the BIRT.