SharePoint Designer 2010 and PerformancePoint Web Part Connections (Part 1)

Part 1, Part 2, Part 3

When using PerformancePoint Services (PPS) web parts in a web part page to create custom dashboards, you’ll typically set up connections between the (PPS, OOTB and custom) web parts using your web browser. Once your connections are established, you’ll realize that you don’t want to run through the process more than once, especially if you have tens of connections on the page. For a project that I am currently working on, I have several dashboards consisting of around 80 connections each (thankfully, I didn't have to create all of the connections at once).

What I quickly discovered (as many have) is that if you have to use SharePoint Designer to customize the web part page after the PPS connections have been established, you'll run into a very painful problem. It turns out that upon saving a web part page with PPS connections in SharePoint Designer, all of the PPS connections are lost.

My solution, after a lot of Googling and trial and error, has been to create PowerShell functions that connect PPS web parts (Part 1), connect PPS web parts to SharePoint filters (Part 2), and create connection scripts by having PowerShell examine the connections on a web part page (Part 3).

The Create-BIDataProvider-To-TransformableBIDataProvider-Connection function, provided below, will handle the following connections:

• Scorecard to PPS Filter
• Analytic Report (i.e. Chart or Grid) to PPS Filter
• Analytic Report to Scorecard
• PPS SSRS Report to PPS Filter
• PPS SSRS Report to Scorecard

It will also handle Connection Formulas and Display Conditions. There's certainly room for improvement, but it handles everything that I need it to do regarding PPS web part connections.

An example of calling the function to connect a Scorecard to a PPS Filter:

try
{   
    $web = Get-SPWeb "http://servername/sitecollectionname/webname"
    $wpm = $web.GetLimitedWebPartManager("http://servername/sitecollectionname/webname/DashboardPages/demo.aspx", [System.Web.UI.WebControls.WebParts.PersonalizationScope]::Shared)

    $params = @{
        wpm = $wpm
        wpConsumerTitle ="Scorecard"
        wpProviderTitle ="Area Filter"
        consumerParameterName = "EndPoint_Page"
        providerParameterDisplayName = "PerformancePoint Values"
        providerParameterName = "FilterValues"
        providerFormula = ""
        reconnect = $False
    }
    Create-BIDataProvider-To-TransformableBIDataProvider-Connection @params
}
catch [Exception]
{
    write-host $_.Exception.ToString() -ForegroundColor Red
}
finally
{
    $wpm.Dispose()
    $web.Dispose()
}

I won't bore you with a bunch of examples, because in Part 3, I'll provide a script that will take your web part page and create all of the function calls for you.

Below is the function is in entirety:

# cvr parameters: ConditionalVisibilityRecord Properties
function Create-BIDataProvider-To-TransformableBIDataProvider-Connection {
    param(
        [Parameter(mandatory=$true)] $wpm,
        [Parameter(mandatory=$true)] [string]$wpConsumerTitle,
        [Parameter(mandatory=$true)] [string]$wpProviderTitle,
        [string]$consumerParameterName="",
        [string]$providerParameterDisplayName="",
        [string]$providerParameterName="",
        [string]$providerFormula="",
        [bool]$cvrIsDefined=$false,
        [string]$cvrProviderParameterDisplayName="",
        [string]$cvrProviderParameterName="",
        [string[]]$cvrVisibilitySelections = @(),
        [bool]$cvrIsDefaultVisibility=$false,
        [bool]$reconnect=$false
    )
    
     try
    {
        $foundConnection = $false
        
        $wpConnections = $wpm.SPWebPartConnections

        # find connection based on consumer and provider web part titles 
        foreach ($wpc in $wpConnections)
        {
            if ($wpc.Consumer.Title -eq $wpConsumerTitle -and $wpc.Provider.Title -eq $wpProviderTitle)
            {
                if ($reconnect -ne $true) {
                    $foundConnection = $true
                }
                else {
                    $wpm.SPDisconnectWebParts($wpc)
                }
                break
            }
        }

        # if found, attempt the connection process
        if($foundConnection -eq $false)
        {
            # get the consumer and provider web parts
            $wpConsumer = $wpm.WebParts | Where {$_.Title -eq "$wpConsumerTitle"}
            $wpProvider = $wpm.WebParts | Where {$_.Title -eq "$wpProviderTitle"}
           
            # get the consumer and provider connection points
            $consumerConnectionPoint = $wpm.GetConsumerConnectionPoints($wpConsumer)["BIDataProvider"] 
            $providerConnectionPoint = $wpm.GetProviderConnectionPoints($wpProvider)["TransformableBIDataProvider"]
            
            [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.PerformancePoint") 

            if($consumerConnectionPoint -ne $null -and $providerConnectionPoint -ne $null)
            {
                $pcTransformations = New-Object Microsoft.PerformancePoint.Scorecards.ProviderConsumerTransformations

                # note: the consumerParameterName may be empty if a Display Condition only
                if($consumerParameterName.length -ne 0)
                {
                    # establish the connection settings
                    $tpcRecord = New-Object Microsoft.PerformancePoint.Scorecards.TransformProviderConsumerRecord
                    $guid = [guid]::NewGuid()
                    $tpcRecord.MappingId = $guid.ToString()
                    $tpcRecord.ConsumerParameterName = $consumerParameterName
                    $tpcRecord.ProviderParameterDisplayName = $providerParameterDisplayName
                    $tpcRecord.ProviderParameterName = $providerParameterName
                    $tpcRecord.TypeFullName = "System.String"
                    $tpcRecord.ProviderFormula = $providerFormula
                    $tpcRecord.ValuesColumnName = "MemberUniqueName"
                    $tpcRecord.DisplayColumnName = "DisplayValue"
                    $tpcRecord.EncodeAsSet = $false
                    $pcTransformations.Records.Add($tpcRecord)
                }
                
                # conditional visibility
                $tcvRecord = New-Object Microsoft.PerformancePoint.Scorecards.TransformConditionalVisibilityRecord
                $tcvRecord.IsDefined = $cvrIsDefined
                $tcvRecord.ProviderParameterDisplayName = $cvrProviderParameterDisplayName
                $tcvRecord.ProviderParameterName = $cvrProviderParameterName
                foreach($vs in $cvrVisibilitySelections)
                {
                    $tcvRecord.VisibilitySelections.Add($vs)
                }
                $tcvRecord.IsDefaultVisibility = $cvrIsDefaultVisibility

                $tcRecord = New-Object Microsoft.PerformancePoint.Scorecards.TransformerConfigurationRecord($pcTransformations, $tcvRecord)
                
                $transformer = New-Object Microsoft.PerformancePoint.Scorecards.WebControls.TransformableBIDataProviderTransformer
                $guid = [guid]::NewGuid()
                $transformer.ID = $guid.ToString()
                $transformer.ConfigurationState = $tcRecord

                # finally create the connection
                $wpConnection = $wpm.SPConnectWebParts($wpProvider, $providerConnectionPoint, $wpConsumer, $consumerConnectionPoint, $transformer) 
                #$wpConnections.Add($wpConnection)

                 write-host "Successfully connected: $wpConsumerTitle, $wpProviderTitle" -ForegroundColor Green
             }
             else
             {
                 write-host "Web parts did not have supported BIDataProvider-To-TransformableBIDataProvider connection types: $wpConsumerTitle, $wpProviderTitle" -ForegroundColor Green
             }
        }
        else
        {
             write-host "Connection already exists: $wpConsumerTitle, $wpProviderTitle" -ForegroundColor DarkCyan
        }
    }
    catch [Exception]
    {
        write-host $_.Exception.ToString() -ForegroundColor Green
    }
    finally
    {
    }    
}

 

In the part of this post, I'll provide a function that connects SharePoint Filters to PPS web parts.