FineReport 参数级联查询与树集分组序号

悠悠 2022-02-02 00:43 408阅读 0赞

### 一、参数级联查询 ###

一般情况下，多是使用下拉框直接查询报表记录。但有时会涉及到多级级联关系的参数查询，如利用省市区三级级联查询：

首先，需要找出三级级联的关系；然后，创建三个下拉框；最后，使用三级参数查询报表。

**1、创建好需要的数据表：表很重要**

省市区对应的父子关系表：

具体使用到关联省市区表的业务信息表： 测试简单创建的表

DROP TABLE IF EXISTS `t_project`;
    CREATE TABLE `t_project` (
      `id` int(11) NOT NULL,
      `name` varchar(200) DEFAULT NULL,
      `provice_city_county_id` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of t_project
    -- ----------------------------
    INSERT INTO `t_project` VALUES ('1001', '陕西-省内项目', '3');
    INSERT INTO `t_project` VALUES ('1002', '陕西西安-市内项目', '8');
    INSERT INTO `t_project` VALUES ('1003', '陕西西安-长安区内项目', '16');
    INSERT INTO `t_project` VALUES ('1004', '陕西西安-碑林区内项目', '17');
    INSERT INTO `t_project` VALUES ('1005', '陕西西安-长安区内项目', '16');
    INSERT INTO `t_project` VALUES ('1006', '陕西渭南-市内项目', '9');
    INSERT INTO `t_project` VALUES ('1007', '安徽-省内项目', '2');
    INSERT INTO `t_project` VALUES ('1008', '安徽-省内项目', '2');
    INSERT INTO `t_project` VALUES ('1009', '安徽芜湖-市内项目', '7');
    INSERT INTO `t_project` VALUES ('1010', '安徽芜湖-市内项目', '7');
    INSERT INTO `t_project` VALUES ('1011', '浙江-省内项目', '1');
    INSERT INTO `t_project` VALUES ('1012', '浙江-省内项目', '1');
    INSERT INTO `t_project` VALUES ('1013', '浙江-省内项目', '1');
    INSERT INTO `t_project` VALUES ('1014', '浙江杭州-市内项目', '4');
    INSERT INTO `t_project` VALUES ('1015', '浙江杭州-市内项目', '4');
    INSERT INTO `t_project` VALUES ('1016', '浙江杭州-西湖区内项目', '10');
    INSERT INTO `t_project` VALUES ('1017', '浙江杭州-滨江区内项目', '11');
    INSERT INTO `t_project` VALUES ('1018', '浙江杭州-萧山区内项目', '12');
    
    -- ----------------------------
    -- Table structure for `t_provice_city_county`
    -- ----------------------------
    DROP TABLE IF EXISTS `t_provice_city_county`;
    CREATE TABLE `t_provice_city_county` (
      `id` bigint(10) NOT NULL AUTO_INCREMENT,
      `parentid` bigint(10) DEFAULT NULL,
      `name` varchar(255) DEFAULT NULL,
      `code` varchar(255) DEFAULT NULL,
      `seqno` int(11) DEFAULT NULL,
      `levelno` int(11) DEFAULT NULL,
      `decode` varchar(255) DEFAULT NULL,
      `description` varchar(255) DEFAULT NULL,
      `status` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of t_provice_city_county
    -- ----------------------------
    INSERT INTO `t_provice_city_county` VALUES ('1', '0', '浙江省', '', null, '1', '0_1_', null, '1');
    INSERT INTO `t_provice_city_county` VALUES ('2', '0', '安徽省', '', null, '1', '0_2_', null, '1');
    INSERT INTO `t_provice_city_county` VALUES ('3', '0', '陕西省', '', null, '1', '0_3_', null, '1');
    INSERT INTO `t_provice_city_county` VALUES ('4', '1', '杭州市', null, null, '2', '0_1_4_', null, '1');
    INSERT INTO `t_provice_city_county` VALUES ('5', '1', '嘉兴市', null, null, '2', '0_1_5_', null, '1');
    INSERT INTO `t_provice_city_county` VALUES ('6', '2', '合肥市', null, null, '2', '0_2_6_', null, '1');
    INSERT INTO `t_provice_city_county` VALUES ('7', '2', '芜湖市', null, null, '2', '0_2_7_', null, '1');
    INSERT INTO `t_provice_city_county` VALUES ('8', '3', '西安市', null, null, '2', '0_3_8_', null, '1');
    INSERT INTO `t_provice_city_county` VALUES ('9', '3', '渭南市', null, null, '2', '0_3_9_', null, '1');
    INSERT INTO `t_provice_city_county` VALUES ('10', '4', '西湖区', null, null, '3', '0_1_4_10_', null, null);
    INSERT INTO `t_provice_city_county` VALUES ('11', '4', '滨江区', null, null, '3', '0_1_4_11', null, '1');
    INSERT INTO `t_provice_city_county` VALUES ('12', '4', '萧山区', null, null, '3', '0_1_4_12', null, '1');
    INSERT INTO `t_provice_city_county` VALUES ('13', '5', '桐乡市', null, null, '3', '0_1_5_13_', null, '1');
    INSERT INTO `t_provice_city_county` VALUES ('14', '6', '肥西县', null, null, '3', '0_2_6_14_', null, '1');
    INSERT INTO `t_provice_city_county` VALUES ('15', '7', '鸠江区', null, null, '3', '0_2_7_15_', null, '1');
    INSERT INTO `t_provice_city_county` VALUES ('16', '8', '长安区', null, null, '3', '0_3_8_16_', null, '1');
    INSERT INTO `t_provice_city_county` VALUES ('17', '8', '碑林区', null, null, '3', '0_3_8_17_', null, '1');
    INSERT INTO `t_provice_city_county` VALUES ('18', '9', '富平县', null, null, '3', '0_3_9_18_', null, '1');

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDAyODU0_size_16_color_FFFFFF_t_70][]     ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDAyODU0_size_16_color_FFFFFF_t_70 1][]

