JasperReport 交叉表crosstab的使用

目录

• 一、数据准备
• 二、设置SQL与字段
• 三、设计列表
• 四、设计显示

 

---

效果图：

准备工作

回到顶部

一、数据准备

DROP TABLE IF EXISTS `address_item_sex_value`;
CREATE TABLE `address_item_sex_value` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `address` varchar(255) DEFAULT NULL,
  `sex` varchar(255) DEFAULT NULL,
  `item` varchar(255) DEFAULT NULL,
  `value` int(11) DEFAULT NULL,
  `proportion` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of address_item_sex_value
-- ----------------------------
INSERT INTO `address_item_sex_value` VALUES ('1', '城区', '男生', '指标1', '2', '50');
INSERT INTO `address_item_sex_value` VALUES ('2', '远郊', '男生', '指标1', '3', '40');
INSERT INTO `address_item_sex_value` VALUES ('3', '近郊', '男生', '指标1', '1', '60');
INSERT INTO `address_item_sex_value` VALUES ('9', '城区', '女生', '指标1', '3', '40');
INSERT INTO `address_item_sex_value` VALUES ('10', '远郊', '女生', '指标1', '5', '50');
INSERT INTO `address_item_sex_value` VALUES ('11', '近郊', '女生', '指标1', '4', '60');
INSERT INTO `address_item_sex_value` VALUES ('15', '城区', '男生', '指标2', '6', '70');
INSERT INTO `address_item_sex_value` VALUES ('16', '远郊', '男生', '指标2', '3', '50');
INSERT INTO `address_item_sex_value` VALUES ('17', '近郊', '男生', '指标2', '5', '30');
INSERT INTO `address_item_sex_value` VALUES ('18', '城区', '女生', '指标2', '7', '20');
INSERT INTO `address_item_sex_value` VALUES ('19', '远郊', '女生', '指标2', '3', '10');
INSERT INTO `address_item_sex_value` VALUES ('20', '近郊', '女生', '指标2', '6', '50');
INSERT INTO `address_item_sex_value` VALUES ('21', '城区', '男生', '指标3', '2', '50');
INSERT INTO `address_item_sex_value` VALUES ('22', '远郊', '男生', '指标3', '3', '40');
INSERT INTO `address_item_sex_value` VALUES ('23', '近郊', '男生', '指标3', '1', '60');
INSERT INTO `address_item_sex_value` VALUES ('25', '城区', '女生', '指标3', '3', '40');
INSERT INTO `address_item_sex_value` VALUES ('26', '远郊', '女生', '指标3', '5', '50');
INSERT INTO `address_item_sex_value` VALUES ('27', '近郊', '女生', '指标3', '4', '60');
INSERT INTO `address_item_sex_value` VALUES ('28', '城区', '男生', '指标4', '6', '70');
INSERT INTO `address_item_sex_value` VALUES ('29', '远郊', '男生', '指标4', '3', '50');
INSERT INTO `address_item_sex_value` VALUES ('30', '近郊', '男生', '指标4', '5', '30');
INSERT INTO `address_item_sex_value` VALUES ('31', '城区', '女生', '指标4', '7', '20');
INSERT INTO `address_item_sex_value` VALUES ('32', '远郊', '女生', '指标4', '3', '10');
INSERT INTO `address_item_sex_value` VALUES ('33', '近郊', '女生', '指标4', '6', '50');

回到顶部

二、设置SQL与字段

SELECT address_item_sex_value.id,
    address_item_sex_value.address,
    address_item_sex_value.sex,
   address_item_sex_value.item,
    address_item_sex_value.value,
    1 a
FROM address_item_sex_value order by address_item_sex_value.address

SQL中有一个空闲的字段a、有一个默认值。很重要、是为了X轴的交叉表做考虑

回到顶部

三、设计列表

设置横列

1、选择字段a

2、设置total（按需要、这里不需要汇总列）

设置竖列

 

设置交叉值

 

回到顶部

四、设计显示

删除横列值

设置Column groups高度

设置高度为 0px

效果图

 设置 水平居中、垂直居中

表头部分说一下

表头放在ColumnHeader里面，按需自己组装即可。

 最终效果图

 

摘自：https://www.cnblogs.com/tusheng/p/10621520.html