Customizing Filter Persistence in PPS2007

A couple of days ago i had to get into the PPS 2007 backend stored procs in order to customize the filter behaviour. As we know, PPS remembers the value of the filter selection across user sessions. This can be a good thing or a bad thing depending on your setup and requirements.

In my case I had a number a number dashboards built with a filter called “Site”. This filter was populated from an SSAS 2005 cube and simply populated a single-select list with a list of facilities that my client had in their business. So a user would pick a facility from the list and the dashboard would then filter the information based on the selection.

We had leveraged security in SSAS to lock down access to facilities via roles (as is best practice.) This worked great, since users would only be able to see facilities in the dropdown if they had been granted access via an SSAS role.

Most users in the business had access to only one facility, so the filter list would contain just a single item. This way regardless of which dashboard they visited, the “Site” filter would have the same selection.

So far so good.. but here things start causing problems. Some users needed access to more than one facility, so again the roles in SSAS reflected this. The issue was that these select users were now getting “lost” because they would select “Facility A” on one dashboard, then drill down into a detailed dashboard expecting it to remember the selection from another dashboard. Some things to note here:

• The “drilldown” was a hack to get to a completely different dashboard. Sadly, PPS does not support URL links (while the older BSM did!). So I had used this commonly used trick originally highlighted by Nick Barclay
• PPS does not natively support passing ANY values between dashboard pages

So I started researching how to turn off the filter persistence. Annoyingly this is not a simple on/off setting somewhere.. not even in a text config file! What is required is to get into your PPSMonitoring database and comment out some code in the stored proc [dbo].[ParameterValuesCreate]. So this only gets me halfway there.. now any filter will always default to the first item in the list.

So i knew i needed to customise this stored proc and turned out i needed to learn some XPath in the process. My requirements summarised:

• PPS must remember filter values per user ONLY for filters called Site.
• The selection must persist across any dashboard. So a user looking at “Facility B” will always see this selected no matter which dashboard they change to.

The final solution was:

• Look at [dbo].[FCObjects], parse the XML column [SerializedXML] to extract the GUID for filters called “Site”. (This means the facility filters must be named “Site” all the time for this to work everyehere in the solution).
• Within [SerializedXML] i get a number of ParameterUIElement nodes for each dashboard. Each of these contains a single filter defitinion, as a descendant node of a dashboard element.
• I then had to loop through these to extract just a single node pertaining to the site filter. This is done for each dashboard, so effectively i end up with a list of all GUIDs for any filter called site across the whole solution.
• Finally i check if the filter being passed to the stored proc is actually a Site filter by seeing if it exists in the list created above. If it is, I update the rows in the table [dbo].[ParameterValues] which correspond to the GUIDs i got earlier (i.e. “Site” filter values)

So here is the code below.. noting that this was a first hack and there are improvements I can think of already such as getting rid the of cursor and possible replacing with a CTE.. also creating a table of the GUIDs found so that we dont have to parse [dbo].[FCObjects] every single time a dashboard is viewed or filter is changed. This version may get slow if there are many items in the PPSMonitoring DB.

USE [PPSMonitoring]
GO
/****** Object: StoredProcedure [dbo].[ParameterValuesCreate] Script Date: 11/12/2010 11:03:15 ******/

SET ansi_nulls ON
GO
SET quoted_identifier ON
GO

ALTER PROCEDURE [dbo].[Parametervaluescreate] @Login [NVARCHAR](1800), @ParameterUniqueName [NVARCHAR](100),@SerializedXml [XML]
AS
BEGIN
SET nocount ON
DECLARE @tErrMsg NVARCHAR(50)
SET @tErrMsg = ‘ParameterValuesCreate Failed For Object – ‘
DECLARE @TransactionIsOurs INT
DECLARE @ReturnCode INT

— init
SET @TransactionIsOurs = 0
IF @@TRANCOUNT = 0
BEGIN — only if @@TRANCOUNT is 0, we do BEGIN TRAN
BEGIN TRANSACTION
SET @TransactionIsOurs = 1
END

