Powerpivot - pivottable sorting

Question

I am using SQL Server 2012 Powerpivot with Excel 2010. I created a pivottable using the powerpivot fields. I am trying to sort the data in the pivottable by different columns - any column other than the first one. But it is not working. The data is always sorted by the first column.

example

ID	Year	ItemNumber
2009155644	2008	09716
2010115283	2010	07273
2010126046	1997	SR72
201022742	2009	30370
201047897	2008	07365

sorting on first column works just fine. But if I try to sort by ItemNumber, nothing changes. I see down and up arrows based on what I choose - desc or asc. But the actual order doesn't really change. There are more columns in the pivottable and  the user wants to be able to sort data by any column and not just first column. How can this be done? Is there something I am not setting correctly?

Thanks,
Sonal

Answer 1

It is hard to tell without seeing where you have these columns. But it sounds like the item number is under the year and therefore would only sort in that year. Since you only have one item under the year you do not see any sorting. Sorting works inside the parent level of the row label or column label. Item number would need to be above id for it to sort by item number. Remember, the pivot tables do not sort like plain table.

---

Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

Answer 2

Thanks Mike.

If the users want to be able to sort data like a plain table, would they have to copy the data to a separate sheet and do it there? What are their options?