I am trying a crosstab report in CUBA-Platform 7.2x and getting the following error:
An error occurred while loading data for band [payroll] and query [payroll]. Report name [Report for entity "Salary payscale payroll line"]
An error occurred while loading data for data set [payroll]
user lacks privilege or object not found: CROSSTABDEMO_SALARYPAYSCALEPAYROLLLINE in statement [select
allowance.id as payroll_dynamic_header_allowance_id,
employee.id as payroll_master_data_employee_id,
e.amount as "amount"
from crosstabdemo_SalaryPayscalePayrollLine e
left join e.allowance allowance
left join e.salaryPayscalePayroll.employee employee
where e.salaryPayscalePayroll.salaryPayScale.id = ?] Query: select
allowance.id as payroll_dynamic_header_allowance_id,
employee.id as payroll_master_data_employee_id,
e.amount as "amount"
from crosstabdemo_SalaryPayscalePayrollLine e
left join e.allowance allowance
left join e.salaryPayscalePayroll.employee employee
where e.salaryPayscalePayroll.salaryPayScale.id = ? Parameters: [97c0b2cc-38c7-2e92-9343-15007766fd01]
From the error message it seems to be the object crosstabdemo_SalaryPayscalePayrollLine is missing but it exists. On the other hand, the user I am using is Admin having full-access.
Hi Alex
Thanks. I have changed that query to JPQL and now running. However, the amount field is not populated.
select
allowance.id as payroll_dynamic_header@allowance_id,
employee.id as payroll_master_data@employee_id,
SUM(e.amount) as "amount"
from crosstabdemo_SalaryPayscalePayrollLine e
left join e.allowance allowance
left join e.salaryPayscalePayroll.employee employee
where e.salaryPayscalePayroll.salaryPayScale.id = ${salaryPayscalePayroll_salaryPayScale1}
GROUP BY allowance.id, employee.id
I did have a successful result by following the example: https://doc.cuba-platform.com/reporting-7.2/crosstab_xls.html
having hsqldb as datasource, but after switching to posgresql as datasource and adapting the sql sentence, it didn’t work anymore.
@mortozakhan finally, the reason amount wasn’t being populated is my case was because the header id’s were different data types across bands, I had to cast them to integer on the sql sentence
