★ IMC Report Generate Failed

Environment:

Windows 2022 + MariaDB 10.5.18

Issue description:

We're unable generate the report for :

  a) Number of Users Per Month Report V2 (Generate for previous months)

  b) Number of Users Per Year Report V2 (Generated for current year)

Please refer screenshot below :

Analysis process

Jserver log shows:

2023-09-06 13:16:56 [ERROR] [Thread-3647] [net.sf.jasperreports.web.servlets.AsyncJasperPrintAccessor::reportFillError] Error during report execution

net.sf.jasperreports.engine.JRException: Error executing SQLstatement for : uam_online_detail_stat_hour

View entries in the database in the environment are faulty. You need to run the database command to rectify the fault.

Please perform the following operations to rectify them. The repair only affects reports:

1. Back up the entire dbman database first.

2. then connect to mysql, execute the following statement, semicolon is a sentence.

DROP view IF EXISTS report_db.uam_v_online_detail_stat_day_sub;

CREATE VIEW report_db.uam_v_online_detail_stat_day_sub as

SELECT distinct ONLINE_DETAIL_COUNT,STAT_TIME ,date_format(STAT_TIME,'%Y-%m') flag_time

FROM  ead.TBL_ONLINE_DETAIL_STAT

where STAT_CYCLE_TYPE=2

union all

select count(user_id) ONLINE_DETAIL_COUNT,DATE_FORMAT(begin_time, '%Y-%m-%d %h:00:00') as STAT_TIME ,date_format(begin_time,'%Y-%m') flag_time

from ead.tbl_online group by DATE_FORMAT(begin_time, '%Y-%m-%d %h:00:00'), date_format(begin_time,'%Y-%m');

DROP view IF EXISTS report_db.uam_v_online_detail_stat_day;

CREATE view report_db.uam_v_online_detail_stat_day as

select sum(a.ONLINE_DETAIL_COUNT) ONLINE_DETAIL_COUNT,a.STAT_TIME,a.flag_time

from

report_db.uam_v_online_detail_stat_day_sub a group by a.STAT_TIME,a.flag_time;

After execute, issue resolved