Add Power View filters to a report URL
Need to filter an existing Power View report by a user’s location or restrict new reports to the current month? Filters in Power View can now be pinned and applied to an entire report. Pinned filters can also be specified in the URL when opening or creating a report - to get started open an existing report and add a new filter condition to the URL using the format
&rf=[Table Name].[Field Name] eq 'Value'
Multiple filters can be created by chaining together filter conditions. A report on Box Office performance could be filtered to just Comedies released in 2004 with a URL like
&rf=[Media].[Genre] eq 'Comedy'&rf=[Media].[Release Year] eq 2004
Data types
Filters can be specified on string, numeric, date and Boolean fields. Currently only ‘equals’ filter conditions are supported.
String |
&rf=[City].[Name] eq 'Sydney' |
Numeric |
&rf=[Time].[Year] eq 2013 &rf=[Sale].[Price] eq 19.99 |
Date |
&rf=[Sale].[Date] eq datetime'2013-02-25' &rf=[Sale].[Date] eq datetime'2013-02-25T12:34' &rf=[Sale].[Date] eq datetime'2013-02-25T12:34:17.0001' |
Boolean |
&rf=[Resource].[Active] eq true |
Blank or null values |
&rf=[Something].[Else] eq null |
Escaping characters
Some web browsers handle URL parameters containing UTF-8 characters in funky ways. If passing non-ASCII values be safe and URL encode. Convert
&rf=[Region].[State Province] eq '河南'
Into
&rf=[Region].[State Province] eq '%E6%B2%B3%E5%8D%97'
Use double '' to escape quotes
&rf=[Region].[City Name] eq 'O''Fallon'
Existing filters
Filters added via the report URL will replace any existing pinned filters on the same field.
Comments
Anonymous
December 15, 2013
Could I add parameter with multiple values? For example, I'd like to see filter criteria with year 2012 or 2013?Anonymous
December 17, 2013
Unfortunately at the moment it's not possible to pass OR filter conditions via URL parameters. While you can pass multiple filter conditions via URL parameters they are currently AND'ed together.Anonymous
January 26, 2014
Can you provide full URL with filters? I tried adding?rf=.... but always getting error 'Unsupported file type http://servername/report.rdlx?&rf=[City].[Name] eq 'Sydney' I am also trying to put this url inside a Silver Light web part where it's failing with the similar messageAnonymous
February 03, 2014
Hi SGM - try using the URL in the format http://[Server]/_layouts/15/ReportServer/AdHocReportDesigner.aspx?RelativeReportUrl=[MyReportPath.rdlx]Anonymous
February 13, 2014
I tried with http://[Server]/_layouts/15/ReportServer/AdHocReportDesigner.aspx?RelativeReportUrl=[MyReportPath.rdlx] but no luck. Are you really sure about Power View supporting default filters with parameters? Can you provide any documentation from msdn which talks about this?Anonymous
February 16, 2014
msdn.microsoft.com/.../dn198310.aspx URL filters require that SP1 CU4 is applied.Anonymous
February 16, 2014
I installed SP1 CU4 using below link on a SQL Server Instance on Database server. support.microsoft.com/.../en-us. Do I need to install something on the SharePoint WFE Server too?Anonymous
February 16, 2014
Hey, I installed it on WFE. Basically where I have ReportingServices in SharePoint Integrated mode and it worked. Thanks a bunchAnonymous
February 20, 2014
Nice. Can you provide a syntax example when the PowerView report references a tabular model rather than MD?Anonymous
February 24, 2014
Not sure if it was the effect intended by the Power View team, but I realized that you can also unfilter a pinned filter by passing in (All). e.g. In the report I have Geography='Europe' but in some cases, I need to create a link to this report where I need this filter to be cleared. For this I realized I can pass &rf=[Geography].[Area] eq '(All') Not sure what would happen if your attribute actually had an element with the same value (All)?! This is a valid scenario which is of use to us. <b>Rhys</b>, would you know if this would continue to be supported (ability to unfilter/clear a pinned filter using a URL parameter)?Anonymous
February 24, 2014
Okay I take it back. The eq (All) does not work. So I back to square one. I do have a legitimate scenario where I need to clear the pinned filter based using URL Parameter. Rhys: Would you know if this is possible and how I can do this?Anonymous
March 06, 2014
Any way to hide the filter, so that the user of the report cannot change the value of the forced filter?Anonymous
March 07, 2014
It's not possible to hide a filter in a Power View report. I'm not sure on your exact scenario but URL filters are not designed to hide data, just to start a report in a particular state. If you need to hide data from particular users it's best to do so using permissions in the data model itself.Anonymous
March 26, 2014
Rhys: How I use the permissions in the data model itself to hide the filter (area) for users?Anonymous
April 06, 2014
Can we hide pinned filters to keep the value uneditableby users?Anonymous
April 07, 2014
No, filters cannot be hidden from users (users could just alter the URL to edit the filter values anyway).Anonymous
April 25, 2014
Is is possible to set dynamic filters or parameters within the Power View report on SharePoint that are based on the specific AD user? For example, the report would always show data specifically related to them, as opposed to having to filter the report manually after opening?Anonymous
May 04, 2014
Hi Rhys, very good reference! thanks Regarding Lucas scenario (roles/dax filters?), I wonder if you have additional info.... We have a similar scenario (tabular model roles with dax filters). What we are doing for the moment: -admins/power view authors (can see all data, no dax filters) author the reports, usually set the reports with a specific business unit (most data requires this level selected to be visible), this allows reports to have proper thumbnails (yes, some security questions regarding the thumbnails...) -regular users all belong to tabular roles with dax filter applied to the pinned filter attribute, but this doesn't change the default selection in the report... -we are ensuring (tricky...) that all reports pass trough a utility redirect page that appends the pv querystring filter derived from the user profile, overriding the default selection, and setting it to a value allowed by the user dax role Now, this works, but really seems to be a lot of work. Are we missing something? :) Thanks, RuiAnonymous
May 12, 2014
My URL with a filter is not working. The page comes up fine but the filter does not change. Do I have to be in edit mode somehow? confidential.net/.../AdHocReportDesigner.aspx[vwScoreCardValues].[Division]%20eq%20 ‘Fleet’Anonymous
May 12, 2014
Hey Fred - does your URL have "&rf=" between the last URL param and the filter condition? It's also worth checking what version SQL Server is patched tooAnonymous
May 16, 2014
Is there a similar thing that will set slicer values attached to powerpivot's? Thanks PaulAnonymous
June 01, 2014
@Rui, the utility redirect page Sounds like the solution I need at the moment. Could you share that to prevent me reinventing the wheel.Anonymous
August 11, 2015
please, what would be the filter format for Microsoft crm fieldsAnonymous
October 08, 2015
We need the ability to create Dynamic Drill Downs. We have used the Pinned feature and the URL command to navigate to an report page. However after a user selects a page filter and we navigate them to 2nd page of Power View it does not maintain Pinned Values. So my question is there a way to either get value that is pinned or keep values that are pinned in URL?