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 Pasteboard - Uploaded Image 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 Sample Crosstab Report - CUBA Platform. Report Generator 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