Modifying PerformancePoint dashboard filters is a ‘sticky’ situation

In most cases, the sticky filters employed by PerformancePoint dashboards are quite useful. In the situations where you would like to not retain the last user selections, it can be a bit difficult. For example, I deployed a dashboard with filters that were passed to an SSRS report contained in the dashboard. I did not want the report to run automatically, which it would do if all of its filters were applied. By forcing one of the filters to be blank, and not remember its last selection, the report cannot run (and yes, fine, it does give a small error message but it was acceptable to me) and only runs when the filter or prompt is satisfied.

I would like to point out here that there is a pretty good Microsoft support document here that outlines the steps to take to disable ALL of the sticky filters. And of course, please please please make sure you backup your PPS Monitoring database before making any alterations.

First, I would like to review the properties of the filter, as these were a little confusing for me.

On the dashboard layout, you can Edit the filter. There is a setting for ‘Cache option’. I was hoping that by setting this to ‘Do not cache this item’ that PerformancePoint would, well, not cache the item. In actuality, this does not apply to the last item selected, but rather the filter as a whole, so the filter is re-run, or regenerated, when the dashboard is viewed.

Before going through this example, I would like to note that I am not necessarily suggesting that this is a recommended or optimal approach. We will be modifying a Stored Proc along the way so any upgrades/patches may wipe out the settings.

1. Retrieve the ParameterID.
   1. To retrieve the parameter ID – view source on the dashboard page (make sure you view the source of the Sharepoint page, not the actual dashboard itself).
   2. Search for the text identifying the parameter. In the below case, the filter was called "Measure". The ParamterID is the field I have highlighted in yellow. Below is just a small excerpt from the ‘view source’.

      var ctl00_m_WebPart_51f847c87e114fd48055c36b0d017a81 = new PPSMA.WebPartViewManager([DashboardItem_51f847c87e114fd48055c36b0d017a81], "ctl00_m_WebPart_51f847c87e114fd48055c36b0d017a81");

      var DashboardItem_0178c801d9d54d2591b00890eb7e6b28;

      DashboardItem_0178c801d9d54d2591b00890eb7e6b28=new PPSMA.ParameterizableControlProxy("http://intranetdev/_wpresources/Microsoft.PerformancePoint.Scorecards.WebParts/3.0.0.0__31bf3856ad364e35/RenderingService.asmx/CreateRenderingInstructions", "ctl00_m_WebPart_0178c801d9d54d2591b00890eb7e6b28", "SingleSelectList", "DashboardItem_0178c801d9d54d2591b00890eb7e6b28");

      DashboardItem_0178c801d9d54d2591b00890eb7e6b28.dashboardId = "5a68731e25134a7ea9e8d0a5b34bd946";

      DashboardItem_0178c801d9d54d2591b00890eb7e6b28.displayTitle = "Measure";

      DashboardItem_0178c801d9d54d2591b00890eb7e6b28.isTitleBarVisible = false;

      DashboardItem_0178c801d9d54d2591b00890eb7e6b28.resourcePath = "http://intranetdev/_wpresources/Microsoft.PerformancePoint.Scorecards.WebParts/3.0.0.0__31bf3856ad364e35/";

      DashboardItem_0178c801d9d54d2591b00890eb7e6b28.dashboardItemId = "0178c801-d9d5-4d25-91b0-0890eb7e6b28";

      DashboardItem_0178c801d9d54d2591b00890eb7e6b28.isParameterUIControl = true;

      PPSMA.DashboardController.get_instance().addNewParameter(‘0178c801-d9d5-4d25-91b0-0890eb7e6b28’,‘a148f272-d9bf-429e-93af-0dae84bcbf5f’,’Label’,’Label’);

1. In SQL Server Management Studio, connect to the Database that hosts the PPSMonitoring database.
   1. Navigate to PPSMonitoring  Programmability  Stored Procedures  dbo.ParameterValuesGet
   2. Right click the stored procedure and select Modify.
   3. Add the ID from the above step to the end of the where clause (telling the statement to retrieve the parameter values except where the ID equals the ID of the parameter we would like to modify). Execute to recompile the Stored Proc.

/****** Object: StoredProcedure [dbo].[ParameterValuesGet] Script Date: 10/13/2008 12:04:09 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

Revolutionize Your Business With Generative AI

From product design and software development to virtual agents, content creation, and reporting, GenAI is transforming business. Our AI experts help you unlock GenAI’s full potential and drive growth.

Let’s Get Started

GO

ALTER Procedure [dbo].[ParameterValuesGet]

@Login [nvarchar](1800),

@ParameterUniqueName [nvarchar](100)

AS

BEGIN

SET NOCOUNT ON

SELECT

[SerializedXml]

FROM

[ParameterValues] WITH (NOLOCK)

WHERE

([Login] = @Login AND

[ParameterUniqueName] = @ParameterUniqueName)

and ( @ParameterUniqueName <> ‘a148f272-d9bf-429e-93af-0dae84bcbf5f’)