none
Current User Filter with Pivot Table Report RRS feed

  • Question

  • Hi,

    Will the SharePoint 'current user filter' work with a pivot table report in Excel Web Access ? It works well when I have data as a table in my excel report but nothing is displayed when I insert a pivot table and connect it to the filter.


    Bala Bhavana

    Monday, February 15, 2016 2:38 PM

Answers

  • The steps in the link you mentioned are accurate, and apply well for a table. When you use a pivot table, you need to set the filter for the field you want, IN the pivot table and then use that as a parameter.

    While not using the current user filter, this article I published a while ago, will give you an idea about how to use the pivot table filter as a parameter. Hope it helps.

    https://gallery.technet.microsoft.com/Step-By-Step-to-create-a-3f0f5f38#content


    Cheers,

    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    • Marked as answer by Bala Bhavana Tuesday, February 16, 2016 8:40 AM
    Monday, February 15, 2016 3:42 PM
    Moderator
  • It works now. The problem was in using the data model to create the pivot table as mentioned below:

    http://blogs.technet.com/b/office_online_support_blog/archive/2015/03/11/excel-online-unable-to-set-one-or-more-parameters.aspx


    Bala Bhavana

    • Marked as answer by Bala Bhavana Tuesday, February 16, 2016 8:40 AM
    Tuesday, February 16, 2016 8:40 AM

All replies

  • Bala,

    How are you defining the filter paramater for the Pivot Table? You need to set the filter in the pivot table as users, name the cell, and then map the cell to the Current User Filter.


    Cheers,

    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    Monday, February 15, 2016 3:03 PM
    Moderator
  • Hi Prasanna,

    I followed the steps in the below article:

    http://consulting.risualblogs.com/blog/2014/11/11/filter-excel-web-access-web-parts-in-sharepoint-with-the-current-user-filter/

    I followed the same steps for a pivot table.


    Bala Bhavana


    Monday, February 15, 2016 3:35 PM
  • The steps in the link you mentioned are accurate, and apply well for a table. When you use a pivot table, you need to set the filter for the field you want, IN the pivot table and then use that as a parameter.

    While not using the current user filter, this article I published a while ago, will give you an idea about how to use the pivot table filter as a parameter. Hope it helps.

    https://gallery.technet.microsoft.com/Step-By-Step-to-create-a-3f0f5f38#content


    Cheers,

    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    • Marked as answer by Bala Bhavana Tuesday, February 16, 2016 8:40 AM
    Monday, February 15, 2016 3:42 PM
    Moderator
  • Thank you Prasanna. I followed the steps mentioned on the link and connected it with the current user filter. I get the following error:

    Unable to set one or more parameters. An error occurred while attempting to set one or more parameters in this workbook. As a result, none of the parameters has been set. Click OK to return to the workbook.


    Bala Bhavana

    Tuesday, February 16, 2016 8:05 AM
  • It works now. The problem was in using the data model to create the pivot table as mentioned below:

    http://blogs.technet.com/b/office_online_support_blog/archive/2015/03/11/excel-online-unable-to-set-one-or-more-parameters.aspx


    Bala Bhavana

    • Marked as answer by Bala Bhavana Tuesday, February 16, 2016 8:40 AM
    Tuesday, February 16, 2016 8:40 AM