CODEWITHSUNDEEP
sql-serverexceldatepivot-table
Cenderze
Cenderze

Reputation: 1222

Can't group on this selection using Dates column from SQL in Pivot Table in Excel

I have a pivot table in Excel, which I'd like to be able to performing Grouping on using the SaleDate column.

However, when I've created my Pivot Table, right click an element in the field and choose Grouping... I get the error that:

Cannot Group on this selection

Which I've figured is because there is either

1) Blanks in the column, or

2) The column is not of date type Date in Excel

I've copied the whole column to Notepad++ and performed a Find what: (a blankspace) but that gave nothing in return, i.e. there are no blank spaces in the columns.

That leaves option number 2, and since I can't filter the SaleDate column on Year or Month it seems to in fact be interpreted as a text rather than a Date.

I'm using a SQL database as a source, which I have tried to adjust to parry this (my raw data to the SQL is of data type numeric, hence I first need to convert it to varchar and subsequently to date (note that these are Three different approaches I have used to adjust the date in SQL. I have noticed that the table to which I save the data is indeed of data type date in SQL):

left(convert(date,convert(varchar,Rawdate,110),110),7) as SaleDate
convert(date,convert(varchar,Rawdate,112),112) as SaleDate
convert(date,convert(varchar,Rawdate,110),110) as SaleDate

which returns, in order, yyyy-mm, yyyy-mm-dd, yyyy-mm-dd but none of these works to either Group on in the Pivot Table, or filter on Months or year in Excel.

Upvotes: 2

Views: 1148

Answers (2)

KtX2SkD
KtX2SkD

Reputation: 752

While I never worked specifically on Excels that utilize SQL-Server directly, I know SQL-Server has many date & time types, unlike .NET's C# which has fewer, or Excel which has only one(1). The types of SQL-Server itself are not that cooperative with each other to begin with(2), so I wouldn't be surprised if issues arise from even the tiniest differences when trying to port to other technologies, which I faced a few times.

With that in mind, and your evidence of a likely failure in date conversion, plus the chained conversions you mentioned, my first suggestion is to feed the Excel a different date type, and my first choices would be datetime or datetime2, for being the most popular, the most complete, and the most similar to Excel's lonely type.

(1): It's more like zero, it's just an integer with everybody around it giving it special treatment, which they fail at half the time.
(2): Why would int to/from datetime be fine, but int to/from datetime2 is not...

Upvotes: 1

jeffreyweir
jeffreyweir

Reputation: 4834

If you make the field of interest a rowfield, and click on the "Filter" triangle in the column heading, then often right to the bottom of the list in that PivotFilter box you'll see the item(s) causing you the problem. Be aware that it might be text that simply looks like a date, or it might be something more obvious as per the basic example in the screenshot below:

enter image description here

As per my comment, another way to diagnose what's going on is by taking just a few of the items that you are sure are dates, putting them into a range, making a PivotTable out of them, and seeing if you can group them. If you can, then you know that the problem is indeed likely some text in the data. If you can't, then it's likely you have text that still needs to be converted to dates...but you'll need to post some examples here in order for us to give you suggestions on how to turn it into something Excel recognizes as a date.

Upvotes: 1

Related Questions