locked
Pivot Table using External Connection shows incorrect Datatypes RRS feed

  • Question

  • Hi,

    I have a Pivot Table in excel that is pulling data from another excel file with an embedded PowerPivot Datamart hosted in SharePoint 2012; but the datatypes defined at the PowerPivot Datamart are not reflected on the Pivot Table in excel, instead it identifies all columns as text. I only encounter this issue when I build a Pivot Table using an external connection, if I build a Pivot Table on the same file as the embedded PowerPivot Datamart it can reflect the datatype defined on the datamart.

    Is there a way to enforce the datatype already defined on the embedded Datamart to the Pivot Table? Is there a way to change the datatype of the column on the Pivot Table?

    Thanks and Regards,

    Justin

    Thursday, December 11, 2014 1:39 AM

Answers

  • No. To access an external file you're using the SSAS connector and this (based on its history with Multidimensional cubes) will only return string data. You will see the same behavior if connecting to an external SSAS Tabular model in Excel. The most surprising thing is that it maintains types when utilizing an in-workbook Power Pivot model.
    • Proposed as answer by Michael Amadi Friday, December 12, 2014 8:47 AM
    • Marked as answer by Charlie Liao Sunday, December 21, 2014 1:48 PM
    Thursday, December 11, 2014 3:22 PM