报表-横向拓展重复标题拓展

楼主
我是社区第735815位番薯,欢迎点我头像关注我哦~

报表-横向拓展重复标题拓展

该文章主要介绍在做报表时遇到的横向拓展的问题,并针对这个问题的一种解法,仅供参考,该文章主要从目标展示样式,期初处理时遇到的问题,以及后期如何解决这些问题的方向介绍

报表最终需求展示样式:

期初设计

  • 数据集:数据中表分为<主表>和<子表>,<主表>包括”工厂”和“工单号”的信息,<子表>包括”工单号”,“鼓风机编号”,“鼓风机频率”的信息

```sql

--

Design1

WITH temp1 AS(

SELECT 'GD001' AS 工单号,'没品' AS 工厂 FROM DUAL 

UNION ALL 

SELECT 'GD002' AS 工单号,'没品' AS 工厂 FROM DUAL 

UNION ALL 

SELECT 'GD003' AS 工单号,'没品' AS 工厂 FROM DUAL 

UNION ALL 

SELECT 'GD004' AS 工单号,'没品' AS 工厂 FROM DUAL 

)

SELECT t1.*

FROM temp1 t1

-- Design2

WITH temp1 AS (

SELECT 'GD001' AS 工单号,'#11' AS 鼓风机编号,'1' AS 鼓风机频率 FROM DUAL 

UNION ALL 

SELECT 'GD001' AS 工单号,'#112' AS 鼓风机编号,'2' AS 鼓风机频率 FROM DUAL 

UNION ALL 

SELECT 'GD002' AS 工单号,'#1111' AS 鼓风机编号,'3' AS 鼓风机频率 FROM DUAL 

UNION ALL 

SELECT 'GD002' AS 工单号,'#112' AS 鼓风机编号,'4' AS 鼓风机频率 FROM DUAL 

UNION ALL 

SELECT 'GD002' AS 工单号,'#113' AS 鼓风机编号,'5' AS 鼓风机频率 FROM DUAL 

UNION ALL 

SELECT 'GD003' AS 工单号,'#111' AS 鼓风机编号,'' AS 鼓风机频率 FROM DUAL 

UNION ALL 

SELECT 'GD003' AS 工单号,'#112' AS 鼓风机编号,'51' AS 鼓风机频率 FROM DUAL 

UNION ALL 

SELECT 'GD003' AS 工单号,'#113' AS 鼓风机编号,'2' AS 鼓风机频率 FROM DUAL 

UNION ALL 

SELECT 'GD003' AS 工单号,'#114' AS 鼓风机编号,'23' AS 鼓风机频率 FROM DUAL 

UNION ALL 

SELECT 'GD004' AS 工单号,'#11121' AS 鼓风机编号,'2' AS 鼓风机频率 FROM DUAL 

)

SELECT t1.*

FROM temp1 t1

```

  • 报表设计:

  • 期初设计展示:经过调试,发现标题不跟着数据移动,所以无法满足需求。

 

新的设计方案

上面的方式中,数据已经能够正确展示,但是标题未能横向拓展,所以标题能不能和数据一样展示在数据集中,和数据一样拓展。

  • 新的数据集

