VBA将OLAP多维数据集中的数据透视筛选器更改为一个范围 [英] VBA to change Pivot Filter in OLAP cube to a range

问题描述

我有一个数据透视表，该数据透视表从OLAP多维数据集中提取数据，并且我想创建一个宏以根据另一个单元格中的值过滤包含年度最佳"的数据透视字段，以便我可以轻松地更改表格的时间范围.

I have a pivot table that pulls data from an OLAP cube, and I'd like to create a macro to filter a pivot field that contains 'Week of Year' based on the value in another cell, so that I can easily change the time frame of the table.

我对多维数据集的经验非常有限，因此我使用了宏记录器来查看正在发生的事情.我得到的示例代码是:

My experience with cube sets is very limited, so I used the macro recorder to see what was happening. The sample code I got was:

ActiveSheet.PivotTables("PivotTable3").PivotFields( _
        "[Time].[Week of Year].[Week of Year]").VisibleItemsList = Array( _
        "[Time].[Week of Year].&[1]", "[Time].[Week of Year].&[2]", _
        "[Time].[Week of Year].&[3]")

有没有一种方法可以简化此过程，以便将数组1的过滤器设置为n，而n是另一个单元格的值?我的目标是通过在指定的字段中输入15来显示1到15周.

Is there a way to simplify this so that it sets the filter with the array 1 to n, with n being value of another cell? My aim to to be able to show weeks 1 through 15, by entering 15 in a specified field.

推荐答案

应该是这样的:

Dim aWeeks()
Dim n                     As Long
Dim x                     As Long

n = Range("A1").Value

ReDim aWeeks(n - 1)
For x = 1 To n
    aWeeks(x - 1) = "[Time].[Week of Year].&[" & x & "]"
Next x
ActiveSheet.PivotTables("PivotTable3").PivotFields( _
        "[Time].[Week of Year].[Week of Year]").VisibleItemsList = aWeeks

这篇关于VBA将OLAP多维数据集中的数据透视筛选器更改为一个范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！