帆软 FineReport 绘制漏斗图

七一建党节，祝党生日快乐！
夏日炎炎，周末在家，想起在用帆软做页面展示的时候，使用到了漏斗图，记录下来，方便查看。
以订单销量变化为例，分为五个阶段，商品浏览人数，点击商品人数，创建订单人数，加入购物车人数和支付订单任务等，来实现效果。展示效果大致如下图所示。

第一步、构造基本列表

新建数据集，根据五个阶段来组装SQL，即通过union连接后再group by 实现统计，查询SQL如下所示，仅做参考，可根据具体业务调整。

select 
data_type,
sum(aa.pvNum) as pvNum,
sum(aa.clickNum) as clickNum,
sum(aa.createNum) as createNum,
sum(aa.cartNum) as cartNum,
sum(aa.payNum) as payNum
from (
SELECT '商品浏览人数' AS 'data_type',COUNT(*) AS pvNum,0 AS clickNum,0 AS createNum,0 AS cartNum,0 AS payNumFROMtb_orderWHERE1 = 1 AND DATE_FORMAT(created_time, '%Y-%m-%d') >= DATE_ADD('${beginDate}', INTERVAL 1 DAY)AND DATE_FORMAT(created_time, '%Y-%m-%d') <= DATE_ADD('${endDate}', INTERVAL 1 DAY)UNION ALL SELECT '点击商品人数' AS 'data_type',0 AS pvNum,COUNT(*) AS clickNum,0 AS createNum,0 AS cartNum,0 AS payNumFROMtb_orderWHERE1 = 1 and order_status IN ('CO','OP','FN')AND DATE_FORMAT(created_time, '%Y-%m-%d') >= DATE_ADD('${beginDate}', INTERVAL 1 DAY)AND DATE_FORMAT(created_time, '%Y-%m-%d') <= DATE_ADD('${endDate}', INTERVAL 1 DAY)UNION ALL SELECT '创建订单人数' AS 'data_type',0 AS pvNum,0 AS clickNum,COUNT(*) AS createNum,0 AS cartNum,0 AS payNumFROMtb_orderWHERE1 = 1 and order_status = 'CO'AND DATE_FORMAT(created_time, '%Y-%m-%d') >= DATE_ADD('${beginDate}', INTERVAL 1 DAY)AND DATE_FORMAT(created_time, '%Y-%m-%d') <= DATE_ADD('${endDate}', INTERVAL 1 DAY)UNION ALL SELECT '加入购物车人数' AS 'data_type',0 AS pvNum,0 AS clickNum,0 AS createNum,COUNT(*) AS cartNum,0 AS payNumFROMtb_orderWHERE1 = 1 and order_status = 'CA'AND DATE_FORMAT(created_time, '%Y-%m-%d') >= DATE_ADD('${beginDate}', INTERVAL 1 DAY)AND DATE_FORMAT(created_time, '%Y-%m-%d') <= DATE_ADD('${endDate}', INTERVAL 1 DAY)
UNION ALL SELECT '支付订单人数' AS 'data_type',0 AS pvNum,0 AS clickNum,0 AS createNum,0 AS cartNum,COUNT(*) AS payNumFROMtb_orderWHERE1 = 1 and order_status = 'OP'AND DATE_FORMAT(created_time, '%Y-%m-%d') >= DATE_ADD('${beginDate}', INTERVAL 1 DAY)AND DATE_FORMAT(created_time, '%Y-%m-%d') <= DATE_ADD('${endDate}', INTERVAL 1 DAY)
) aa

绘制列表，对应字段绑定，效果如下图所示。

在浏览器预览效果如下。

第二步、绘制漏斗图

在列表下方单元格，点击属性列表，选择插入元素-插入图表，在类型中选择漏斗图(新特性)，操作如下图所示。

构造数据源，类似的，五个阶段要分别拼接，展示值和对应的显示名要分开，即 key-values形式，注意要添加排序字段。

SELECT data_type, num
FROM(SELECT '商品浏览人数' AS 'data_type',COUNT(*) AS num,1 AS ordSeqFROMtb_orderWHERE1 = 1 AND DATE_FORMAT(created_time, '%Y-%m-%d') >= DATE_ADD('${beginDate}', INTERVAL 1 DAY)AND DATE_FORMAT(created_time, '%Y-%m-%d') <= DATE_ADD('${endDate}', INTERVAL 1 DAY)UNION ALL SELECT '点击商品人数' AS 'data_type',COUNT(*) AS num,2 AS ordSeqFROMtb_orderWHERE1 = 1 and order_status IN ('CO','OP','FN')AND DATE_FORMAT(created_time, '%Y-%m-%d') >= DATE_ADD('${beginDate}', INTERVAL 1 DAY)AND DATE_FORMAT(created_time, '%Y-%m-%d') <= DATE_ADD('${endDate}', INTERVAL 1 DAY)UNION ALL SELECT '创建订单人数' AS 'data_type',COUNT(*) AS num,3 AS ordSeqFROMtb_orderWHERE1 = 1 and order_status = 'CO'AND DATE_FORMAT(created_time, '%Y-%m-%d') >= DATE_ADD('${beginDate}', INTERVAL 1 DAY)AND DATE_FORMAT(created_time, '%Y-%m-%d') <= DATE_ADD('${endDate}', INTERVAL 1 DAY)UNION ALL SELECT '加入购物车人数' AS 'data_type',COUNT(*) AS num,4 AS ordSeqFROMtb_orderWHERE1 = 1 and order_status = 'CA'AND DATE_FORMAT(created_time, '%Y-%m-%d') >= DATE_ADD('${beginDate}', INTERVAL 1 DAY)AND DATE_FORMAT(created_time, '%Y-%m-%d') <= DATE_ADD('${endDate}', INTERVAL 1 DAY)
UNION ALL SELECT '支付订单人数' AS 'data_type',COUNT(*) AS num,5 AS ordSeqFROMtb_orderWHERE1 = 1 and order_status = 'OP'AND DATE_FORMAT(created_time, '%Y-%m-%d') >= DATE_ADD('${beginDate}', INTERVAL 1 DAY)AND DATE_FORMAT(created_time, '%Y-%m-%d') <= DATE_ADD('${endDate}', INTERVAL 1 DAY)) main
ORDER BY ordSeq asc
;

在编辑栏-数据，数据来源选择数据集数据，即上一步新建的，选择使用字段值，指定系列名和值，效果如下所示。

在编辑栏-样式，可设置漏斗图的标题，图例等信息，效果所下图所示。

放大图的范围，即选中区域后，合并单元格，如下图所示。

在图例页签下，勾选图例可见，在浏览器预览效果如下图所示。

比如可以使用标签，在编辑栏-格式，标签下-标签，使用标签。

在浏览器展示效果如下图所示。

以上就是漏斗图添加的过程，帆软自带有很多属性，可以调试查看效果，祝好！

异常处理

【1】结果显示乱码，如？？？？
处理方案：
在数据库连接后指定编码方式为 utf8

?useUnicode=true&characterEncoding=utf8