Implement From Date and To Date Range for PerformancePoint 2010 Scorecard

I’m always asking by customers how we can implement a date range with PerformancePoint scorecard.

In all other objects in PerformancePoint we can do it (Analytic Grid & Chart, Reporting services etc…).

So, I decide to implement a scorecard transformation to do it, most of times we implement a scorecard transformation to extend the scorecard behavior.

In this solution I had a basic assumption that there are 3 date filters in addition to other filters.

1. Final Date (will be the final proxy filter which will connect to the scorecard).
2. From Date Filter (will remove dynamically in late binding by the extension).
3. To Date filter (will remove dynamically in late binding by the extension).

All of them had to load the same hierarchy.

Order of filters:

The order of the filters in the page is not relevant, the order of the (Filter Links - web part links) is important.

For example if we have: Product & Promotion filters the order to link the scorecard is the following: Final Date, From Date, To Date, Product, Promotion.

All the filters can implement any UI objects (Tree, Multi Select Tree etc.)

The final date filter will be read only so we will implement it with List (Combo Box) with a single value (for example the hierarchy All Member, the extension will override the value of final date.

The following screenshots describes conceptual workflow:

 

The extension will collect from & to date unique name values and create the where clause and then remove both from & to date filters.

 

How the code works?

Scorecard transformation has 4 main override event properties:

1. Pre Query
2. Post Query
3. Pre Render
4. Post Render

I implemented the Pre Query method, I control the filter collection and then everything behaves the default.

public GridViewTransformType GetTransformType() { return GridViewTransformType.PreQuery; }

The code does other two steps:

1. Works on a specific Scorecard (this gives you a flexibility to work with a specific scorecard in specific page). You need to find the scorecard ID from the workspace or from the view source of the page (locate for "ScorecardId")...
2. If the end user selects an arbitrary shape (members from different levels) I create a set, other if the end user selects members from the same level I create a range set.

Be aware that multi select is not supported (data range have to be single value to single value).

Installation steps:

1. Open the source code project with Visual Studio 2010
2. Compile the code
3. Copy the “Microsoft.PerformancePoint.SDK.Samples.RangeCreatorTransform.dll” to GAC (Global assembly cache).
4. Locate the PerformancePoint we.config file (default at C:\Program Files\Microsoft Office Servers\14.0\WebServices\PpsMonitoringServer)
5. Backup the file (the panic step… J)
6. At the appsetting add the scorecard id , for example

<appSettings>

     <add key="ScorecardViewId" value="The GUID…" />

</appSettings>

1. Locate the <Bpm> <CustomViewTransforms> entry and add the following:

<add key="RangeCreator" value="Microsoft.PerformancePoint.SDK.Samples.ScorecardTransforms.PreQuery.RangeCreatorTransform, Microsoft.PerformancePoint.SDK.Samples.RangeCreatorTransform, Version=14.0.0.0, Culture=neutral, PublicKeyToken=fa525c8383a44a52"/>

Be awere that FilterEmptyRows extension will be the last one, PPS product team add the remark.

<!-- FilterEmptyRows should allways be the last transform in this list -->

1. Reset IIS.
2. Create a dashboard page, add 3 date filters call them Final,From,To date (final date will be a combo box)
3. Link the filters to your scorecard.
4. Enjoy J

Good luck