Create filter data providers for PerformancePoint Services in SharePoint
Learn how to create the data provider component in a custom filter extension for PerformancePoint Services.
What are custom data providers for PerformancePoint Services?
In PerformancePoint Services, custom data providers retrieve data from a filter's underlying data source and define how to use the data. Most importantly, data providers specify the data values to expose in the filter control and the data that can be used as the filter's begin point. A data provider also stores the value that a user selects from the filter control, which is then sent to filter consumers. Data providers use two DataTable objects to organize and store data. For more information, see Filters Overview.
The following procedures and examples that show you how to create, configure, and define a filter data provider are based on the SampleFilterDataProvider class from the custom objects sample. The editor is a thin web application that enables users to modify the report's name and description. For the complete code for the class, see Code example: Create a data provider for custom PerformancePoint Services filters in SharePoint.
We recommend that you use the sample data provider as a template. The sample shows how to call objects in the PerformancePoint Services API and demonstrates best practices for PerformancePoint Services development.
Create data providers for custom PerformancePoint Services filters
Install PerformancePoint Services, or copy the DLLs that your extension uses (listed in step 3) to your computer. For more information, see DLLs with Class Libraries.
In Visual Studio, create a C# class library. If you have already created a class library for your extension, add a new C# class.
You must sign your DLL with a strong name. In addition, ensure that all assemblies referenced by your DLL have strong names. For information about how to sign an assembly with a strong name and how to create a public/private key pair, see How to: Create a Public/Private Key Pair.
Add the following PerformancePoint Services DLLs as assembly references to the project:
- Microsoft.PerformancePoint.Scorecards.Client.dll
- Microsoft.PerformancePoint.Scorecards.Server.dll
Depending on your extension's functionality, other project references may be required.
In your provider class, add using directives for the following PerformancePoint Services namespaces:
Depending on your extension's functionality, other using directives may be required.
Inherit from the CustomParameterDataProvider base class.
Set the string identifier for the data provider name. This must match the key that you add to the CustomParameterDataProviders section of the web.config file when you register the extension. For more information, see How to: Manually Register PerformancePoint Services Extensions.
Override the GetId() method to return the identifier for your data provider.
Override the GetDisplayDataInternal method to define a DataTable object to store the data values from the underlying data source. The filter uses this method to populate the filter selection control. The display data table must contain the following column names:
Key The unique identifier for the record. This value cannot be null. For performance and security purposes, controls emit only a key; they do not emit values from the other columns.
Display The value that appears in the filter control.
ParentKey This value is used to arrange hierarchical data in a tree control.
IsDefault This value is used for filter persistence.
Tip
You can add more columns to extend the filter's functionality.
GetDisplayDataInternal calls the DataSourceRegistry.GetDataSource(DataSource) method to verify the data source type by name, as follows:
- It references a custom data source type by using the SubTypeId property of the data source, which is the same value as the subType attribute that is registered in the PerformancePoint Services web.config file for the data source extension.
- It references a native data source by using the SourceName property, which returns a field from the DataSourceNames class.
Override the GetMessageData method to store the user's selection from the filter control. The filter uses this method when it sends the user's selections to consumers.
Code example: Create a data provider for custom PerformancePoint Services filters in SharePoint
The following code example shows how a data provider retrieves values from a web service or an Excel worksheet and returns DataTable objects for the filter's display data and message data.
Before you can compile this code example, you must configure your development environment as described in To create and configure the provider class.
using System.Data;
using Microsoft.PerformancePoint.Scorecards;
using Microsoft.PerformancePoint.Scorecards.Server.Extensions;
namespace Microsoft.PerformancePoint.SDK.Samples.SampleFilter
{
// Represents the sample filter's data provider.
public class SampleFilterDataProvider : CustomParameterDataProvider
{
// This value must match the key that you register for this extension
// in the CustomParameterDataProviders section in the web.config file.
private const string dataProviderName = "SampleFilterDataProvider";
// Returns a table of all possible values (rows) for the
// filter's beginpoints. The filter's BeginPoint property returns
// one ParameterDefinition object.
protected override DataTable GetDisplayDataInternal(ParameterDefinition parameterDefinition, RepositoryLocation parameterSourceLocation, object custom)
{
DataTable retrievedData = null;
// Get the data source.
DataSource parameterDataSource = SafeGetDataSource(parameterSourceLocation);
if (null != parameterDataSource)
{
// Verify that the data source is the sample data source
// or an Excel workbook, which are the types that the
// sample supports.
// If you modify these types of data source, you must make
// the corresponding change in the filter's editor.
if (parameterDataSource.SourceName == "WSTabularDataSource" || parameterDataSource.SourceName == DataSourceNames.ExcelWorkbook)
{
IDataSourceProvider parameterDataSourceProvider =
DataSourceRegistry.GetDataSource(parameterDataSource);
if (null != parameterDataSourceProvider)
{
var dataSourceMetadata = parameterDataSourceProvider as IDataSourceMetadata;
if (null != dataSourceMetadata)
{
// Get the data and store it in the retrievedDataSet
// variable. The -1 parameter returns all records
// from the data source.
DataSet retrievedDataSet = dataSourceMetadata.GetPreviewDataSet(-1);
// Verify that the dataset contains data.
if (retrievedDataSet != null &&
retrievedDataSet.Tables != null &&
retrievedDataSet.Tables.Count > 0 &&
retrievedDataSet.Tables[0] != null &&
retrievedDataSet.Tables[0].Columns != null &&
retrievedDataSet.Tables[0].Columns.Count > 0 &&
retrievedDataSet.Tables[0].Rows != null &&
retrievedDataSet.Tables[0].Rows.Count > 0 &&
retrievedDataSet.Tables[0].Columns.Contains(parameterDefinition.KeyColumn))
{
retrievedData = retrievedDataSet.Tables[0];
}
}
}
}
if (null != retrievedData)
{
// Name the display data table.
retrievedData.TableName = "ParamData";
// Verify that the table has the correct structure.
EnsureDataColumns(retrievedData, parameterDefinition);
bool firstRowSeen = false;
foreach (DataRow row in retrievedData.Rows)
{
// Set the ParentKeyColumn to null because the data
// does not have a hierarchical structure.
row[parameterDefinition.ParentKeyColumn] = null;
// Set the IsDefaultColumn column in the first row to true.
row[parameterDefinition.IsDefaultColumn] = !firstRowSeen;
if (!firstRowSeen)
{
firstRowSeen = true;
}
}
// Set the column visibility.
SetColumnVisibility(retrievedData);
}
}
return retrievedData;
}
// Adds the ShowColumn extended property to a column in the display data table
// and sets it to true. This exposes the column in Dashboard Designer as
// a source value for the beginpoint.
private static void SetColumnVisibility(DataTable displayData)
{
for (int i = 0; i < displayData.Columns.Count; i++)
{
if (!displayData.Columns[i].ExtendedProperties.Contains("ShowColumn"))
{
displayData.Columns[i].ExtendedProperties.Add("ShowColumn", true);
}
}
}
// Verify that all required columns are in the data table.
// The data table returned by this method is expected to contain a
// Key, ParentKey, IsDefault, Display, and an arbitrary number of
// Value columns.
// The specific column names (except for Value columns) are defined
// in the filter's ParameterDefinition object, which is referenced by
// the filter's BeginPoint property.
private static void EnsureDataColumns(DataTable dataTable, ParameterDefinition parameterDefinition)
{
if (!string.IsNullOrEmpty(parameterDefinition.KeyColumn) && !dataTable.Columns.Contains(parameterDefinition.KeyColumn))
{
dataTable.Columns.Add(parameterDefinition.KeyColumn);
}
if (!string.IsNullOrEmpty(parameterDefinition.DisplayColumn) && !dataTable.Columns.Contains(parameterDefinition.DisplayColumn))
{
dataTable.Columns.Add(parameterDefinition.DisplayColumn);
}
if (!string.IsNullOrEmpty(parameterDefinition.ParentKeyColumn) && !dataTable.Columns.Contains(parameterDefinition.ParentKeyColumn))
{
dataTable.Columns.Add(parameterDefinition.ParentKeyColumn);
}
if (!string.IsNullOrEmpty(parameterDefinition.IsDefaultColumn) && !dataTable.Columns.Contains(parameterDefinition.IsDefaultColumn))
{
dataTable.Columns.Add(parameterDefinition.IsDefaultColumn, typeof(bool));
}
}
// Returns the unique string identifier of the data provider.
// This value must match the key that you register for this extension
// in the CustomParameterDataProviders section in the web.config file.
public override string GetId()
{
return dataProviderName;
}
// Returns a table of rows that match the keys in the passed
// ParameterMessage object.
// This method is used by controls that accept parameters, such as
// scorecard and reports. It can also apply a Post Formula.
public override DataTable GetMessageData(RepositoryLocation providerLocation, ParameterMessage parameterMessage, RepositoryLocation parameterSourceLocation, ParameterMapping parameterMapping, object custom)
{
DataTable msgTable = null;
// The ParameterMapping object contains information about
// linked dashboard items.
// The CustomData object is optionally used to store information
// that is not stored in other properties.
DataTable displayTable = GetDisplayDataInternal(parameterMessage, parameterSourceLocation, custom);
if (null != displayTable)
{
msgTable = displayTable.Clone();
for (int i = 0;i < parameterMessage.Values.Rows.Count; i++)
{
for (int j = 0;j < displayTable.Rows.Count; j++)
{
if (!parameterMessage.Values.Rows[i][parameterMessage.KeyColumn].Equals(displayTable.Rows[j][parameterMessage.KeyColumn].ToString()))
continue;
msgTable.ImportRow(displayTable.Rows[j]);
break;
}
}
}
return msgTable;
}
}
}
Next steps
After you create a data provider and a filter editor (including its user interface, if required), deploy the extension as described in How to: Manually Register PerformancePoint Services Extensions.