none
PowerPivot PivotTable "Show Properties In Report" right-click menu option

    Question

  • Scenario: Using Excel 2013 and connecting using OLAP to an Excel 2013 PowerPivot data model hosted in SharePoint to generate a pivottable.

    When right clicking on a field and choosing "Show Properties in Report" there are no properties shown. What do I need to do to enable properties to appear?

    Please help!

    Show Properties in Report menu option


    Paul

    Thursday, May 01, 2014 4:52 PM

Answers

  • Hi Paul,

    Properties are originally coming from multidimensional models, to be precise they are based on the attribute relationships defined in the dimension. All attributes that are directly "above" (following the n:1 relationship chain) the selected attribute in the pivot table are displayed as properties.

    the concept of attribute relationships does not exist for tabular model (or Power Pivot) hence you also do not have properties. 

    If you are dealing with wide pivot tables on lowest granularity, the use of Linkback tables may be an option for your:
    http://www.sqlbi.com/articles/linkback-tables-in-powerpivot-for-excel-2013

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Monday, June 30, 2014 7:06 AM

All replies

  • Paul,

    Is this still an issue?

    Thanks!


    Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Saturday, June 28, 2014 12:20 AM
  • Yes, this is still an issue. I have read lots of information since and it appears this is not supported yet. The main issue is that being able to do this would solve a big performance problem with using powerpivot. For example, if you create a pivot table and add lots of text based data as columns then Excel brings back all the sub totals from powerpivot. The quantity of data soon gets large (due to all the sub totals) and so performance really suffers. Inspecting the MDX that Excel uses shows the use of hierarcherize which generates all the subtotals. Although the pivot table in Excel can be configured to not show these sub totals the damage is already done because it takes so long to read the data. In a conventional OLAP cube the method to improve this is to configure and use 'show properties in report'. As described above in my original post this does not seem to work in PowerPivot. It would be fantastic if this feature could be enabled for PowerPivot! I can provide a sample workbook showing the issue if required. Paul

    Paul

    Saturday, June 28, 2014 7:34 AM
  • Hi Paul,

    Properties are originally coming from multidimensional models, to be precise they are based on the attribute relationships defined in the dimension. All attributes that are directly "above" (following the n:1 relationship chain) the selected attribute in the pivot table are displayed as properties.

    the concept of attribute relationships does not exist for tabular model (or Power Pivot) hence you also do not have properties. 

    If you are dealing with wide pivot tables on lowest granularity, the use of Linkback tables may be an option for your:
    http://www.sqlbi.com/articles/linkback-tables-in-powerpivot-for-excel-2013

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Monday, June 30, 2014 7:06 AM