Crosstab report: mission impossible

Hi, we are using report for the first time and we need to create something like this:

starting from a table GROUP_WORK where we have these fields: date, group name like

01/01/2020; group 1;
02/01/2020; group 2;
03/01/2020; group 3;
01/02/2020; group 1;
03/02/2020; group 5;

and so on for all the year dates…

we need to create a report like this https://pasteboard.co/IPNOLhK.png where the group name has to be written into the n.day/month right coordinate…

So, since yesterday we started studying the crosstab table, reading the documentation here https://doc.cuba-platform.com/reporting-latest/crosstab_xls.html and trying to have the report done.

We have done the month headers, the day numbers, but, when we need to put data into the central cells, we fail…well, we have data, but, we think, as we cannot have a unique day_id, the report is not able to put the right values into the correct place, it seam that the report cannot “rebiuld” the relation between the day number on the row-header and the month-year on the column header.

we created 3 datasets under the root:
report
report_dynamic_header
report_master_data

to get the day numbers from 1 to 31 with the report_master_data:

SELECT 
day(GROUP_WORK.DATE) as day_number
FROM
GROUP_WORK
order by day(GROUP_WORK.DATE) asc

to have the column header to have records like this 012020;JAN-2020 … with the report_dynamic_header:

SELECT 
DATE_FORMAT(GROUP_WORK.DATE,'%m%Y') as header_id,
concat(substr(upper(monthname(GROUP_WORK.DATE)),1,3), "-",year(GP_PF1DAY.DATE)) as monthname
FROM
GROUP_WORK
GROUP BY DATE_FORMAT(GROUP_WORK.DATE,'%m%Y'),
concat(substr(upper(monthname(GROUP_WORK.DATE)),1,3), "-",year(GP_PF1DAY.DATE)),
cast(concat(year(GROUP_WORK.DATE),lpad(month(GROUP_WORK.DATE),2,"0")) as UNSIGNED)
order by cast(concat(year(GROUP_WORK.DATE),lpad(month(GROUP_WORK.DATE),2,"0")) as UNSIGNED)

and than finally i have the report dataset with the main select…:

SELECT
day(GROUP_WORK.DATE) as report_master_data@day_number,
DATE_FORMAT(GROUP_WORK.DATE,'%m%Y') as header_id,
GROUP_WORK.group as "team"
FROM
GP_PF1YEAR
where
day(GROUP_WORK.DATE) in (${report_master_data@day_number})
and DATE_FORMAT(GROUP_WORK.DATE,'%m%Y') in (${report_dynamic_header@header_id})
ORDER BY GROUP_WORK.DATE ASC

after two days i think we need help to understand how build it correctly, do you have an idea?

thanks
Angelo

Hello, @lazzari.angelo

In the query you need to use report_dynamic_header@header_id

I created a project for you, with an example of solving your problem, take a look at it, it should help you.

Example project: cross-report.7z (56.9 КБ)
Report: Report.zip (8.3 КБ)

Regards,
Nikita

Thank you very much @shchienko! I will test it! Thanks again!
Angelo

IT WORKS!!! it is incredible…what a powerful platform!

Now I have to problems, i struggled with them a few hours without a real solution:

  1. how can i put a title into the excel? i would like to have the first line with a label center on the top of the main table, but even if i put it into the excel the report hasn’t the title.

  2. we have the “team” field valued as some letters and some of the value has a few spaces on the right like "M P A R " or "M P L " … for some reason when i extract the report with the GROUP_WORK.group as “team” the final spaces are deleted and i have to concat a “real” char to have the space put into the cell…

can you help me please in this problem too?

thank you very much!

and @shchienko thank you VERY VERY MUCH for your project… it was really easy to understand where i had the mistake.

tnhanks