```sql

-- Design1 不变

-- Design2

WITH temp1 AS (

SELECT 'GD001' AS 工单号,'#11' AS 鼓风机编号,'1' AS 鼓风机频率 FROM DUAL 

UNION ALL 

SELECT 'GD001' AS 工单号,'#112' AS 鼓风机编号,'2' AS 鼓风机频率 FROM DUAL 

UNION ALL 

SELECT 'GD002' AS 工单号,'#1111' AS 鼓风机编号,'3' AS 鼓风机频率 FROM DUAL 

UNION ALL 

SELECT 'GD002' AS 工单号,'#112' AS 鼓风机编号,'4' AS 鼓风机频率 FROM DUAL 

UNION ALL 

SELECT 'GD002' AS 工单号,'#113' AS 鼓风机编号,'5' AS 鼓风机频率 FROM DUAL 

UNION ALL 

SELECT 'GD003' AS 工单号,'#111' AS 鼓风机编号,'' AS 鼓风机频率 FROM DUAL 

UNION ALL 

SELECT 'GD003' AS 工单号,'#112' AS 鼓风机编号,'51' AS 鼓风机频率 FROM DUAL 

UNION ALL 

SELECT 'GD003' AS 工单号,'#113' AS 鼓风机编号,'2' AS 鼓风机频率 FROM DUAL 

UNION ALL 

SELECT 'GD003' AS 工单号,'#114' AS 鼓风机编号,'23' AS 鼓风机频率 FROM DUAL 

UNION ALL 

SELECT 'GD003' AS 工单号,'#11121' AS 鼓风机编号,'2' AS 鼓风机频率 FROM DUAL 

)

SELECT t1.*,'鼓风机编号' AS 编号,'鼓风机频率' AS 频率

FROM temp1 t1

```

 

  • 报表设计:C1,C2,D1,D2上父格无;C1左父格B1;D1左父格C1;C2左父格B2;D2左父格C2;B1,B2的上父格左父格都为无,A2,C2数据设置为列表,A2,B2拓展方向纵向,C1,C2,D1,D2拓展方向横向,C2设置过滤工单号=B2。

  • 报表设计展示:发现标题拓展多了……那么再设计,拓展根据Design2中查询的数据里,工单号最多有几个鼓风机编码,然后根据这个数据展示

  • 重新调整数据集Design2,获取每个工单有多少鼓风机号。

```sql

WITH temp1 AS (

SELECT 'GD001' AS 工单号,'#11' AS 鼓风机编号,'1' AS 鼓风机频率 FROM DUAL 

UNION ALL 

SELECT 'GD001' AS 工单号,'#112' AS 鼓风机编号,'2' AS 鼓风机频率 FROM DUAL 

UNION ALL 

SELECT 'GD002' AS 工单号,'#1111' AS 鼓风机编号,'3' AS 鼓风机频率 FROM DUAL 

UNION ALL 

SELECT 'GD002' AS 工单号,'#112' AS 鼓风机编号,'4' AS 鼓风机频率 FROM DUAL 

UNION ALL 

SELECT 'GD002' AS 工单号,'#113' AS 鼓风机编号,'5' AS 鼓风机频率 FROM DUAL 

UNION ALL 

SELECT 'GD003' AS 工单号,'#111' AS 鼓风机编号,'' AS 鼓风机频率 FROM DUAL 

UNION ALL 

SELECT 'GD003' AS 工单号,'#112' AS 鼓风机编号,'51' AS 鼓风机频率 FROM DUAL 

UNION ALL 

SELECT 'GD003' AS 工单号,'#113' AS 鼓风机编号,'2' AS 鼓风机频率 FROM DUAL 

UNION ALL 

SELECT 'GD003' AS 工单号,'#114' AS 鼓风机编号,'23' AS 鼓风机频率 FROM DUAL 

UNION ALL 

SELECT 'GD004' AS 工单号,'#11121' AS 鼓风机编号,'2' AS 鼓风机频率 FROM DUAL 

)

SELECT t1.*,'鼓风机编号' AS 编号,'鼓风机频率' AS 频率,t2.NUMB_MAX

FROM temp1 t1

INNER JOIN (SELECT t1.工单号,COUNT(0) AS NUMB_MAX

FROM temp1 t1

GROUP BY t1.工单号) t2 ON t1.工单号=t2.工单号

```

  • 报表调整。C1筛选前N项,添加公式;MAX(UNIQUEARRAY(Design2.select(NUMB_MAX)))

 

  • 报表展示:符合需求:文件好像上传不了,放弃了

分享扩散:

沙发
发表于 2024-7-3 15:28:11
扔个链接下载最后的报表设置吧:https://saber967.lanzouo.com/igE1C23epbja
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

1回帖数 1关注人数 1693浏览人数
最后回复于:2024-7-3 15:28

返回顶部 返回列表