**  2、在  FineReport  创建数据集**

项目测试表

SELECT 
    	t1.name projectname,
    	t1.provice_city_county_id,
    	t2.name t2name,
    	t2.decode,
    	t2.status
    FROM 
    	t_project t1 left join t_provice_city_county t2 on t1.provice_city_county_id=t2.id
    where
     	1=1
     	${if(len(省) == 0, "", "and t2.decode like '" + 省 + "%'" )}
    	${if(len(市) == 0, "", "and t2.decode like '" + 市 + "%'" )}
    	${if(len(区县) == 0, "", "and t2.decode like '" + 区县 + "%'" )}

然后，创建 省、市、区数据集作为 参数查询的数据字典

**省**

SELECT decode, name FROM `t_provice_city_county` where levelno=1

**市**

SELECT decode, name FROM `t_provice_city_county` where levelno=2 and
    ${if(len(省)>0, "decode like '"+省+"%' ", "false")}

**区县**

SELECT decode, name FROM `t_provice_city_county` where levelno=3 and
    ${if(len(市)>0, "decode like '"+市+"%' ", "false")}

**3、 报表的制作**

**    1） 省市区三级级联关系的参数**

**   下图为参数省，其他同理**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDAyODU0_size_16_color_FFFFFF_t_70 2][]

**     2）报表展示内容：简单展示下**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDAyODU0_size_16_color_FFFFFF_t_70 3][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDAyODU0_size_16_color_FFFFFF_t_70 4][]    ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDAyODU0_size_16_color_FFFFFF_t_70 5][]

**     说明一下：**多级级联关系的参数查询时，前面一级没选，后面的参数都不可选。

### 二、树集分组序号    ###

在上面的基础上，进行树集分组序号显示

**1、再创建一个数据集用于树： 省市区表**

SELECT * FROM `t_provice_city_county`

**2、创建树数据集， 用到省市区表数据集的 id 和 parentid**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDAyODU0_size_16_color_FFFFFF_t_70 6][]

