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 message

• Anonymous
  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 bunch

• Anonymous
  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, Rui

• Anonymous
  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 too

• Anonymous
  May 16, 2014
  Is there a similar thing that will set slicer values attached to powerpivot's? Thanks Paul

• Anonymous
  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 fields

• Anonymous
  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?