Hi All,

I have deployed one PowerPivot Worksheet containing only data model on SharePoint 2013.

Then I have generated some reports out of it. I mean using that deployed worksheet(as a Data Model) I created some reports (Power-Pivot Link Table as a Data Source) in other Excel files. and then I uploaded these newly created reports on sharepoint.

Now suppose when I open these newly created reports, I want to send parameters with that. so based on these parameters my newly created report should vary.

So how can I send parameters in URL so that my excel will get filtered?

This blog post should help get you started:

http://www.powerpivotpro.com/2012/05/drill-across-in-powerpivot-live-demo/

Hi. The link is fine.They are saying that you have to open that specific file & then click on hyperlink etc.

But instead of that what I want is when I click on the file itself then only I should be capable of sending the

parameters & I will get filtered report based on parameters sent.

Can I conclude that - PowerPivot (on SharePoint) will NOT have the ability to pass a web based parameter that could be used as either a Filter (Slicer) before loading/executing the worksheet. I want to be able to load the worksheet dynamically based on the user's credentials when they click on it from the browser based sharepoint user interface.

The blog post was simply meant to be a guide to point you towards a possible solution and provide ideas.

It specifically talks about Query String Filter Web Parts and how to connect them to an Excel Services workbook.

There are other Filter Web Parts in SharePoint that may be more appropriate for your needs such as Current User Filter which pulls the name of the current SharePoint user to pass as a parameter.

Additionally, upgrading your PowerPivot workbooks to a SSAS Tabular model will give you additional Row Filtering/Security options that could be used to filter your reports based on the current user.

• Proposed as answer by Ed Price - MSFTMicrosoft employee, Owner Friday, January 17, 2014 7:07 PM
• Marked as answer by Ed Price - MSFTMicrosoft employee, Owner Wednesday, July 02, 2014 11:24 PM

Yes I get it.

Please tell me one thing that,  Can we use these filters for filtering Power-Pivot Model Data or not? Or we can only filter normal Excel workbook Data?

In the blog post, an Excel Pivot Table using PowerPivot as it's data source is being filtered by the Query String.

Hi Mike,

Everything is set for sending parameters to Excel Web Access through Excel Web parts.

as it is receiving the parameters but not filtering excel worksheet n giving above error.

Please guide me if you have any solution on this.

Thanks in advance!

Hi Mike,

Everything is set for sending parameters to Excel Web Access through Excel Web parts.

as it is receiving the parameters but not filtering excel worksheet n giving above error.

Please guide me if you have any solution on this.

Thanks in advance!

You can also ask Rob on his site, if his instructions aren't working for you.

Thanks!

Hi Ed Price, Can you provide me the link to get Rob's blog?

Rob's blog is the one linked to in my initial reply.

This blog post should help get you started:

http://www.powerpivotpro.com/2012/05/drill-across-in-powerpivot-live-demo/

Hi Rameshwar,

Did the blog post help you to solve your problem?

I think it amazing that noone can give a streight answer to this pretty simple question. I had no problem sending parameters to excel from an url using normal pivot. Using power pivot though gave me hell. Why reference to a useless article?

Searching for the solution to this problem ( sending parameters to poverpivot from an url) takes me to this page time after time. And I have to see the same riduculous copy and paste answers.. DAMN! As Rameshwar Pawale correctly staes you will get an erorr in powerpivot.

If anybody has an answer I will be very gratefull :)

Hi jsa061, I've the same problem/error that you had while trying to pass parameters to PowerPivot...

No one could give me the answer as well. Did you find a solution?

Thanks!

Daniel

 

I would like to do the same.  How come no one from Microsoft Power BI team answer this simple question and if the answer is yes, a simple example would be greatly appreciated.  If not, it should be added to the backlog. 

Looks like they don't have answer to this question. It would be great if they add this functionality in their upcoming  release.

• Proposed as answer by SVN Mobile Solutions 10 hours 6 minutes ago
• Unproposed as answer by SVN Mobile Solutions 10 hours 6 minutes ago

Looks like they don't have answer to this question. It would be great if they add this functionality in their upcoming  release.

• Proposed as answer by SVN Mobile Solutions Friday, April 10, 2015 8:58 PM
• Unproposed as answer by SVN Mobile Solutions Friday, April 10, 2015 8:58 PM

The article at the powerpivotpro.com link is somewhat unclear. However, I was able to make this work with some experimentation.

Here is what I found:

1. If your PowerPivot is connected to a Data Model (rather than a simple excel) then your Query String parameters need to be formatted based on the MDX format rather than a simple string value. This is mentioned in the article midway through when he talks about building the hyperlinks.
2. You do NOT need to duplicate each slicer parameter as a page filter on the target worksheet contrary to what the article says.
3. Once you select your slicers in the Publishing Options and publish the file to your site you should be all set to connect them directly to Query String Filter in SharePoint.

Here is a quick illustration:

I have a "Dates" dimension with "Year" hierarchy in my data model, so note how it is passed in the query string to the Year slicer in the Excel Web Part. If you simply pass "...?Year=2013" you'd get the same "Unable to set one or more parameters" error mentioned earlier in this post.

• Edited by SVN Mobile Solutions 9 hours 31 minutes ago

The article at the powerpivotpro.com link is somewhat unclear. However, I was able to make this work with some experimentation.

Here is what I found:

1. If your PowerPivot is connected to a Data Model (rather than a simple excel) then your Query String parameters need to be formatted based on the MDX format rather than a simple string value. This is mentioned in the article midway through when he talks about building the hyperlinks.
2. You do NOT need to duplicate each slicer parameter as a page filter on the target worksheet contrary to what the article says.
3. Once you select your slicers in the Publishing Options and publish the file to your site you should be all set to connect them directly to Query String Filter in SharePoint.

Here is a quick illustration:

I have a "Dates" dimension with "Year" hierarchy in my data model, so note how it is passed in the query string to the Year slicer in the Excel Web Part. If you simply pass "...?Year=2013" you'd get the same "Unable to set one or more parameters" error mentioned earlier in this post.

• Edited by SVN Mobile Solutions Friday, April 10, 2015 9:33 PM