**3、报表内容进行树集分组序号展示：**

1）把要展示的信息放置好：

树数据集：B4 默认，B6 左父格为B4，B8 左父格为B6。然后都设置相同形态显示  
          B5-E5: 列表，左父格为B4，过滤为provice\_city\_county\_id = B4  
          B7-E7: 列表，左父格为B6，过滤为provice\_city\_county\_id = B6  
          B9-E9: 列表，左父格为B8，过滤为provice\_city\_county\_id = B8

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDAyODU0_size_16_color_FFFFFF_t_70 7][]

效果为：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDAyODU0_size_16_color_FFFFFF_t_70 8][]

2）设置分组序号和去掉空行

A4：插入公式为 numto(&B4, false)，左父格为B4  
           A5：条件属性 if(len(B5) > 0, seq(B4), "")，左父格为B5    
           A7：条件属性 if(len(B7) > 0, seq(B4), "")，左父格为B7  
           A9：条件属性 if(len(B9) > 0, seq(B4), "")，左父格为B9    
           A6：左父格为B4    
           A8：左父格为B6  
           A4-A9：扩展方向为纵向  
           B5-B9：添加无数据隐藏行的条件

len($$$) = 0  //"$$$"表示当前值

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDAyODU0_size_16_color_FFFFFF_t_70 9][]    ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDAyODU0_size_16_color_FFFFFF_t_70 10][]

参考文章：

[隐藏行时序号不连续的解决方案][Link 1]

效果为：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDAyODU0_size_16_color_FFFFFF_t_70 11][]

**   3）到此基本完成，根据需求添加一个求和个数**

各分层级别单独求个数， 然后合计各分层级别的数量，即：项目的总数。

C3：插入公式为SUM(C4) + SUM(C6) + SUM(C8)  
     C4：汇总（个数），过滤为：provice\_city\_county\_id = B4，其他默认  
     C6：汇总（个数），过滤为：provice\_city\_county\_id = B6，其他默认  
     C8：汇总（个数），过滤为：provice\_city\_county\_id = B8，其他默认

效果为：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDAyODU0_size_16_color_FFFFFF_t_70 12][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDAyODU0_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20190510114909272.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDAyODU0,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDAyODU0_size_16_color_FFFFFF_t_70 1]: /images/20220202/ae4a35a572514b5289f38efdc8724e71.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDAyODU0_size_16_color_FFFFFF_t_70 2]: /images/20220202/0adcc57168ac4a1abe246dffc5f6943a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDAyODU0_size_16_color_FFFFFF_t_70 3]: /images/20220202/8754addfa4ba4e969575dbaf6ccb7e3c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDAyODU0_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20190510150127990.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDAyODU0,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDAyODU0_size_16_color_FFFFFF_t_70 5]: /images/20220202/ca09a46301184431a1030718af6644e3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDAyODU0_size_16_color_FFFFFF_t_70 6]: /images/20220202/1ec00c1d3b8e4473bb0cbbb0c31341fb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDAyODU0_size_16_color_FFFFFF_t_70 7]: /images/20220202/1e4a39eff2e045ddbd9b65de87d9fe39.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDAyODU0_size_16_color_FFFFFF_t_70 8]: /images/20220202/c47e9ceb9cef46e7bfe06a380832a432.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDAyODU0_size_16_color_FFFFFF_t_70 9]: https://img-blog.csdnimg.cn/20190512114047228.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDAyODU0,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDAyODU0_size_16_color_FFFFFF_t_70 10]: /images/20220202/9e7350bab98f478fa967f29c95f69696.png
[Link 1]: http://help.finereport.com/doc-view-1929.html?tdsourcetag=s_pctim_aiomsg
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDAyODU0_size_16_color_FFFFFF_t_70 11]: /images/20220202/cb41ce5dbaec4380b591df6a74e4f768.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDAyODU0_size_16_color_FFFFFF_t_70 12]: /images/20220202/a125962f171648d8836865c19b9cf058.png