跳到主要内容

纵向分组小计

小计：是指数字列表的一部分的总和 纵向分组小计：是指每个纵向分组下的小计

示例1—不存在横向分组小计​

1、效果展示​

纵向分组小计：点击预览示例1 以地区为分组依据，计算销售金额合计；合计两个字展示在地区下。

2、创建一个名为区域省份销售统计的数据报表​

创建一个区域销售表，有七个字段：ID、数量、区域名称、年、省份、价格、月份

2.1 为区域省份销售统计创建SQL数据源​

部分数据展示

2.2 将字段拖拽到设计器页面​

2.3 设置纵向分组、横向分组、横向分组字段​

此步骤涉及到横向纵向组合动态列和交叉报表

横向纵向组合动态列请参考 横向纵向组合动态列 交叉报表请参考 交叉报表

  • 地区(areaname)纵向分组： #{jm.group(areaname)}
  • 省份(province)纵向分组： #{jm.group(province)}
  • 年份(year)横向分组： #{jm.groupRight(year)}年
  • 月份(month)横向分组： #{jm.groupRight(month)}月
  • 销售额(amount)横向分组字段：#{jm.dynamic(amount)}

2. 4、重点来了->设置分组小计​

#{jm.group(areaname)}设置为分组依据

注意：分组依据的设置只有在当前选中单元格的数据设置为分组时才会显示

#{jm.dynamic(amount)}聚合方式设置为求和

保存预览

示例2—存在横向分组小计​

1、效果展示​

组合分组小计：点击预览示例2 以区域为分组依据，设置纵向小计和横向小计 小计：横向计算，单价*金额 合计：金额列组内相加

2、创建一个名为区域省份销售统计的数据报表​

创建一个区域销售表，有七个字段：ID、数量、区域名称、年、省份、价格、月份

2.1 为区域省份销售统计创建SQL数据源​

部分数据展示

2.2 将字段拖拽到设计器页面​

2.3 设置横向分组、纵向分组、动态属性、横向小计​

此步骤涉及到横向纵向组合动态列和交叉报表和横向分组小计

