Hiding Empty Rows in a Scorecard - Code Sample

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

 

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

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

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

Comments

• Anonymous
  April 15, 2008
  Where could I get information regarding when to use Microsoft Forecaster versus PerformancePoint Planning?

• Anonymous
  April 16, 2008
  The comment has been removed

• Anonymous
  April 21, 2008
  Alyson, The explanation of Forecaster vs. PPS above is a good start, but is there anything formal that compares the two in a more feature by feature format.   I have been getting requests like this from our prospects/clients. Alan

• Anonymous
  April 22, 2008
  Hi Alan - Unfortunately I don't have a feature by feature comparison between the products.  The above statement is the marketing statement that has been issued, that is all that I have seen. You might try your partner contact or account manager if you need more details.

• Alyson

• Anonymous
  May 09, 2008
  Where can I find the download for the PerformancePoint 2007 Monitoring SDK?

• Anonymous
  May 09, 2008
  Here is the location of the developer portal: http://msdn.microsoft.com/en-us/office/bb660518.aspx You can find everything here. Thanks, Alyson

• Anonymous
  May 09, 2008
  Thanks - I just copied .dll from server and created library.  When I view scorecard in designer the row headers that have empty values are indeed red.  However, when I preview my dashboard these same rows are not red and are visible - any ideas?

• Anonymous
  May 09, 2008
  ok - it works in preview site.  But am getting error when I try to deploy to sharepoint.  At first I was getting error due to control (script mgr) which was marked as unsafe.  I added entry to <SafeControl> of web.config but now I get an unexpected web part error.

• Anonymous
  July 23, 2008
  Great post. This helped me get started with the SDK. Have you had any luck editing the Values in a cell? I am trying to create a custom calculation to compute the score when the column name contains "Delta".

• Anonymous
  March 23, 2009
  I have managed to develop the DLL, get it into the GAC and when I look that the scorecard in Dashboard Designer it shows as red. However, when I try to look at the dashboard (containing just the scorecard) on the Preview site, the rows with no actuals are still being displayed and they are not red. I have changed the web.config for the preview site and restarted IIS but still no luck. Is there a "hidden" step that still needs to be done before this works. Please, please help!!

• Anonymous
  March 24, 2009
  Please try the following recommendation from Catalin. Maybe he could attach the debugger and make sure his code is hit and the expected rows are removed. Note that the sample is only about empty actual values.

• Anonymous
  July 08, 2009
  Hi. Great Job. I try it and it´s works, but I have seen any limitations for another uses (my case...). I have created any KPI´s and, foreach one, i have assigned to a different group (permmision given at this point). Data is comming from ODBC oracle datasource. A KPI that one user not have permision to edit is empty, only have the name of KPI when it´s loaded in "normal" mode (without used of this dll). At this point if I used this dll, and when i load the page that contains the dashboard it generates error, because (i think) it try to change one property and it haven´t permision to it (a user that no have permision to edit all kpi´s). If I used a user that can edit all kpi´s, and if any kpi is empty it hide them, and no error happens. Any suggestion, please?

• Anonymous
  January 18, 2010
  Hi there, you are a star. Everything worked out just great!