What happened to Pivot Table view in Access 2013?

Thanks for decoding the TLA.

Some of what I've said is clearly opinion. But what I've said about the focus of the Access product is mostly from team members past and present. So is fact.

And I have never denied that Access has always had features that cater to developers. That's why such an extensive developer community has grown up around it. But this has happened not because of any specific design on Microsoft's part but in spite of it.

No. Even that is just opinion. 2nd hand opinion. 

The Microsoft JET team people I spoke to at Tech Eds in the early 1990s gave me a very different impression. That's just opinion too.

At the end of the day it hardly matters what Microsoft intended. Thousands of developers delivered great solutions to small to mid sized database problems for over 20 years. Since 2003 its been getting harder to deliver as full range of solutions as before.

ADP, the security model, the upsizing wizard, merge replication (how could I forget that?) And now Pivot Tables have bitten the dust. If you're not a C# .Net developer building SQL Server solutions it seems you don't figure on Microsoft"s plans and I think that's a real shame.

JET (now called ACE) is NOT Access. JET is a part of Access, but, at the time you refer to it was a separate piece under the control of a separate team. It is only recently that the Access product team took over responsibility for JET hence the renaming to ACE. So anything you learned from that team doesn't apply directly to Access and is second hand opinion. Not what I have said.

I agree, it doesn't matter what Microsoft's original or current focus is. Whether by accident or design, Access has become the best Rapid Development platform out there. And yes that's my opinion. Where I disagree is that "Since 2003 its been getting harder to deliver as full range of solutions as before." Maybe its just the apps I develop, but I don't see much of a difference.

I also agree that "If you're not a C# .Net developer building SQL Server solutions  it seems you don't figure on Microsoft"s plans". I think that's a fair statement to make. However, I don't think that is a new thing. That's the point I've been trying to make.

Yes, MS removed Pivot Tables from Access 2013.

