I want to clear the cache of a PivotTable report which is attached to a SSAS cube. It should be easy, in the connection definition there is a checkbox which should serve this exact purpose:
我想清除附加到SSAS多维数据集的数据透视表的缓存。应该很简单，在连接定义中有一个复选框，它应该达到这个目的:
"Remove data from the external data range before saving the workbook"
“在保存工作簿之前，从外部数据范围删除数据”
(参见:https://support.office.com/en - us/article/refresh -连接-进口数据- e76a38b0 e2e1 - 400 b - 9 - f2f c87b9b18c092)
Unfortunately the checkbox is grayed out and I could not find a way to activate it. I am aware that I could solve this with VBA or with an extension, but the preferred way should be to be able to activate this checkbox.
不幸的是，复选框变灰了，我找不到激活它的方法。我知道我可以用VBA或扩展来解决这个问题，但是首选的方法应该是能够激活这个复选框。
The server is Microsoft SQL Server 2014, enterprise edition, version 12.0.2000.8
服务器是Microsoft SQL server 2014, enterprise edition，版本12.0.2000.8。
3 个解决方案
#1
1
I didn't have success with that Excel feature either so I wrote this Excel add-in feature. Give it a try and see if that's what you are looking for: http://olappivottableextend.codeplex.com/wikipage?title=Clear%20PivotTable%20Cache&referringTitle=Home
我也没有成功的使用Excel功能，所以我写了这个Excel插件功能。试试看，看看这是不是你想要的:http://olappivottableextend.codeplex.com/wikipage?
#2
1
It seems that the option gets disabled when the connection manager includes "Multiple Tables", i.e. when the ODC connection has a command type "TableCollection".
当连接管理器包含“多个表”时，即当ODC连接具有命令类型“TableCollection”时，该选项似乎被禁用。
<odc:CommandType>TableCollection</odc:CommandType>
< odc:CommandType > TableCollection < / odc:CommandType >
Below is the option to avoid using in order to have the "Remove data from the external data range before saving the workbook" check box available.
下面是避免使用的选项，以便“在保存工作簿之前从外部数据范围中删除数据”复选框可用。
#3
-1
just check the box "Refresh data when opening the file"
勾选“打开文件时刷新数据”
#1
1
I didn't have success with that Excel feature either so I wrote this Excel add-in feature. Give it a try and see if that's what you are looking for: http://olappivottableextend.codeplex.com/wikipage?title=Clear%20PivotTable%20Cache&referringTitle=Home
我也没有成功的使用Excel功能，所以我写了这个Excel插件功能。试试看，看看这是不是你想要的:http://olappivottableextend.codeplex.com/wikipage?
#2
1
It seems that the option gets disabled when the connection manager includes "Multiple Tables", i.e. when the ODC connection has a command type "TableCollection".
当连接管理器包含“多个表”时，即当ODC连接具有命令类型“TableCollection”时，该选项似乎被禁用。
<odc:CommandType>TableCollection</odc:CommandType>
< odc:CommandType > TableCollection < / odc:CommandType >
Below is the option to avoid using in order to have the "Remove data from the external data range before saving the workbook" check box available.
下面是避免使用的选项，以便“在保存工作簿之前从外部数据范围中删除数据”复选框可用。
#3
-1
just check the box "Refresh data when opening the file"
勾选“打开文件时刷新数据”