Share via

Pinning filters in Power View

  • Article

In addition to adding support for Power View with multidimensional BI Semantic Models (cubes), Cumulative update package 4 for SQL Server 2012 SP1 includes a bonus Power View feature – the ability to “pin” view filters. Let’s take a closer look at this feature.

In a Power View report, you can have filters for a specific table, chart, or other visualization on the canvas. The Filters Area automatically contains filters for the fields the visualization displays, plus you can add more filters by dragging fields from the Field List to the Filters Area:

image

You can also have view filters, which apply to an entire view and all of the visualizations it contains. For example, in the screenshot below, the filter on Fiscal Year applies to the entire view and both of the charts it contains:

image

A Power View report can contain multiple views, so what if you want to be able to “slice” all of them by a certain field? For example, what if you want to be able to slice any view by Fiscal Year? Well, you could create a view filter on Fiscal Year on each view, but you end up with separate, independent filters; if you slice by FY 2007 on one view, you find that selection doesn’t carry over when you navigate to other views. What if you want your selection to affect the entire report and “stick” as you navigate to other views?

Now you can “pin” filters to do exactly that! You’ll notice a new “pin” toggle button in view filter cards:

image

By default, a view filter is “unpinned” and applies only to the current view, not to other views in the report. Clicking the toggle button “pins” the filter so that it appears – and your selection applies – on all views in the report. Clicking it again “unpins” the filter, reverting it to the current view only and removing it from other views.

It’s pretty much that simple. Some things to note:

  1. Initially, we've added this feature for the “standalone” Power View web app in SharePoint, not for Power View in Excel.
  2. You can export reports that have pinned filters to PowerPoint, but you can’t pin or unpin filters in PowerPoint.

As an added bonus, we’ve also added the ability to add or change some basic pinned filters via URL parameters! More on that part in a separate blog post…

Comments

  • Anonymous
    January 05, 2014
    I'm guessing you mean "Power View in Excel" rather than "PowerPoint" at point 2. above...

  • Anonymous
    January 07, 2014
    Nope, I do mean PowerPoint there. To clarify, you can export a "standalone" Power View report to a PowerPoint presentation, and when you view those PowerPoint slides and their Power View content, you can interact with any pinned filters, but you can't pin or unpin them.

  • Anonymous
    January 07, 2014
    Do we have to install this CU on the backend SQL Server for the sharepoint site, if we are using PowerPivot report on sharepoint?

  • Anonymous
    January 08, 2014
    You need to install this CU on your SharePoint servers (which are running Reporting Services in SharePoint-integrated mode). (Note: To use Power View with Analysis Services multidimensional models, you also need to update your Analysis Services servers.) You don't necessarily need to install this CU on any SQL Server Database Engine instances, such as those hosting your SharePoint databases. Hope that helps.

  • Anonymous
    March 05, 2014
    What if we are using Powerview with Excel? We have to install CU also??? Regards

  • Anonymous
    March 05, 2014
    We haven't yet added this feature for Power View in Excel, but we aim to do so in a future update for Excel.

  • Anonymous
    April 06, 2014
    Can we hide chart filtersFilters in power views ?

  • Anonymous
    April 11, 2014
    You can choose to hide the entire Filters Area (although it's important to know that the user can easily show it again). You can't yet choose to hide individual filter cards. Could you describe the scenario you'd like to achieve?

  • Anonymous
    April 24, 2014
    Can we use any other operator just as 'like' instead of 'eq' in parameters?

  • Anonymous
    April 24, 2014
    Today, we support only the equality operator (eq) in URL parameters, but we've considered adding support for other operators (especially "in" for multiple values) in future.

  • Anonymous
    August 06, 2014
    If we install this CU to the SharePoint server, will it impact the other existing Analysis service reports? For example, we upgrade our SharePoint SQL analysis services version, but some Tabular cube is not upgrade.  Will it have compatibility to the previous version Analysis services?

  • Anonymous
    August 08, 2014
    I'm not sure what you mean by "will it impact the other existing Analysis service reports?" Are you asking whether installing the CU will break your existing Power View reports? If so, no, they'll continue to work just fine and return the same results as they did before. You'll just gain the option to pin filters if you wish.

  • Anonymous
    August 11, 2014
    Thanks Riccardo for the prompt response! Yes, I wanted to ask since the SQL version is not matching, will it impact the current PowerView and PowerPivot reports? Because I think I saw the msdn article, if I remember correctly, after install SQL 2014 version in the SharePoint, the previous PowerPivot file scheduled refresh will not work, the Admin has to re-enable the function for all the files.  Not sure if it will have the same impact if only install the CU or only SQL 2012 Serive Pack 2. Refrerence: msdn.microsoft.com/.../ee210646.aspx "Upgrade will impact data refresh operations. Scheduled data refresh on the server is available only for workbooks that match the server version. If you are hosting workbooks from the previous version, data refresh might no longer work for those workbooks. To re-enable data refresh, you must upgrade the workbooks. You can upgrade each workbook manually in PowerPivot for Excel, or enable the auto-upgrade for data refresh feature in SharePoint 2010. Auto-upgrade will upgrade a workbook to the current version prior to running data refresh, allowing data refresh operations to remain on schedule. For more information about this feature, see Upgrade PowerPivot for Excel."

  • Anonymous
    September 16, 2014
    Great update. I am trying it today. I have a report with 3 views. Each view has the same filter in each. When I pin the filter in one view it replicates the filter in the other views, but now I have a duplicate filter. Is there a way to link the filters so that when one filter is pinned, it updates the corresponding filter in the other views?

  • Anonymous
    October 29, 2014
    To clarify:  This is only for Power Views which are hosted on SharePoint On Premise?  This is not supported through O365 SharePoint?

  • Anonymous
    October 29, 2014
    Scott, re: "Is there a way to link the filters so that when one filter is pinned, it updates the corresponding filter in the other views?" Yup, that's pretty much the point of the pinning feature, but it sounds like your report has a bunch of existing filters you created the "old way" - by creating essentially the same filter on multiple views. You probably don't need those separate filters anymore; you could delete all but one of them and then pin it to apply it to all views, and it should stay in sync across views.

  • Anonymous
    October 29, 2014
    Peter, that's correct at the moment.

  • Anonymous
    March 07, 2015
    We've been using PowerView for a couple of years and I've had our IS guys take the updates you mention above and we still do not see the pinning feature. Is there a trick to getting this to show up?  This and the url parameters would be very useful to our organization where we use PowerView for team reports.

  • Anonymous
    March 09, 2015
    Curtis, are you using the standalone Power View web app in SharePoint Server on-premises (not Power View in Excel/Excel Online)?