使用专业的报表工具设计报表模板时,数据准备是一个关键环节。数据准备的核心,实际上是数据库查询SQL语句的编写。
在一个典型的交叉分析报表中,需要显示每个大区、每个月度的销售业绩,如下图:
这个报表的数据集查询语句大致如下:
select DATEPART(month,订购日期) 月份
, 销售大区
, SUM(订单金额) 销售额
from Demo_销售明细
where 订购日期 >='2019-1-1'
and 订购日期 <'2020-1-1'
group by DATEPART(month,订购日期)
, 销售大区
年初时,由于数据很少,表格列上的月份只有1个月,或者很少几个月份,表格行上的大区可能也很少,表格可能会很难看,如下图:
如果想要保证表格的样式不受数据量的影响,就要自动“补齐”月份和大区的列和行。为此,需要在准备报表数据集时想办法。
首先,准备月份列表,12个月,每个月一条记录,SQL语句如下:
select 1 M
union select 2
union select 3
union select 4
union select 5
union select 6
union select 7
union select 8
union select 9
union select 10
union select 11
union select 12
这个语句是一个不带FROM子句的SQL,其中第一个语句 select 1 M 中的M,是字段别名。后续语句通过UNION合并起来,结果就是一个单字段、12条记录的数据集。如下图:
然后,准备大区列表。为了避免因某些大区暂无销售业绩导致大区列表不全,应从基础资料表查询全部的大区。如果没有这种基础资料表,也可以直接从所有年度的销售数据中抽取大区列表,语句的核心是DISTINCT关键字,SQL语句如下:
select distinct 销售大区 from Demo_销售明细
有了这两个列表,先将这两个列表通过无条件JOIN生成两者的交叉组合全集,再通过LEFT JOIN与实际销售数据关联,即可保证没有业绩的大区-月份组合也会出现在报表数据集中。SQL语句如下:
select MR.M, MR.销售大区, Sales.销售额 from
( select * from
( select 1 M
union select 2 union select 3 union select 4 union select 5 union select 6
union select 7 union select 8 union select 9 union select 10 union select 11 union select 12
) Months
inner join
( select distinct 销售大区 from Demo_销售明细 ) Regions
on 1=1
) MR left join
(
select DATEPART(month,订购日期) 月份
, 销售大区
, SUM(订单金额) 销售额
from Demo_销售明细
where 订购日期 >='2017-1-1'
and 订购日期 <'2017-1-2'
group by DATEPART(month,订购日期)
, 销售大区
) Sales
on Sales.月份=MR.M and Sales.销售大区=MR.销售大区
上面这种SQL语句可能看起来很复杂,不易理解。可以采用“通用表表达式(CTE)”改写为:
with Months as
( select 1 M
union select 2 union select 3 union select 4 union select 5 union select 6
union select 7 union select 8 union select 9 union select 10 union select 11 union select 12
)
, Regions as
( select distinct 销售大区 from Demo_销售明细
)
, Sales as
( select DATEPART(month,订购日期) 月份
, 销售大区
, SUM(订单金额) 销售额
from Demo_销售明细
where 订购日期 >='2017-1-1'
and 订购日期 <'2017-1-2'
group by DATEPART(month,订购日期)
, 销售大区
)
select M.M, R.销售大区, Sales.销售额 from
Months M inner join Regions R on 1=1
left join Sales on Sales.月份=M.M and Sales.销售大区=R.销售大区
这样的SQL语句相当于创建了3个临时的视图:Months,Regions,Sales,然后基于这些视图做查询。最终生成的数据集记录如下图:
绑定到报表表格之后,显示效果如下图:
可以看到,尽管大多数月份和大区都没有业绩数据,报表仍然完整显示了表格框架。
小结:
(1)用 select 1 union select 2... 这种UNION起来的常数字段值可生成临时的有限数据集合,比如月份列表。
(2)使用DISTINCT关键字,可从业务数据中获取某个分类的完整列表。
(3)使用CTE可简化复杂SQL的结构,既容易看懂,也便于调试。