Excel Online – "Unable to Set One or More Parameters" when connecting "Excel Web Access Web Part" and "Current User Filter"


I recently came across the error:

I was connecting a Current User Filter to an Excel Web Access Web Part I was trying to pass the User's Login Name via the Current User Filter to a Parameter in an Excel Web Access WebPart.

I first created a workbook that contained a Pivot Table and then gave one of the cells (which is a Filter) a Define Name (I called it "NameParameter").

You can see Cell B:1 (which contains the name "Tommy") is named (the cell's Defined Name) "NameParameter".  You can also see the cell's Defined Name by clicking on the Formula tab > Name Manager.

To help you fully understand what I am doing, here is the Pivot Table data which is located on another worksheet in the same workbook.

I now need to save this to a SharePoint Library.  While doing this, I need to make sure my Defined Name ("NameParameter") is published. 

To ensure this choose Save As > Other Web Locations (Browse to the SharePoint Library) > Browser View Options… > Parameters tab > Add… (add the Parameter) > OK


Next I created an Excel Web Access Web Part via Insert > Web Part > Business Data > Excel Web Access

Then point to the workbook and publish > OK

Next I add a Current User Filter via Insert > Web Part

 

Filters > Current User Filter > OK

I now need to connect the Current User Filter to the Excel Web Access Web Part.  To do this, click the dropdown arrow > Connections > Send Filter Values To > Excel Web Access



Choose Get Filter Values From



Choose NameParameter next to Filter Parameter > Finish

 

We know need to Select value to provide.  I chose Name as you can see, I am logged in as MOD Administrator, so if I log into SharePoint as this account, this Name should be passed to the NameParameter!

Everything works, yay!

HOWEVER

If the PivotTable's source data is a Data Model, this will not work.  I am currently investigating this with our Product Team.

Comments (9)

  1. Anonymous says:

    The Product Team is currently investigating this. I will post an update when I get one.

  2. Hi Tom, did you all find an answer to this particular issue yet? We are encountering this today and would love to have the Current User Filter as an option to filter an EWP.

  3. Ross says:

    I am also experiencing the same issue with SharePoint 2010. Using a Choice Filter and manually choosing a username has the same outcome. so maybe it isn’t related the actual filter being used? This works fine if the source of data is not a data model as
    mentioned.

  4. Chintan says:

    Hi Tom,
    I am also facing the same issue in SharePoint Server 2013.
    What I am looking, I want to customize that Error Message & Want to Hide OK Button.
    Is is possible?

  5. shaun says:

    Does the ‘Data Model’ issue apply to both PowerPivot and SSAS Tabular?

    I’m having problems using Text Filters. Is this a know issue for this webpart also?

    Thank you,

  6. Dave says:

    Hi Tom – Has there been a progress on this issue or has anyone found a work around?

  7. Dave says:

    I ended up figuring out a solution that I wanted to share for future visitors to this post…

    If a PivotTable’s source data is a PowerPivot Data Model, you need to pass in the table name & column name for the field that you’re trying to set. To do this, go to the Advanced Filter Options in the properties pane for the Content User Filter, then to the
    two fields labeled "Text to insert before values" and "Text to insert after values", and add the following:
    * TEXT BEFORE: [yourTableName].[yourColumnName].&[
    * TEXT AFTER: ]

    Doing this will, in essence, concatenate together the true address / path of the destination field.

    Hope this helps!

  8. Vicky says:

    Glad to have come upon this discussion as I’ve been working on just this problem for the past week. Dave, I’m not clear with your last solution

    By using this in the current user filter properties boxes: * TEXT BEFORE: [yourTableName].[yourColumnName].&[
    * TEXT AFTER: ]

    can you specify what exactly would be [yourTableName] and [yourColumnName] in Tom’s example above??

  9. Raj says:

    Thanks Dave & Vicky … it resolved my issue…

Skip to main content