275 Star 2.2K Fork 915

JEECG官方 / JimuReport

 / 详情

纵向小计结果显示有问题

Done
Opened this issue  
2021-10-11 10:48
版本号:1.3.796
问题描述:纵向小计结果显示有问题
错误日志&截图:

输入图片说明

重现步骤:

友情提示(为了提高issue处理效率):

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

Comments (13)

raymas created任务
raymas set related repository to JEECG开源社区/JimuReport
Expand operation logs

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?

已修复,下个版本升级

lvdandan changed issue state from 待办的 to 已完成

Sign in to comment

Status
Assignees
Milestones
Pull Requests
Successfully merging a pull request will close this issue.
Branches
Planed to start   -   Planed to end
-
Top level
Priority
参与者(2)
Java
1
https://gitee.com/jeecg/JimuReport.git
git@gitee.com:jeecg/JimuReport.git
jeecg
JimuReport
JimuReport

Search

101014 b92fc32e 1850385 101014 af024cb7 1850385