JPQL version of the crosstab sample report

Support
#1

Hi everyone, What will be the equivalent jpql queries for the sample crosstab report provided in the documentation. I have to create a crosstab report for a project of mine and i am stuck . TIA

#3

Hi, it should be like this

SELECT 11 AS header_id, ‘November’ as month_name
FROM (VALUES (1)) AS dual (dual)

#4

@s.markevich sorry for getting back so late. but i wanted to know what will be the equivalent jpql for this sample crosstab report’s data band query:

select bi.library_department_id as bi_master_data@department_id,
month(bi.created_date) as bi_dynamic_header@header_id,
sum(bi.book_count) as “amount”
from BOOK_INSTANCE bi
where bi.created_date >= ${start_date} and bi.created_date<= ${end_date}
and bi.library_department_id in (${bi_master_data@department_id})
and month(bi.created_date) in (${bi_dynamic_header@header_id})
group by bi.library_department_id,month(bi.created_date)
order by bi.library_department_id,month(bi.created_date)

if it is possible to do it in jpql.

#6

Something like this:

SELECT bi.libraryDepartmentId AS departmentId,
       FUNCTION('MONTH', bi.createdDate) AS headerId,
       SUM(bi.bookCount) AS amount
FROM BookInstance bi
WHERE bi.createdDate >= :startDate AND bi.createdDate <= :endDate
AND bi.libraryDepartmentId IN :departmentIds
AND FUNCTION('MONTH', bi.createdDate) IN :headerIds
GROUP BY bi.libraryDepartmentId, FUNCTION('MONTH', bi.createdDate)
ORDER BY bi.libraryDepartmentId, FUNCTION('MONTH', bi.createdDate)

Translated by AI Assistant.
Didn’t test.

Regards,
Konstantin

#7

Thanks for your reply @krivopustov .The solution you gave didn’t work for me. Can you please check if it works or provide alternative?