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

Part 1, Part 2, Part 3

In the two previous parts to this post, I provided a function to connect two PerformancePoint Services (PPS) web parts and a function to connect a SharePoint Filter to a PPS web part. These functions work great, but only if you know the arguments to pass. These arguments can be GUIDs, for example, in the case of connecting a PPS web part to a Scorecard's row or setting a PPS web part's Display Condition based on a PPS Filter.

In this post, I'll solve that problem for you by providing a function that can create all of the function calls for you for a web part page that has existing connections on it. The purpose of this function is to record the connections on the page so that when they are lost (e.g. by saving a web part page using SharePoint Designer), the script can recreate them in seconds.

The Create-Connection-Script function below loops through all of the connections on a web part page, identifying the type connections and creates a script full of function calls to the Create-BIDataProvider-To-TransformableBIDataProvider-Connection and Create-IFilterValues-To-ITransformableFilterValues-Connection functions. If you create the connection script prior to editing and saving a web part page in SharePoint Designer, you'll be able to run the newly created script afterwards and your PPS web parts will be reconnected.

An example of how to call the Create-Connection-Script function for a web part page with exisitng connections:

# Script that contains functions
. M:\ps1\connections\ConnectWebParts.ps1

clear-host

try
{
    $scriptFolder = "M:\ps1\connections"

    if (!(Test-Path -path $scriptFolder))
    {
        New-Item $scriptFolder -type directory
    }

    $date = Get-Date -format "yyMMddHHmmss"

    $web = Get-SPWeb "http://servername/sitecollectionname/webname"
    $wpm = $web.GetLimitedWebPartManager($web.Url + "DashboardPages/Demo.aspx", [System.Web.UI.WebControls.WebParts.PersonalizationScope]::Shared)
    $scriptPath = Join-Path $scriptFolder -childpath "$($date)DemoConnections.ps1"    
    Create-Connection-Script $wpm $scriptPath
}
catch [Exception]
{
    write-host $_.Exception.ToString() -ForegroundColor Red
}
finally
{
    $wpm.Dispose() 
    $web.Dispose()
}   

 

The function in it's entirety (place this in a .PS1 file along with the functions provided in Parts 1 and 2):

