locked
Adding or removing columns in a pivot table without using VBA RRS feed

  • Question

  • Is there any way to add new columns (ie. powerpivot row labels) or remove existing ones without giving the user access to the field list and without using VBA? We want to give user some flexibility in defining which columns should be displayed in a grid without giving them full access to the field list. This would enable us to scale the Powerpivot file to Sharepoint browser mode without having to design the model from scratch again.

    The ideal solution (if possible) would be to present the user with a slicer enabling them to select fields that they wish to view. I have managed to create a data model that enables this but the results are displayed in two already defined columns in the Powerpivot table. One column displays the 'name' of the selected field and the other column the relevant value as held in the data model. The content of several selected slicer fields would be displayed in the same column beneath each other. It would be useful to be able to display the field contents side by side whereby the selected slicer values appears as column titles and the velues of the selected fields appear as the actual values in the pivot table. (eg. If the grid displayed employee details and 'Date of Birth' was selected from the slicer, then a column title with 'Date of Birth' would appear with the relevant employee DOB values being displayed in the table.


    Graham Goodwin Email: g.goodwin@inova.ch

    Tuesday, August 28, 2012 2:24 PM

Answers

  • Look at using CUBE function for this. They respond to slicers as well.
    • Proposed as answer by Elvis Long Tuesday, September 4, 2012 8:41 AM
    • Marked as answer by grahamgoodwin Tuesday, September 4, 2012 11:56 AM
    Tuesday, August 28, 2012 3:30 PM

All replies

  • Look at using CUBE function for this. They respond to slicers as well.
    • Proposed as answer by Elvis Long Tuesday, September 4, 2012 8:41 AM
    • Marked as answer by grahamgoodwin Tuesday, September 4, 2012 11:56 AM
    Tuesday, August 28, 2012 3:30 PM
  • I agree with David your best bet is to use the cube functions.  You may also get some mileage by creating sets in MDX (Options - Fields/Items/Sets) although last time I did this I used MDX to create row sets, I'm not too sure if you can create column sets.

    Also, you can hide columns from the Pivot table by going to the Power Pivot window and hiding columns.   Thereby leaving just the fields the user needs. 

    • Proposed as answer by Elvis Long Tuesday, September 4, 2012 8:41 AM
    Wednesday, August 29, 2012 12:49 PM
  • Hi Graham, David and Lee

    Another possibility (given you mentioned SharePoint) is to leverage the PowerPivot model but consume it through another client (not Excel or Excel Services).  In other words, if you have the PowerPivot for SharePoint add-in installed, every time you publish a PowerPivot to the Gallery it quite literally becomes an Analysis Services-cube equivalent (a tabular model), and as so you can then query this model/cube through tools like SQL Server Reporting Services, Report Builder, even non-Microsoft reporting clients (as long as they are OLAP/MDX compatible).  

    What this gives you is the ability to design much more flexible reporting interfaces off the PowerPivot model, and create things like parameter-based reports that can then dynamically select what columns to display/hide based on user selection, just as you need.




    Javier Guillen
    http://javierguillen.wordpress.com/

    • Proposed as answer by Elvis Long Tuesday, September 4, 2012 8:41 AM
    Wednesday, August 29, 2012 1:07 PM
    Answerer
  • Thank you for your replies.

    I have come to the conclusion that it is almost impossible to design a solution to this problem using a pivot table without presenting the user with the field list. Only via the field list can the user select additional columns to display in the table. The problem is that this will not scale up to Sharepoint browser mode as the field list isn't available (and not all users will the rights to open in Excel mode). When scaling up to Sharepoint we could of course use PowerView tables and matrixes but these are nowhere near as flexible as the pivot tables in Excel.

    I will spend more time looking at CUBE functions and see how far I get.


    Graham Goodwin Email: g.goodwin@inova.ch

    Wednesday, August 29, 2012 1:22 PM