VBA设置Power Pivot数值型字段的切片器

我有一个Excel的报告文档,用Power Query将数据加载到数据模型中,然后基于数据模型创建Power Pivot数据透视表,并添加切片器提供快速选择功能。简化的报告结构如下图:

报告结构示意图
报告结构示意图

为了偷懒实现自动化,使用VBA来自动设置切片器。Excel数据模型本质上是OLAP,只要设置SlicerCache.VisibleSlicerItemsList属性就可以控制选中的成员,比如要选择1月份就写:

Sub SetSlicer()
    Dim sc As SlicerCache
    Set sc = ThisWorkbook.SlicerCaches("Slicer_month")
    sc.VisibleSlicerItemsList = Array("[Fact].[Month].&[1]")
End Sub

这里Array中的元素用的是MDX语言,其中"[Fact].[Month].&[1]"意思是Fact表的Month列的值为1的成员,很简单易懂。然而——

错误提示

不慌,遇到这种问题可以把透视表转换成公式来看看这个成员应该怎么写:

数据透视表转换成公式

转换后的Cube函数
数据透视表转换后就会变成CubeMember, CubeSet, CubeValue函数

原来是数字后面多了一个点,虽然不知道为什么,总之跟着加上就没问题了:

Sub SetSlicer()
    Dim m As Integer, sc As SlicerCache
    m = 1
    Set sc = ThisWorkbook.SlicerCaches("Slicer_Month")
    sc.VisibleSlicerItemsList = Array("[Fact].[Month].&[" & m & ".]")
End Sub

这样跑了9个月安然无恙,然而到了10月份问题又出现了:

错误提示

依然用上面的办法查看成员,这次变成了[Fact].[Month].&[1.E1]。可是为什么是1.E1呢?顺手看了下11月和12月,分别是[Fact].[Month].&[1.1E1][Fact].[Month].&[1.1E1],这下就全明白了,原来是科学记数法嘛,之前数字后面之所以有个点,其实那是小数点,只是省略了小数位的0。
不过依然有问题,这并不是通常的科学记数法的写法,无论是工作表函数TEXT()还是VBA函数Format()转换出来的结果都不对。简单粗暴的解决方法是直接硬写12个月的MDX字符串,这样固然能快速解决问题,但无法应对以后可能遇到的其他数字。

仔细想了下,既然用了科学记数法,那就意味着该列的类型很可能是浮点型,一旦想到这一点,验证问题和解决源头都很容易了。首先在Power Pivot数据模型窗口选中Month列,看到数据类型果然是Decimal:

PowerPivot数据类型
Power Pivot列的数据类型包括整数、小数(浮点型)等

这里当然可以手动改成整型,但每次手动修改模型的数据类型显然不是正常操作。进一步追溯,由于数据是用Power Query导入的,那么源头应该是转换数据一开始指定的数据类型不对:

let
    Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", type number}, {"Month", type number}, {"Sales", type number}})
in
    #"Changed Type"

这下就清楚了,当时为了偷懒统一直接把所有数值都设为了基础类型type number,也就是浮点型。只要在这里把年和月的类型改成Int64.Type,最后数据透视表及切片器里的Month就会变成整型,进而VBA的MDX正常写[Fact].[Month].&[1]即可。要查看切片器的成员,除了把透视表转换成公式之外,也可以用VBA直接输出SlicerCache对象的VisibleSlicerItemsList属性:

Sub ShowSlicerItems()
    Dim sc As SlicerCache
    Set sc = ThisWorkbook.SlicerCaches("Slicer_Month")
    MsgBox Join(sc.VisibleSlicerItemsList, vbCrLf)
End Sub

查看VisibleSlicerItemsList的元素

注意修改数据格式之后,相关的切片器、透视表的筛选会被重置,需要重新进行设置。

P.S.

使用VBA选择数值型的OLAP成员时,名称必须与该成员的内部名称完全一致,否则会报错。但如果使用工作表函数CubeMember就没这种问题,即使是浮点型数据也可以直接以整型写法的MDX来选择,不会报错:

用CubeMember和CubeValue函数取值

可以猜测Cube函数应该对此做了特别转换。

P.S. 2

对于年月日这类一般只用作维度的数值字段,另一种处理方法是将它们指定为文本(字符串)格式,好处是选择区域快速求和时,不会不小心被包括进加总范围。不过这样的话默认排列会变成{1, 10, 11, 12, 2, 3, …},解决方法是在数据模型中再添加一列隐藏的数值型月份数据,仅用于对文本型的月份列进行排序,尽管比较麻烦,但这确实是比较正统的解决方案。

发表评论

您的电子邮箱地址不会被公开。