— BEGIN MOD BY BHAVIK – customise filter persistence behaviour
/* —————————— */
— check if this was a “Site” parameter, if so update all other site parameters for this user
— this way site will be consistent across any dashboard they visit
— ** Note – filter MUST be called Site in any dashboards for this code to work across all **

— vars
DECLARE @tempxml XML
DECLARE @NodeList XML
DECLARE @search VARCHAR(5)

SET @search = ‘Site’
DECLARE @FilterGUIDS TABLE (id UNIQUEIDENTIFIER)

— retrieve the guids for all filters in dashboard elements with name “Site”
— this brings back ALL ParameterUIElement items for the dashboard
DECLARE nodeloop CURSOR FAST_FORWARD FOR
SELECT serializedxml.QUERY(‘/Dashboard/ParameterElements/ParameterUIElement’)
FROM dbo.fcobjects
WHERE serializedxml.EXIST(‘/Dashboard/ParameterElements/ParameterUIElement/Properties/BpmProperty[@Text=sql:variable(“@search”)]’) = 1

— iterate over the list of ParameterUIElements and filter keep only the “Site” ones
OPEN nodeloop
FETCH NEXT FROM nodeloop INTO @NodeList
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @tempXML = c.QUERY(‘.’)
FROM @NodeList.NODES(‘/ParameterUIElement’) t(c)
WHERE CONVERT(VARCHAR(MAX), c.QUERY(‘.’)) LIKE ‘%Site%’

INSERT INTO @FilterGUIDS([id])
SELECT @tempXML.VALUE(‘(/ParameterUIElement/@Guid)[1]’, ‘uniqueidentifier’)

FETCH NEXT FROM nodeloop INTO @NodeList
END

CLOSE nodeloop
DEALLOCATE nodeloop

IF EXISTS (SELECT id FROM @FilterGUIDS WHERE id = @ParameterUniqueName)

BEGIN
/* —————————— */
— BEGIN ORIGINAL MICROSOFT CODE
–If this parameter value already exists (for this login), update. Otherwise, insert.
IF( EXISTS(SELECT *
FROM [ParameterValues]
WHERE LOGIN = @Login
AND [ParameterUniqueName] = @ParameterUniqueName) )
BEGIN
UPDATE [ParameterValues]
SET [LastUpdated] = Getdate(),
[SerializedXml] = @SerializedXml
WHERE [Login] = @Login
AND [ParameterUniqueName] = @ParameterUniqueName

IF @@ERROR <> 0
BEGIN
RAISERROR (5580001, 16, 1, @tErrMsg, 7, N’ParameterValues’) WITH LOG
SET @ReturnCode = –1
GOTO exit_label
END
END
ELSE
BEGIN
–Insert record
INSERT INTO [ParameterValues] ([Login],[ParameterUniqueName],[LastUpdated],[SerializedXml])

VALUES (@Login, @ParameterUniqueName, Getdate(), @SerializedXml)

IF @@ERROR <> 0
BEGIN
RAISERROR (5580002,16,1,@tErrMsg,8,N’ParameterValues’) WITH LOG
SET @ReturnCode = –1
GOTO exit_label
END
END

— END ORIGINAL MICROSOFT CODE
/* —————————— */
— update all Site params for this user
UPDATE [ParameterValues]
SET serializedxml = @SerializedXml,
lastupdated = Getdate()
WHERE parameteruniquename IN (SELECT id FROM @FilterGUIDS)
AND [Login] = @Login
END

— END MOD BY BHAVIK
/* —————————— */

IF @TransactionIsOurs = 1
BEGIN
COMMIT TRANSACTION
END
RETURN 0

EXIT_LABEL:
IF @TransactionIsOurs = 1
AND @@TRANCOUNT > 0
ROLLBACK TRANSACTION
RETURN @ReturnCode
END