横向纵向组合动态列请参考 横向纵向组合动态列 交叉报表请参考 交叉报表 横向分组小计请参考 横向分组小计

  • 地区(areaname)纵向分组： #{jm.group(areaname)}
  • 部门(province)纵向分组： #{jm.group(province)}
  • 年份(year)横向分组： #{jm.groupRight(year)}年
  • 月份(month)横向分组： #{jm.groupRight(month)}月
  • 横向分组字段：
    • 单价(#{jm.dynamic(price)})
    • 金额(#{jm.dynamic(amount)})
  • 横向小计(#{jm.compute(price*amount)})

2.4、重点来了->设置分组小计​

#{jm.group(areaname)}设置为分组依据

#{jm.dynamic(amount)}设置成求和列

保存预览

分组小计列位置调整​

版本支持version 1.7.0+ date 20240218

在1.7.0之前的版本中，分组小计将在「分组依据」的后一列展示；这会导致：

  1. 最后一个分组字段的分组小计将没有地方展示
  2. 并不符合用户的使用习惯和直觉；

所以，在1.7.0版本后，纵向分组小计的小计字段会与「分组依据」列保持在一列，如下：

自定义分组小计​

版本支持version 1.7.4+ date 20240419

效果展示​

设置方式：​

特殊规则注意​

设置动态单元格类型

当数据不完整时系统默认会进行补0操作便于小计表达式计算，如果该单元格不是数值类型则需要设置为文本，设置文本后该单元格显示为空；如：备注字段为空时，不需要展示为0，则设置备注字段类型为文本。

数据格式 重要！！！(数据必须完整)​

特别注意：当有横向分组时，数据中第一条数据必须完整；例如：期望展示1-12月的数据，则第一条数据1-12月必须都有数据。

  • SQL数据：

  • 返回JSON格式：
{
	"data": [{
		"amount": "100",
		"month": "1",
		"areaname": "华北",
		"year": "2020",
		"price": "5",
		"id": "1",
		"dept": "河北",
		"settleamount": "100"
	},
	{
		"amount": "200",
		"month": "2",
		"areaname": "华北",
		"year": "2020",
		"price": "5",
		"id": "10",
		"dept": "河北",
		"settleamount": "200"
	},
	{
		"amount": "202",
		"month": "2",
		"areaname": "华北",
		"year": "2020",
		"price": "6",
		"id": "12",
		"dept": "河南",
		"settleamount": "202"
	},
	{
		"amount": "203",
		"month": "1",
		"areaname": "华东",
		"year": "2020",
		"price": "6",
		"id": "13",
		"dept": "辽宁",
		"settleamount": "203"
	},
	{
		"amount": "204",
		"month": "2",
		"areaname": "华东",
		"year": "2020",
		"price": "6",
		"id": "14",
		"dept": "辽宁",
		"settleamount": "204"
	},
	{
		"amount": "205",
		"month": "1",
		"areaname": "华南",
		"year": "2020",
		"price": "5",
		"id": "15",
		"dept": "湖北",
		"settleamount": "205"
	},
	{
		"amount": "206",
		"month": "2",
		"areaname": "华南",
		"year": "2020",
		"price": "5",
		"id": "16",
		"dept": "湖北",
		"settleamount": "206"
	},
	{
		"amount": "207",
		"month": "1",
		"areaname": "华南",
		"year": "2020",
		"price": "5",
		"id": "17",
		"dept": "安徽",
		"settleamount": "207"
	},
	{
		"amount": "208",
		"month": "2",
		"areaname": "华南",
		"year": "2020",
		"price": "5",
		"id": "18",
		"dept": "安徽",
		"settleamount": "208"
	},
	{
		"amount": "201",
		"month": "2",
		"areaname": "华北",
		"year": "2021",
		"price": "5",
		"id": "20",
		"dept": "河北",
		"settleamount": "201"
	},
	{
		"amount": "200",
		"month": "1",
		"areaname": "华北",
		"year": "2021",
		"price": "5",
		"id": "21",
		"dept": "河北",
		"settleamount": "200"
	},
	{
		"amount": "202",
		"month": "2",
		"areaname": "华北",
		"year": "2021",
		"price": "6",
		"id": "22",
		"dept": "河南",
		"settleamount": "202"
	},
	{
		"amount": "209",
		"month": "1",
		"areaname": "华北",
		"year": "2021",
		"price": "6",
		"id": "23",
		"dept": "河南",
		"settleamount": "209"
	},
	{
		"amount": "202",
		"month": "1",
		"areaname": "华北",
		"year": "2020",
		"price": "6",
		"id": "24",
		"dept": "河南",
		"settleamount": "202"
	},
	{
		"amount": "203",
		"month": "1",
		"areaname": "华东",
		"year": "2021",
		"price": "6",
		"id": "25",
		"dept": "辽宁",
		"settleamount": "203"
	},
	{
		"amount": "204",
		"month": "2",
		"areaname": "华东",
		"year": "2021",
		"price": "6",
		"id": "26",
		"dept": "辽宁",
		"settleamount": "204"
	},
	{
		"amount": "205",
		"month": "1",
		"areaname": "华南",
		"year": "2021",
		"price": "5",
		"id": "27",
		"dept": "湖北",
		"settleamount": "205"
	},
	{
		"amount": "206",
		"month": "2",
		"areaname": "华南",
		"year": "2021",
		"price": "5",
		"id": "28",
		"dept": "湖北",
		"settleamount": "206"
	},
	{
		"amount": "207",
		"month": "1",
		"areaname": "华南",
		"year": "2021",
		"price": "5",
		"id": "29",
		"dept": "安徽",
		"settleamount": "207"
	},
	{
		"amount": "208",
		"month": "2",
		"areaname": "华南",
		"year": "2021",
		"price": "5",
		"id": "30",
		"dept": "安徽",
		"settleamount": "208"
	},
	{
		"amount": "203",
		"month": "1",
		"areaname": "华东",
		"year": "2020",
		"price": "6",
		"id": "43",
		"dept": "天津",
		"settleamount": "203"
	},
	{
		"amount": "204",
		"month": "2",
		"areaname": "华东",
		"year": "2020",
		"price": "6",
		"id": "44",
		"dept": "天津",
		"settleamount": "204"
	},
	{
		"amount": "203",
		"month": "1",
		"areaname": "华东",
		"year": "2021",
		"price": "6",
		"id": "45",
		"dept": "天津",
		"settleamount": "203"
	},
	{
		"amount": "204",
		"month": "2",
		"areaname": "华东",
		"year": "2021",
		"price": "6",
		"id": "46",
		"dept": "天津",
		"settleamount": "204"
	}]
}