function Create-Connection-Script {
    param(
        [Parameter(mandatory=$true)] $wpm,
        [Parameter(mandatory=$true)] [string]$scriptPath
    )

    try
    {
        $m_scriptPath = $scriptPath
        clear-script
        
        $wpConnections = $wpm.SPWebPartConnections
        write-script "# This script was generated by $($MyInvocation.MyCommand)" -Color Green
        write-script "# Script that contains functions"
        write-script ". M:\ps1\connections\ConnectWebParts.ps1"
        write-script "# Script for $($wpConnections.Count) connection(s) on web part page ($($wpm.ServerRelativeUrl))." -Color Green
        write-script ""
        write-script "try"
        write-script "{"
        write-script "    `$web = `Get-SPWeb `"$($web.Url)`""
        write-script "    `$wpm = `$web.GetLimitedWebPartManager(`"$($web.Url.Replace($web.ServerRelativeUrl, $wpm.ServerRelativeUrl))`", [System.Web.UI.WebControls.WebParts.PersonalizationScope]::Shared)"
        write-script ""
        
        write-host $web.Url.Replace($web.ServerRelativeUrl, $wpm.ServerRelativeUrl)
        
        foreach ($wpc in $wpConnections)
        {
            write-script "    # $($wpc.Consumer.Title) To $($wpc.Provider.Title)" -Color Green

            if($wpc.ConsumerConnectionPointID -eq "BIDataProvider" -and $wpc.ProviderConnectionPointID -eq "TransformableBIDataProvider")
            {
                write-script "    `$params = @{"
                write-script "        wpm = `$wpm"
                write-script "        wpConsumerTitle =`"$($wpc.Consumer.Title)`""
                write-script "        wpProviderTitle =`"$($wpc.Provider.Title)`""
                
                foreach($record in $wpc.Transformer.ConfigurationState.ProviderConsumerTransformations.Records)
                {
                    write-script "        consumerParameterName = `"$($record.ConsumerParameterName)`""
                    write-script "        providerParameterDisplayName = `"$($record.ProviderParameterDisplayName)`""
                    write-script "        providerParameterName = `"$($record.ProviderParameterName)`""
                    write-script "        providerFormula = `"$($record.ProviderFormula)`""
                }

                $cvr = $wpc.Transformer.ConfigurationState.ConditionalVisibilityRecord         

                if($cvr.IsDefined) 
                {
                    write-script "        cvrIsDefined = `$True"
                    write-script "        cvrProviderParameterDisplayName = `"$($cvr.ProviderParameterDisplayName)`""
                    write-script "        cvrProviderParameterName = `"$($cvr.ProviderParameterName)`""

                    $cvrVisibilitySelections = ""
                    foreach($vs in $cvr.VisibilitySelections)
                    {
                        if($cvrVisibilitySelections.length -ne 0)
                        {
                            $cvrVisibilitySelections += ","
                        }
                        $cvrVisibilitySelections += "`"$vs`""
                    }
                    write-script "        cvrVisibilitySelections = @($($cvrVisibilitySelections))"
                    write-script "        cvrIsDefaultVisibility = $([System.Convert]::ToInt32($cvr.IsDefaultVisibility))"
                }                
                write-script "        reconnect = `$False"
                write-script "    }"
                write-script "    Create-BIDataProvider-To-TransformableBIDataProvider-Connection @params"
            }
            elseif($wpc.ConsumerConnectionPointID -eq "IFilterValues" -and $wpc.ProviderConnectionPointID -eq "ITransformableFilterValues")
            {
                write-script "    `$params = @{"
                write-script "        wpm = `$wpm"
                write-script "        wpConsumerTitle =`"$($wpc.Consumer.Title)`""
                write-script "        wpProviderTitle =`"$($wpc.Provider.Title)`""
                write-script "        mappedConsumerParameterName =`"$($wpc.Transformer.MappedConsumerParameterName)`""
                write-script "        reconnect = `$False"
                write-script "    }"
                write-script "    Create-IFilterValues-To-ITransformableFilterValues-Connection @params"
            }
            else
            {
                write-host "    # Web parts did not have a supported connection" -ForegroundColor Green
            }
            write-script ""
        }
        
        write-script "}"
        write-script "catch [Exception]"
        write-script "{"
        write-script "    `write-host `$_.Exception.ToString() -ForegroundColor Red"
        write-script "}"
        write-script "finally"
        write-script "{"
        write-script "    `$wpm.Dispose()"
        write-script "    `$web.Dispose()"
        write-script "}"
    }
    catch [Exception]
    {
        write-host $_.Exception.ToString() -ForegroundColor Red
    }    
}

function Write-Script {
    param(
        [Parameter(mandatory=$false)] [string]$text,
        [System.ConsoleColor] $color="Gray"
    )
    
    #$text = "$(Get-Date -format g): $text"
    
    write-host $text -ForegroundColor $color
    
    out-file $m_scriptPath -Append -InputObject $text
}

 

Additionally, you'll note the write-script function at the bottom, which I use to write to a file and to the screen.

Hopefully, you can use these functions the way that I have been using them for the past several months, or you can use them to create your own functions to make custom dashboard pages easier to maintain.

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

Part 1, Part 2, Part 3

In Part 1 of this post, I provided a function that can reconnect PerformancePoint Services (PPS) web parts as an alternative to having to reconnect the web parts via the browser. This can come in handy when you've lost all of your PPS connections after having saved your web part page in SharePoint Designer.

In this post, I'm providing a function, Create-IFilterValues-To-ITransformableFilterValues-Connection, that can connect a SharePoint Filter to PPS web parts. I had to create this function to connect Query String (URL) Filters (QSUF) to my PPS web parts. Now this wasn't totally necessary, as these connections are not destroyed by saving your page in SharePoint Designer, but I have used it in various scenarios (e.g. deployment, creating new pages from existing) and it is referenced in the function that I'll provide in Part 3.

An example of connecting a QSUF to a PPS SSRS report:

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)

    # Report: Share by Market Scatter Plot To Area Filter
    $params = @{
        wpm = $wpm
        wpConsumerTitle ="Report: Share by Market Scatter Plot"
        wpProviderTitle ="Area Filter"
        mappedConsumerParameterName ="SqlReportViewUniqueParameterIdSI4"
        reconnect = $False
    }
    Create-IFilterValues-To-ITransformableFilterValues-Connection @params
}
catch [Exception]
{
    write-host $_.Exception.ToString() -ForegroundColor Red
}
finally
{
    $wpm.Dispose()
    $web.Dispose()
}

Here is the function in its entirety:

function Create-IFilterValues-To-ITransformableFilterValues-Connection {
    param(
        [Parameter(mandatory=$true)] $wpm,
        [Parameter(mandatory=$true)] [string]$wpConsumerTitle,
        [Parameter(mandatory=$true)] [string]$wpProviderTitle,
        [Parameter(mandatory=$true)] [string]$mappedConsumerParameterName,
        [bool]$reconnect=$false
    )
    
     try
    {
        $foundConnection = $false
        
        $wpConnections = $wpm.SPWebPartConnections

        # find connection
        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($foundConnection -eq $false)
        {
            # get consumer and provider web parts
            $wpConsumer = $wpm.WebParts | Where {$_.Title -eq "$wpConsumerTitle"}
            $wpProvider = $wpm.WebParts | Where {$_.Title -eq "$wpProviderTitle"}
 
            # get consumer and provider connection points
             $consumerConnectionPoint = $wpm.GetConsumerConnectionPoints($wpConsumer)["IFilterValues"]
             $providerConnectionPoint = $wpm.GetProviderConnectionPoints($wpProvider)["ITransformableFilterValues"]

             if($consumerConnectionPoint -ne $null -and $providerConnectionPoint -ne $null)
             {
                 $transformer = New-Object Microsoft.SharePoint.WebPartPages.TransformableFilterValuesToFilterValuesTransformer
                 $transformer.MappedConsumerParameterName = $mappedConsumerParameterName

                 # connect the web parts
                 $wpConnection = $wpm.SPConnectWebParts($wpProvider, $providerConnectionPoint, $wpConsumer, $consumerConnectionPoint, $transformer) 

                 write-host "Successfully connected: $wpConsumerTitle, $wpProviderTitle" -ForegroundColor Green
             }
             else
             {
                 write-host "Web parts did not have supported ITransformableFilterValues-To-IFilterValues 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 Part 3 of this post, I'll provide a function that uses the functions from Part 1 and this part to create a connection script for you when you provide it a web part page full of connections.