Pivot tables in Excel 2007

[bergstrand]

Hi,

I have noticed that it is possible to connect to a TM1 cube from Excel 2003 (using the Applix TM1 OLE DB MD Provider) and thus use Excel's pivot tables with TM1 data. However, I cannot get this to work in Excel 2007. Does anybody know if it is possible somehow to use an Excel 2007 pivot table with TM1?

The following is stated in the software environment specification: "ODBO support via Excel 2007 Pivot tables is not supported". Is this referring to the OLE DB provider, or are they two separate things?

Best regards,
Bergstrand

[belair22]

I hate it when people ask this question but I just have to do it : "Why would ya want to do that?"

The TM1 GUI (Excel & Perspectives) is essentially a super-charged Pivot Table. Kinda like choosing to hand-wash a basket of clothes when you have a brand-new washing-machine right next to you.

[jim wood]

I hate it when people ask this question but I just have to do it : "Why would ya want to do that?"

The TM1 GUI (Excel & Perspectives) is essentially a super-charged Pivot Table. Kinda like choosing to hand-wash a basket of clothes when you have a brand-new washing-machine right next to you.

It's a bit harsh to question his motives without knowing the full facts I guess. Saying that, I too am struggling to see the need. May be Bergstrand can help us out there,

Jim.

[lotsaram]

I hate it when people ask this question but I just have to do it : "Why would ya want to do that?"

The TM1 GUI (Excel & Perspectives) is essentially a super-charged Pivot Table. Kinda like choosing to hand-wash a basket of clothes when you have a brand-new washing-machine right next to you.

I think there are potentially lots of (very good) reasons:
- Pivot tables in 2007 have lots of nice inbuilt formatting (and charting) functionality
- Pivot tables have inbuilt nested drilldown ability (rows and columns)
- no need for end users to need to know any non-standard Excel formulas
- drag and drop field pivot interface (that unlike ISB is not about to be discontinued)
- as middleware to 3rd party data visualisation and analysis plugins that have pivot table connectivity but not OLEDB conectivity

That said I can't see a need for it that often but I certainly wouldn't scoff at the need per se. If the ability to plug in a pivot table to TM1 could be made as easy as setting up an ISB view then it would be a great thing and a fantastic tool.

[Steve Rowe]

Yeah I'd say that pivot tables have a several pieces of functionality that are not available in Excel/Tm1 world.

I had some users that were slicing out most of the data from a cube then using a pivot table to produce their reports. This was partly a habit thing but it was quite hard to reproduce the flexability of the reporting they were getting from the pivot tables with a pure excel / TM1 approach.

I have to confess I'd not though of linking the pivot table directly to TM1, sounds like a powerful feature. WIll be a shame if it's no longer supported.

Not sure I can answer the question of the OP though.

Cheers

[belair22]

I hate it when people ask this question but I just have to do it : "Why would ya want to do that?"

The TM1 GUI (Excel & Perspectives) is essentially a super-charged Pivot Table. Kinda like choosing to hand-wash a basket of clothes when you have a brand-new washing-machine right next to you.

I think there are potentially lots of (very good) reasons:
- Pivot tables in 2007 have lots of nice inbuilt formatting (and charting) functionality
- Pivot tables have inbuilt nested drilldown ability (rows and columns)
- no need for end users to need to know any non-standard Excel formulas
- drag and drop field pivot interface (that unlike ISB is not about to be discontinued)
- as middleware to 3rd party data visualisation and analysis plugins that have pivot table connectivity but not OLEDB conectivity

That said I can't see a need for it that often but I certainly wouldn't scoff at the need per se. If the ability to plug in a pivot table to TM1 could be made as easy as setting up an ISB view then it would be a great thing and a fantastic tool.

Lostsaram - Active Forms in TM1 9.4 covers most if not all the reasons you outline.

[lotsaram]

Active Forms in TM1 9.4 covers most if not all the reasons you outline.

Inbuilt charting - no.
Drilldown on columns - no.
Drag and drop in Excel - no.
Connect to other plugins - no.

I'm not sure what exactly you were referring to.

[sarahmilius]

this may at least offer hope - in the documentation for TM1 9.4 (not sure about the original post version), the compatibility with Excel 2007 is discussed. There are a couple features that TM1 is not yet able to support (pivot tables and some type of filtering). There were another couple features that TM1 Web could not support. My interpretation is that full functionality will be achieved in a future 9.4.x release, but it may take longer.

[George Regateiro]

Full excel compatability would be great if they reach it. I have always thought there could be a lot more done in making TM1Web a lot more client like if they could leverage the XML base in the new format. Not holding holding out too much hope since they seem to focused in other directions, but since we are entirely web based I can always hope.

[belair22]

To cover the original posters query :

(Taken from TM1 9.4 MR1 FP1 release notes)

"Pivot tables are fully functional in Excel 2003. Due to a new data access TM1 pivot tables are currently not supported in the 9.4 release."

[bergstrand]

First of all, thanks for all the replies!

It would be great to be able to use Excel 2007 pivot tables with TM1 because of the functionality lotsaram lists. That functionality does not exist in active forms. For my part it is the drilling of Excel pivot tables that I would like to have the most.

In the organisation where we will be implementing this TM1-solution, the users are used to working with Excel 2007 pivot tables, and it would have been great to let them continue with that. Hopefully this functionality will be supported in coming releases of TM1.