Excel Pivot Table “Group Field” disabled

2
votes

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

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.

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).

Here's what I've tried:

  1. 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.
  2. 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.
  3. 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].

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).

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.

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:

2
exceldatessasgroupingpivot-table
Checked my Date dimension members. Could it be the Unknown member (Name="Date not assigned", Key=Null) that's putting sand in Excel's gears? From online comments by people using pivottables with an Excel back-end (i.e. not SSAS), seems that just one non-date-parseable value will make Excel refuse to group. – SebTHU
OK, I've now tested this with a very basic cube. Even on a numeric (smallint) dimension attribute (I called the dimension "Age"), which excludes any "date-weirdness" as a possible cause, Excel simply will not enable Group Field in an SSAS Pivot Table. Can anyone confirm this? – SebTHU

2 Answers

2
votes

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

  1. Go to File > Options > Advanced
  2. Scroll down to the Data section
  3. Uncheck "Prefer the Excel Data Model when creating Pivot Tables, QueryTables, and Data Connection"

This fixed it for me. Good Luck!

0
votes

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.