Excel OLAP Pivot Table Reporting

From DataSelf Knowledge Base
Jump to navigation Jump to search

Create connection to local cubes

1. Under the Data tab, click Connections, click Add, click “Browse for More…”, choose the local cube file and select the file type as “OLAP Queries/Cube Files”, and click Open.

2. Once you add the connection, click Existing Connections, choose the connection “localsales” and click Open.

Copy Paste Pivot Table to send to others, with Formatting intact

Problem: Copy > Paste Special doesn’t paste the formatting from the Pivot Table.

Solution: Paste from Clipboard PivotTablePaste.JPG

Video: http://blog.contextures.com/archives/2010/09/22/copy-pivot-table-format-and-values/

You can first make the pivot table prettier by not displaying the plus + sign:

Goto Pivot table. RightClick > Pivot Table Options.

Display tab - uncheck “Show expand/collapse buttons” and “Display field captions and filter drop downs”

PivotTable Named Sets in Excel 2010

http://blogs.office.com/b/microsoft-excel/archive/2009/10/05/pivottable-named-sets-in-excel-2010.aspx

OLAP PivotTable Extensions

Make calculations search cubes http://olappivottableextend.codeplex.com/
CodePlex is Microsoft’s open source project hosting web site.

Help with MDX: http://olappivottableextend.codeplex.com/wikipage?title=Calculations%20Help&referringTitle=Home&ProjectName=olappivottableextend

Excel 2010 with SQL 2005 cubes

“Cannot connect tot he server ‘ServerName’. The serer is either not started or too busy”

You may need to make the connection using the last connection option, MSQuery (the OLAP tab) and select OLE DB Provider for Analysis Services 9.0