Download the Code Sample GridViewTransformSample

Grid View Transforms for scorecards are one of the extensibility points of PerformancePoint Monitoring Server. In this post I will discuss how you can easily create your own Grid View Transform to automatically hide all the empty rows in scorecards.

Imagine you have a scorecard that is used by different organizations in your company. Depending on the level of access employees in different organizations have to the cube they may see no "Actual" value for some of the rows in the scorecard. If you have many rows in your scorecard employees may see a lot of these empty rows.  This makes it difficult for them to find the rows they need.

One way to solve this problem is to create different scorecards for different organization. This solution works but you have to create and maintain many scorecards. The other solution is to writing a Custom Grid View Transform that automatically hides all empty rows in a scorecard.

Here are the steps:

1. Create an empty windows class library project in Visual Studio and add the Microsoft.PerformancePoint.Scorecard.Client.dll to its references.

2. Create a class that implements the Microsoft.PerformancePoint.Scorecards.Extensions. IGridViewTransform.

3. Implement the GetID, TransformType and Execute method.

The GetId method simply returns a string ID for the Transform. This can be anything you want it to be and it is the key for your transform.

The GetTransformType method returns the type of transform. You have 3 major types of transforms. A PreQuery transform is executed before the cell data is populated with values. In contrast a PostQuery transform is executed after the cell data is populated and the PreRender transforms are executed after the PostQuery transforms and before the GenerateView method finishes its execution. Since we need to know the value of the cells to do the filtering, we will be using the GridViewTransformType.PreRender transform.

The execute method is where we put the logic of the transform. Before I can explain how it's implemented, we need to discuss the structure of the GridViewData.

In a scorecard GridViewData, columns and rows are represented with different trees. The type of the nodes of these trees are type GridHeaderItem.

GridViewData has a reference to the root of the Column headers tree called RootColumnHeader and a reference to the Row Headers tree called RootRowHeader. A cell in a scorecard is represented with the intersection of a leaf of the rows tree and a leaf of a column tree.

GridCell cell = viewData.Cells[header, col];

To implement the Execute method we need to find the Actual Column. Which is a leaf of the column tree with the HeaderType of ScorecardNodeTypes.KpiActual  and iterate through all the rows and check for empty display value of the intersecting cells(between the Actual column and the current row). Once we find a match we set the isHiddenPath property of the row to true.

Here is the code

 Code1

Code2

4. Compile your project. Make your generated assembly is strongly named as it need to be placed in the GAC.

5. Placed the compiled assembly in the GAC.

6. Add the line below to the <CustomViewTransform> section of the  %Program Files%\Microsoft Office PerformancePoint Server\3.0\Monitoring\PPSMonitoring_1\WebService\Web.config

<add key="HideEmptyRowsTransform" value=" MyCompany.GridViewTransforms. HideEmptyRowsTransform, [NameOfMyAssembly] , Version=1.0.0.0, Culture=neutral, PublicKeyToken=[ MyAssemblyPublicKeyToken]"/>

Replace  the [NameOfMyAssembly] to the name of your assembly

Replace the [MyAssemblyPublicKeyToken] with the public key token of your assembly.

7. Add the above line to your web.config file for the preview site. %Program Files%\Microsoft Office PerformancePoint Server\3.0\Monitoring\PPSMonitoring_1\Preview\Web.config

8. Add the above line to your web.config files for each MOSS/WSS box that has the PerformancePoint web part installed. \inetpub\wwwroot\wss\VirtualDirectories\80\web.config

9. Restart IIS on your servers

To test this transform, Open the designer and load a scorecard that has rows with empty actual. Click on the update button and notice the empty row is marked with red.

External links

http://msdn2.microsoft.com/en-us/library/microsoft.performancepoint.scorecards.extensions.igridviewtransform.aspx

By: Amin Pirzadeh, Catalin Sipos and Stephen Van de Walker Handy