禁用Excel数据透视表“组字段”

The setup is Excel 2013 PivotTables, getting data from a SSAS2014 cube.

设置是Excel 2013数据透视表，从SSAS2014数据集获取数据。

The requirement is to allow users to aggregate measures over the Date dimension, using a strange kind of week in which Friday is the first day of the week.

要求是允许用户在日期维度上聚合度量，使用一种奇怪的周，周五是一周的第一天。

Our time dimension has way too many hierarchies already, so I was hoping that the built-in Excel pivot-table "Group Field" functionality would allow users to show data at the Day level, and then aggregate by "weeks starting with Friday" - without us having to build a new Week hierarchy into the dimension and reprocess the entire cube.

我们的时间维度已经太多的层次结构,所以我希望内置的Excel数据透视表“组场”功能将允许用户显示数据在天级,然后总“周周五开始”——不用我们构建新的一周层次结构维度和再加工整个立方体。

No go, though. I can't get this "Group Field" command (in the PivotTable Tools ribbon, Analyze section) to ever be enabled when I click on a date field. (To be precise, I click on a particular day in the pivot-table; I click on the attribute over in the Rows section of the PivotTable Fields box; I click on the row header - no difference).

不过,没有去。当我单击一个日期字段时，我无法使这个“Group Field”命令(在数据透视工具ribbon, Analyze部分)启用。(准确地说，我点击了数据透视表中的某一天;单击数据透视表字段框的行部分中的属性;我点击行标题-没有区别)。

Here's what I've tried:

这是我试过:

The Days level of the dimension shows in the Pivot-Table in US format (mm/dd/yyyy). I thought this data, in combination with my machine locale settings (European date format dd/mm/yyyy) might be confusing Excel, so I changed the locale to US through Control Panel and reopened the .xlsx file. No difference.
在美国格式的数据透视表(mm/dd/yyyy)中显示的维数的天数。我认为，这些数据与我的机器语言环境设置(欧洲日期格式dd/mm/ yyyyy)结合在一起可能会混淆Excel，因此我通过控制面板将语言环境更改为US，并重新打开.xlsx文件。没有区别。
Checked the underlying dimension attribute. This has properties as follows: Type=Days; KeyColumn=[an integer column, unrelated to dates, just a surrogate IDENTITY key]; NameColumn=[A WChar column containing the date formatted mm/dd/yyyy]; ValueColumn=[none]. OK, I thought - there's nothing Date-like there, maybe that's why Excel can't figure out how to group this data.
检查基础维度属性。它的属性如下:Type=Days;KeyColumn=[一个整数列，与日期无关，只是一个代理身份键];NameColumn=[包含格式化为mm/dd/yyyy的日期的WChar列];ValueColumn =[所有]。好吧，我想，这里没有什么数据，也许这就是为什么Excel不知道如何对这些数据进行分组。
Opened up a pivot-table on AdventureWorks to see what that does. Using the Date attribute of the Date dimension - Excel still doesn't enable "Group Field". Looking at the underlying dimension design, the Date attribute has slightly different properties: Type=Date; KeyColumn=[an integer column of form 20150807 for today, for example]; NameColumn=[a Wchar column]; ValueColumn=[a column of type Date].
在AdventureWorks上打开一个数据透视表，看看它的作用。使用日期维度的日期属性—Excel仍然不能启用“组字段”。查看基础维度设计，Date属性有稍微不同的属性:Type=Date;KeyColumn=[当前格式20150807的整数列];NameColumn =(Wchar列);ValueColumn=[类型日期的一列]。

So I'm confused. Format Cells is a good quick way to find out if Excel is understanding cell contents as dates: but Format Cells doesn't work in the Row Labels of the PivotTable (either against AdventureWorks or against my cube).

所以我困惑。格式化单元格是一种很好的快速方法，可以发现Excel是否理解单元格内容作为日期:但是格式单元格不能在数据透视表的行标签中工作(不是针对AdventureWorks，也不是针对我的多维数据集)。

Is there actually a way to make "Group Field" work on date dimensions in Pivot-Tables/SSAS? I hoped I'd find out through AdventureWorks, but that doesn't work either.

是否有一种方法可以使“组字段”在数据透视表/SSAS中的日期维度上工作?我希望我能通过冒险工程找到答案，但那也不管用。

The closest parallel I can find on-line is here, where people suggest that the problem is Excel not understanding data as dates. But the answers in that thread are all aimed at people who pivot-tabling on data imported to Excel, rather than against SSAS:

我能在网上找到的最接近的相似点是这里，在那里人们认为问题是Excel而不是把数据理解为日期。但是这个线程中的答案都是针对那些对导入到Excel的数据进行数据透视制表的人，而不是针对SSAS:

2 个解决方案

#1

I was having the same problem. I think that I have it fixed.

我也有同样的问题。我想我把它修好了。

Go to File > Options > Advanced
点击文件>选项> Advanced。
Scroll down to the Data section
向下滚动到数据部分。
Uncheck "Prefer the Excel Data Model when creating Pivot Tables, QueryTables, and Data Connection"
不检查“在创建数据透视表、查询表和数据连接时更喜欢Excel数据模型”

This fixed it for me. Good Luck!

这是给我的。好运！

#2

When you create your pivot table off whatever data set there is a box prompting you to add the pivot to the data model. I'm not sure how that works with OLAP cubes but your pivots are going to the data model which will prevent you from grouping data within one file, as something added to the data set will be grouped together. Essentially breaking your ability to group within one data file.

当您从任何数据集创建数据透视表时，都会有一个方框提示您将透视添加到数据模型。我不确定如何使用OLAP多维数据集，但是您的数据点将进入数据模型，这将阻止您将数据分组到一个文件中，因为添加到数据集的内容将被分组在一起。实质上破坏了在一个数据文件中进行分组的能力。

#1