SharePoint Web Part to Excel Workbook problem

[Exhorter]

First off, thanks to anyone who has any thoughts on my issue. Here is what is going on;

I have a dashboard built in excel 2013 with multiple pivot tables. It connects to a data cube via a secure network. The dashboard is displayed in a SharePoint site using an Excel Web Access web part.

This dashboard needs to display an agents scorecard and filter the data based on who is logged in the SharePoint site. (example, agent X logs in and will only see agent X's scorecard)

I have added a Current user Filter and connected it to the Excel web part but that is not working for me. I get the following error.

Unable to Set One or More Parameters.



An error occurred while attempting to set one or more of the parameters in this workbook.

As a result, none of the parameters have been set.
Click OK to return to the workbook.

The parameters are set in excel, and I have tried everything I know to fix this. I do not have to use the current user filter if there is a better way of coding this. The only restriction I have is for some reason the network I am on will not allow SharePoint Designer to operate.

Any help out there?

 

[scottsen]

I'm trying to think if this is possible the way you are doing it. It might not be.

Let's start w/ the easy part

Back in power pivot land, do you have a table that lists all the possible usernames? And you put that into a slicer that you are trying to set via the web part connection magic?

"connects to a data cube" -- can you clarify... do you mean you have reports build against a tabular model? (or was this... some *other* cube data getting pulled into a power pivot model?)


So, the thing is... the required format for setting those filters are kinda freaky. I would start with a URL filter just to get a feel for it. It's like...

[TableName].[ColumnName].&[TheValue] eg [Users].[UserName].&[Jim].

So... I'm kinda skeptical the username web part can send THAT freaky format in. I vaguely recall writing a custom web part for that.

However...

Know that DAX does have a =USERNAME() function that might help you?

 

[Exhorter]

I do have a table with the info loaded from the cube. As far as what I am calling a cube is an external data source that is fed by 7 differnt sources(databases) its data.

I can tell you that the SharePoint filter is only sending the user name itself and nothing additional. It does have the option to link it to the slicer, but it still doesnt work. I am really unfamiliar with DAX, but looks like I may need to look into it.

 

[ImkeF]

Maybe defining the rules in your cube already is a reasonable alternative:

Analysis Services Dynamic Security

Imke

 

[scottsen]

You might take a look at this: “Drill Across” in PowerPivot – Live Demo « PowerPivotPro

it is not exactly related, but it does show hooking up connections to slicers... so you can at least "see" what I'm talking about

 

[Exhorter]

I was able to finally fix this issue. Sharepoint current user webpart was sending over just the user name and it had to be formatted with the MDX needed to fit the slicer.

Thanks for all the help

 

[scottsen]

Cool. Out of curiosity, how did you end up sending the correct format? (custom web part?)

 

[Exhorter]

Under the advanced filter options:

Text before value: [Agent].[SP Login Id].&[
Text after value: ]

This put the full id inside the MDX format that I needed for the slicer to filter properly. I didnt actually have to build a custom web part as I thought I would. Sharepoint alread had this available once editing the current user webpart.

 

[scottsen]

Awesome! Great tip.