纵向小计结果显示有问题

版本号：1.3.796

问题描述：纵向小计结果显示有问题

错误日志&截图：

输入图片说明

重现步骤：

友情提示（为了提高issue处理效率）：

未按格式要求发帖，会被直接删掉;
请针对问题提供报表设计SQL脚本或在官网制作报表示例并提供ID;
针对不好重现的问题，请录制操作视频或详细的重现步骤；

导出报表配置看一下http://report.jeecg.com/2376604

insert into jimu_report(api_code,create_time,name,template,del_flag,id,view_count,note,status,thumb,update_time,create_by,api_url,type,css_str,update_by,js_str,code,json_str,api_method) values (null,'2021-10-09 17:27:02','Brand Sales Monthly Report',0,0,'606740666916192256','38',null,null,null,'2021-10-11 10:43:06','admin',null,'datainfo',null,'admin',null,'20211009172701','{"loopBlockList":[],"area":{"sri":3,"sci":5,"eri":3,"eci":5,"width":119,"height":25},"excel_config_id":"606740666916192256","printConfig":{"paper":"A4","width":210,"height":297,"definition":1,"isBackend":false,"marginX":10,"marginY":10,"layout":"portrait"},"zonedEditionList":[],"rows":{"0":{"cells":{"0":{"text":"Brand Sales Monthly Report","merge":[0,3],"height":25,"style":1}},"height":51},"2":{"cells":{"1":{"text":"Sales Brand","style":5},"2":{"text":"Sales Year","style":5},"3":{"text":"Sales Month","style":5},"4":{"text":"Sales Qty","style":5},"5":{"text":"Grand Total","style":5,"rendered":"","config":""},"6":{"rendered":"","config":""}}},"3":{"cells":{"1":{"style":4,"text":"#{RPT001.group(brand)}","aggregate":"group","subtotal":"groupField","funcname":"-1","rendered":"","config":"","display":"normal","completeBlankStatus":true},"2":{"style":4,"text":"#{RPT001.group(sy)}","aggregate":"group","subtotal":"-1","funcname":"-1","rendered":"","config":"","display":"normal","completeBlankStatus":true},"3":{"style":4,"text":"#{RPT001.sm}","rendered":"","config":""},"4":{"style":4,"text":"#{RPT001.qty}","subtotal":"-1","funcname":"SUM","rendered":"","config":"","display":"normal","aggregate":"","loopBlock":0},"5":{"style":4,"text":"#{RPT001.total}","funcname":"SUM","subtotal":"-1","rendered":"","config":"","display":"normal","aggregate":""},"6":{"funcname":"SUM","subtotal":"-1"},"8":{"text":"","rendered":"","config":""}}},"4":{"cells":{"1":{"text":""},"2":{"text":"","rendered":"","config":""},"4":{"text":"","rendered":"","config":""}}},"6":{"cells":{"10":{"text":"","rendered":"","config":""}}},"7":{"cells":{"3":{"text":"","rendered":"","config":""}}},"8":{"cells":{"4":{"text":"","rendered":"","config":""},"5":{"style":4,"text":" "}}},"9":{"cells":{"7":{"text":"","rendered":"","config":""}}},"10":{"cells":{"9":{"text":"","rendered":"","config":""}}},"11":{"cells":{"8":{"text":"","rendered":"","config":""},"14":{"text":"","rendered":"","config":""}}},"12":{"cells":{"7":{"text":"","rendered":"","config":""},"13":{"text":"","rendered":"","config":""}}},"13":{"cells":{"10":{"text":"","rendered":"","config":""}}},"15":{"cells":{"13":{"text":"","rendered":"","config":""}}},"19":{"cells":{"11":{"text":"","rendered":"","config":""}}},"21":{"cells":{"14":{"text":"","rendered":"","config":""}}},"len":100},"dbexps":[],"dicts":[],"rpbar":{"show":true,"pageSize":"","btnList":[]},"groupField":"RPT001.brand","freeze":"A1","dataRectWidth":633,"displayConfig":{},"background":false,"completeBlankRowList":[{"db":"RPT001","field":"group(brand)"},{"db":"RPT001","field":"group(sy)"}],"name":"sheet1","autofilter":{},"styles":[{"font":{"size":22}},{"font":{"size":22},"underline":true},{"font":{"size":22},"underline":false},{"bgcolor":"#bdd7ee"},{"border":{"bottom":["thin","#000"],"top":["thin","#000"],"left":["thin","#000"],"right":["thin","#000"]}},{"bgcolor":"#bdd7ee","border":{"bottom":["thin","#000"],"top":["thin","#000"],"left":["thin","#000"],"right":["thin","#000"]}}],"validations":[],"isGroup":true,"cols":{"4":{"width":114},"5":{"width":119},"len":50},"merges":["A1:D1"]}',null);
insert into jimu_report_db(db_code,db_ch_name,is_list,create_time,db_type,db_key,db_source,id,jimu_report_id,db_dyn_sql,is_page,update_time,tb_db_table_name,create_by,api_url,json_data,db_table_name,update_by,tb_db_key,java_type,java_value,api_method,api_convert,db_source_type) values ('RPT001','Brand Monthly Sales','0','2021-10-11 10:43:04','0',null,'606744019612123136','606747134688149504','606740666916192256','select brand,date_format(x.tgl,'%Y') sy,date_format(x.tgl,'%m') sm,sum(qty) qty,sum(total) total from v_salesdetail x
WHEREx.tgl >='${fromYearMth}' and x.tgl<='${toYearMth}' and (not((x.int_sts regexp 'cancel|draft|return|reject|refund')))
group by brand,date_format(x.tgl,'%Y') ,date_format(x.tgl,'%m') ','0','2021-10-11 10:43:04',null,'admin',null,'',null,'admin',null,null,null,null,'','mysql');
insert into jimu_report_db_field(create_time,widget_type,order_num,search_mode,field_text,dict_code,id,search_flag,update_time,create_by,jimu_report_db_id,widget_width,search_value,update_by,field_name,search_format,ext_json) values ('2021-10-11 10:43:04','number',0,null,'total',null,'606747134843338752',null,null,'admin','606747134688149504',null,null,null,'total',null,null);
insert into jimu_report_db_field(create_time,widget_type,order_num,search_mode,field_text,dict_code,id,search_flag,update_time,create_by,jimu_report_db_id,widget_width,search_value,update_by,field_name,search_format,ext_json) values ('2021-10-11 10:43:04','number',1,null,'qty',null,'606747135023693824',null,null,'admin','606747134688149504',null,null,null,'qty',null,null);
insert into jimu_report_db_field(create_time,widget_type,order_num,search_mode,field_text,dict_code,id,search_flag,update_time,create_by,jimu_report_db_id,widget_width,search_value,update_by,field_name,search_format,ext_json) values ('2021-10-11 10:43:04','string',2,null,'brand',null,'606747135090802688',null,null,'admin','606747134688149504',null,null,null,'brand',null,null);
insert into jimu_report_db_field(create_time,widget_type,order_num,search_mode,field_text,dict_code,id,search_flag,update_time,create_by,jimu_report_db_id,widget_width,search_value,update_by,field_name,search_format,ext_json) values ('2021-10-11 10:43:04','string',3,null,'sy',null,'606747614625579008',null,null,'admin','606747134688149504',null,null,null,'sy',null,null);
insert into jimu_report_db_field(create_time,widget_type,order_num,search_mode,field_text,dict_code,id,search_flag,update_time,create_by,jimu_report_db_id,widget_width,search_value,update_by,field_name,search_format,ext_json) values ('2021-10-11 10:43:04','string',4,null,'sm',null,'606747614701076480',null,null,'admin','606747134688149504',null,null,null,'sm',null,null);
insert into jimu_report_db_param(create_time,jimu_report_head_id,param_name,order_num,widget_type,search_mode,param_value,dict_code,id,search_flag,update_time,create_by,param_txt,update_by,search_format,ext_json) values ('2021-10-11 10:43:04','606747134688149504','fromYearMth',1,'date',1,'=dateStr('yyyy-MM-dd')',null,'606747135220826112',1,null,'admin','开始月份',null,'yyyy-MM-dd',null);
insert into jimu_report_db_param(create_time,jimu_report_head_id,param_name,order_num,widget_type,search_mode,param_value,dict_code,id,search_flag,update_time,create_by,param_txt,update_by,search_format,ext_json) values ('2021-10-11 10:43:04','606747134688149504','toYearMth',2,'date',1,'=dateStr('yyyy-MM-dd')',null,'606747135304712192',1,null,'admin','结束月份',null,'yyyy-MM-dd',null);

表结构及数据再提供一下吧

grand_total decimal(36,2)

输入图片说明

SQL:
select store,date_format(create_date,'%Y') sy,date_format(create_date,'%m') sm,sum(grand_total) total from egogosales group by store,date_format(create_date,'%Y'),date_format(create_date,'%m')

导出表结构和数据

-- phpMyAdmin SQL Dump
-- version 5.0.4
-- https://www.phpmyadmin.net/

-- 主机： localhost
-- 生成日期： 2021-10-11 14:25:28
-- 服务器版本： 5.7.34-log
-- PHP 版本： 7.4.23

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /;
/!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;
/!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;
/!40101 SET NAMES utf8mb4 */;

--
-- 数据库： `reportdb`

--
-- 表的结构 `egogosales_temp`

CREATE TABLE egogosales_temp (
order_id varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
salesorder_no varchar(255) COLLATE utf8_unicode_ci NOT NULL,
store varchar(5) COLLATE utf8_unicode_ci NOT NULL,
channel_name varchar(5) COLLATE utf8_unicode_ci NOT NULL,
create_date datetime DEFAULT NULL,
payment_date datetime DEFAULT NULL,
delivery_date datetime DEFAULT NULL,
completion_date datetime DEFAULT NULL,
customer_name blob,
customer_phone varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
customer_email varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
sub_total decimal(36,2) DEFAULT '0.00',
shipping_cost decimal(36,2) DEFAULT '0.00',
insurance_cost decimal(36,2) DEFAULT '0.00',
grand_total decimal(36,2) DEFAULT '0.00',
recipient_name blob,
recipient_phone varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
shipping_address blob,
shipping_area varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
shipping_city varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
shipping_province varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
shipping_post_code varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
shipping_country varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
shipper varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
tracking_no varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
channel_status varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
internal_status set('DRAFT','VALIDATED','PROCESSED','SHIPPING','DELIVERED','CANCELED','RETURNED','REFUNDED','COMPLETED') COLLATE utf8_unicode_ci DEFAULT '',
cancel_reason text COLLATE utf8_unicode_ci,
cancel_reason_detail blob,
tms timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
fk_user_create int(11) DEFAULT NULL,
tms_sync timestamp NULL DEFAULT NULL,
fk_user_sync int(11) DEFAULT NULL,
payment_method varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
payment_ref varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
shopid varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
payment_id varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC;

--
-- 转存表中的数据 `egogosales_temp`

INSERT INTO egogosales_temp (order_id, salesorder_no, store, channel_name, create_date, payment_date, delivery_date, completion_date, customer_name, customer_phone, customer_email, sub_total, shipping_cost, insurance_cost, grand_total, recipient_name, recipient_phone, shipping_address, shipping_area, shipping_city, shipping_province, shipping_post_code, shipping_country, shipper, tracking_no, channel_status, internal_status, cancel_reason, cancel_reason_detail, tms, fk_user_create, tms_sync, fk_user_sync, payment_method, payment_ref, shopid, payment_id) VALUES
('1070622446', '1070622446', '16', '15', '2021-10-01 01:27:23', '2021-10-01 01:43:40', NULL, NULL, 0x78787878, 'TEST', 'TEST', '307200.00', '1700.00', '0.00', '308900.00', 0x78787878, 'TEST', 0x78787878, 'Denpasar Utara', NULL, 'Bali', '80115', 'INDONESIA', '', '', 'Waiting for Delivery', 'VALIDATED', '', '', '2021-10-11 06:11:26', NULL, NULL, NULL, '', NULL, '50265', NULL),
('1070628612', '1070628612', '16', '15', '2021-10-01 05:14:59', '2021-10-01 05:16:10', NULL, NULL, 0x78787878, 'TEST', 'TEST', '97227.00', '227.00', '0.00', '97454.00', 0x78787878, 'TEST', 0x78787878, 'Nanggulan', NULL, 'DI Yogyakarta', '55671', 'INDONESIA', 'JX', 'JXPOP01733895601', 'Ready to Ship', 'PROCESSED', '', '', '2021-10-11 06:11:26', NULL, NULL, NULL, '', NULL, '50265', NULL);

--
-- 转储表的索引

--
-- 表的索引 `egogosales_temp`

ALTER TABLE egogosales_temp
ADD PRIMARY KEY (salesorder_no,store,channel_name) USING BTREE,
ADD KEY create_date (create_date,internal_status);
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT /;
/!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS /;
/!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

查询SQL:
select store,date_format(create_date,'%Y') sy,date_format(create_date,'%m') sm,sum(grand_total) total from egogosales_temp
where create_date is not null
group by store,date_format(create_date,'%Y'),date_format(create_date,'%m')

效果：
输入图片说明

还有个小问题是2个分组小计时，第二个分组小计“合计”没显示

收录 yf

什么是收录 yf？

已修复，下个版本升级

JEECG官方 / JimuReport

Content Risk Flag