Yes, many other people have complained about it. (MS doesn't care ...)

However, as Scott pointed out, Pivot Tables are still available in SOME (NOT ALL) versions of 365 Excel 2013 (ie not in 365 Home Premium). So, in theory, what MS "expects" you to do is export / connect your data in Access to an Excel spreadsheet where you can still do the Pivot tables.

And, yes, this is not an optimal "solution", but it is what MS expects you to do ... <sigh>

Here are some links you can read about PT in Office 365/2013

“Hey, Who Moved My (PowerPivot 2013) Cheese?”

http://www.powerpivotpro.com/2013/02/hey-who-moved-my-powerpivot-2013-cheese

Analysis of “what happened to” PowerPivot in 2013 by a recently former Excel developer.

Excel 2013 PowerPivot wouldn’t come up -- here’s what solved it for me, maybe this will help solve the problem for others

http://answers.microsoft.com/en-us/office/forum/office_2013_release-customize/excel-2013-powerpivot-wouldnt-come-up-heres-what/4d5993d1-9ad9-452e-b1c6-4a6305d1e3bb

I installed Office 2013 (typical install - no customization and no errors during it) earlier this week and could not get PowerPivot to come up in Excel. After Office had been installed, I went to the Add-Ins under the Excel options on the start page and clicked on the PowerPivot COM add-in, then selected OK. As it said it would come up after I restarted Excel I did just that, only to see that the PowerPivot tab wasn’t theree. Next, I went to the Developer tab and selected it from the COM add-ins drop down. I saved the worksheet (even though I knew it wouldn’t make a difference but I just had to do it), exited Excel and restarted it. Again, no PowerPivot, and when I looked at the COM add-ins dropdown on the Developer tab, it was no longer selected.  I checked an Office 2013 Excel help article, which included the steps I had just tried and it also suggested that I make sure the add-in wasn’t disabled; it wasn’t. I then spent 2 days trying every suggestion that I could find on the internet, I repaired Office 2013, I uninstalled and reinstalled it, and I also messed with every Trust Center setting combination possible - all without success. So this morning I decided to go back to basics, which is when I finally came up with what needed to be done to get this to work - and it’s something that shouldn’t have even been necessary. You’ll understand why as you keep reading.   

I thought the typical, non customized install would take care of what it needed for the COM add-ins to work, but that was incorrect apparently. For some incomprehensible reason the path to the PowerPivot add-in wasn’t considered a “trusted location” and had to be manually added as one in Excel’s Trust Center. 

I added this path on that tab:   C:\Program Files\Microsoft Office\Officel5\ADDINS\PowerPivot Excel Add-in\PowerPivotExcelClientAddIn.dlI 

To do this, go to Excel Options -> Trust Center -> Trust Center Settings and select the Trusted Locations tab, which is where you add it. While on this tab you’ll probably notice a couple of things:

1. There are already several of the Excel locations for it’s extension in here as a default. (Not sure why this one wasn’t included as a default too.)
2. You may notice on the bottom left of this tab there is an option to select “Allow Trusted Locations on my Network” but it says “not recommended”. (So does this mean we have to manually add all of the paths to the Trusted Center that weren’t added during the install of the Microsoft product?)

After I added the path, I wanted to make sure all possible “trust issues” would be addressed, so I checked the Trusted Publishers tab to see if there was a row with the  Microsoft Corporation (found in the “Issued to” column) and Microsoft Code Signing PCA (found in the “Issued By” column). It was - luckily during my numerous attempts to make this work it got added when I had selected an option presented in the trust center to make the publisher of this add-in a trusted one.     

After I did these things, voila, I now have PowerPivot up and running. But man, this took far too much effort and was very frustrating, which is why I’ve added this post - hopefully this helps reduce the amount of frustration for someone else with this same issue.

Office 2013, Office 365 Editions and BI Features

http://cwebbbi.wordpress.com/2013/02/01/office-2013-office-365-editions-and-bi-features/

PowerPivot 2013 - not in all Versions of Excel!

Office 2013 Standalone Excel from amazon: http://www.amazon.com/gp/product/B00B1TFK4K/ref=as_li_ss_tl?ie=UTF8&camp=1789&creative=390957&creativeASIN=B00B1TFK4K&linkCode=as2&tag=powe022-20 

• US$100 vs US$109 from MS

http://www.powerpivotpro.com/2013/10/verified-yes-excel-2013-standalone-now-includes-power-pivot-for-real-like-normal-people-have-it/

http://office.microsoft.com/en-ca/excel-help/whats-new-in-powerpivot-in-excel-2013-HA102893837.aspx- Note: the punch line that it is not included in all versions of Office is buried in the middle of a large articlet

http://office.microsoft.com/en-ca/excel-help/start-powerpivot-in-excel-2013-add-in-HA102837097.aspx - How to enable PowerPivot addin in 2013

This article describes how to buy a Office using the Volume License terms and Prices: http://www.infoworld.com/t/office-software/how-buy-microsoft-volume-license-the-cheap-213214

<snip> Lemington Consulting, which works extensively with Access volume licenses, explains the details in its FAQ:

Is there a mininum number of licenses that must be purchased under a Volume License Agreement?

Yes. The minimum number of licenses required for a Volume License Agreement is five (5). However, the five licenses can be made up of any combination of products. Quantities of less than five can also be purchased economically by making up the additional license using an inexpensive “make up” product SKU.

Once the initial Volume License Agreement is in place, additional licenses can be added to the Agreement in quantities of less than five. For example, if a company purchases five Office 2010 Professional Plus licenses under a new agreement, three months later the same company can purchase one additional Office 2010 Professional Plus license under the same agreement. The minimum license number therefore only applies to the initial Volume License agreement.

One of the worst-kept secrets in VL cognoscenti circles: The Microsoft DVD Playback Pack for Windows Vista Business is (as you would imagine) quite cheap. I’ve seen it for as little as $7. (Then buy what ever Office product / bundle you want, ie genuine VL Excel 2013 ($173).)

</snip

What’s new in Power Pivot in Microsoft Excel 2013

Note   This feature isn’t available in Office on a Windows RT PC. Power View and Power Pivot are available in the Office Professional Plus and Office 365 Professional Plus editions, and in the standalone edition of Excel 2013. Read Excel 2010 workbooks with Power Pivot don’t work in some versions of Excel 2013. Want to see what version of Office you’re using?

Microsoft Excel 2013 features deeper integration of Power Pivot functionality. For example, you can now import and relate large amounts of data from multiple sources right in Excel by using a new built-in data model, without having to go to the Power Pivot window. Power Pivot provides an environment for more advanced data modeling and connects to the same data model. Read about everything else you can do in Power Pivot.

In Excel 2010, you installed the Power Pivot for Excel 2010 add-in so that you could import and relate large amounts of data from multiple sources.

In Excel 2013, much of that functionality, including the infrastructure that supports it, is built directly into the Data Model in Excel. Without installing a separate add-in, you can now:

• Import millions of rows from multiple data sources.
• Create relationships between data from different sources, and between multiple tables in a PivotTable.
• Create implicit calculated fields (previously called ‘measures’) – calculations created automatically when you add a numeric field to the Values drop zone of the Field List.
• Manage data connections.

Thus all Excel users can now build data models, which they can use as the basis for PivotTables, PivotCharts, and Power View reports. Excel automatically loads data into the in-memory analytics engine, which used to be available only with the Power Pivot add-in. The data model in Excel has other advantages:

• Processing data is fast.
• Data is highly compressed, creating a file of manageable size.
• Data is saved inside the Excel workbook, so it’s portable.

The Power Pivot add-in is available, too, and provides more advanced modeling capabilities for more experienced data modelers.

The Power Pivot in Microsoft Excel 2013 add-in, which connects to the same data model, provides a richer modeling environment that allows more experienced users to enhance their models.

For example, with the Power Pivot in Microsoft Excel 2013 add-in you can:

• Filter data when importing. You can import data in both Excel and Power Pivot, but when importing data in Power Pivot, you can filter out unnecessary data to import just a subset.
• Rename tables and columns as you import data in Power Pivot.
• Manage the model and create relationships using drag and drop in the Diagram View.
• Apply formatting (to be used in Power View and PivotTable reports).
• Define your own calculated fields to use throughout a workbook.
• Define key performance indicators (KPIs) to use in PivotTables.
• Create user-defined hierarchies to use throughout a workbook.
• Define perspectives.
• Author your own calculations by writing advanced formulas that use the Data Analysis Expressions (DAX) expression language.
• Use other more advanced data and modeling operations.

These features are not available in Excel. To use these features, you need to enable the Power Pivot in Microsoft Excel 2013 add-in. The add-in is included in the Office Professional Plus and Office 365 Professional Plus editions, and in the standalone edition of Excel 2013. See Start Power Pivot in Microsoft Excel 2013 for more information.

If you’ve used Power Pivot in the past, a few features available in earlier releases of Power Pivot aren’t available in Power Pivot in Microsoft Excel 2013. See the table in the “What’s new in the Field List” section below for details. See Upgrade Power Pivot Data Models to Excel 2013 if you have existing Power Pivot workbooks that you plan to edit and use in Excel 2013.