SQL Server PowerPivot

09-Sep-1111 Leave a comment

Installation seemed easy enough – If Excel 2010 32-bit edition as you should, then need 32-bit x86 version of PowerPivot

Import

If a table has a timestamp (binary) data then exclude that otherwise you get an error

Group by date

So far this has been disabled for me

How do I … Import more tables using an Existing Connection

At first glance “Home” tab, section “Get External Data” is the only place to get new tables from. However it is not possible to use an existing connection from here, so I landed up with several connections 1,2,3 to the same SQL Server. To add a new import to an existing connection:

Design tab > Existing Connections > Open or Double click the connection you want to get a new import from

How do I … Refresh after a Schema Change

Design Tab > Table Properties
Use Switch to “Table Preview” or “Query Editor” to validate

Insert Pivot Chart

This puts the Data for Sheet 1 Chart 1 on a different tab.
To move it to the same tab as the chart use “PivotTable Tools” > Move PivotTable

Trouble Shoot every sum as same number – no apparent grouping

I had WO -> Reject Card on WO -> Reject Detail on WO
I found this created no proper grouping, so sum was same number for every group. Changed to:
WO – > Reject Card
and
WO -> Reject Detail
Problem solved

Sort by Date workaround

Sorting by date does not work, to workaround this create a Computed/Calculated column and use this:
= Year([WorkOrder!CompletionDate) * 100 + Month([WorkOrder]![CompletionDate]
YearMonth = Date(Year([WorkOrder!CompletionDate), Month([WorkOrder]![CompletionDate], 1)
YearMonthSort = Format(YearMonth, “yyyy-mm”)

 

Error 1

“Could not add the field “<field>” to the PivotTable.  Please ensure the field exists and is calculated, and try refreshing the PivotTable.”

Solution 1

Close the file and then open it again

 

Possible improvements for Microsoft

  1. If an import fails you should be able to go back to the previous step, rather than having to start again
  2. On Diagram View good to have zoom, but why is minimum 100 %
  3. The table import wizard should be resizable
  4. Make sort by date work

Filed under SQL PowerPivot