How to: Create Data Providers for Filters
The SQL Query Filter is a custom filter that is based on an SQL statement or stored procedure. The filter uses a data provider to interact with an ODBC data source and to organize the filter data into two types of DataTable object:
Display data DataTable. Contains the list of choices that are displayed in a filter control. It is used by parameterizable controls that emit a filter, such as the SingleSelectDropDownControl. The table must contain the following columns:
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. The SQL Query Filter does not demonstrate filter persistence.
Note
You can add more columns to extend the filter's functionality.
Message data DataTable. Contains the selections that users choose from a filter control. It is used in parameterizable controls that accept filter values, such as scorecards and reports.
The complete code example for the SQL Query Filter data provider is provided in the Example section. For more information about data providers, see Filters Overview.
To create a data provider for the SQL Query Filter
Create the SqlQueryParameterDataProvider class in the SqlQueryParameterProvider namespace.
Add the following using directives.
using System; using System.Data; using System.Data.Odbc; using System.Reflection; using Microsoft.PerformancePoint.Scorecards; using Microsoft.PerformancePoint.Scorecards.DataSourceProviders; using Microsoft.PerformancePoint.Scorecards.Extensions;Inherit from CustomParameterDataProvider.
Set the string identifier for the data provider name.
To return the identifier for your data provider, override the GetID method.
To return the items that display in the filter, override the GetDisplayData method.
To return the user-selected items, override the GetMessageData method.
Example
The following code example demonstrates how a data provider retrieves and runs an SQL statement and returns DataTable objects for the filter display data and message data.
Prerequisites
Before you compile the code example, ensure you have met the following prerequisites:
Install PerformancePoint Monitoring Server.
Install the SQL Query Filter sample. To download the sample, see Creating a Custom Dashboard Filter for PerformancePoint Server 2007 ~ Code Sample.
Be prepared to 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 KeyPair.
using System;
using System.Data;
using System.Data.Odbc;
using System.Reflection;
using Microsoft.PerformancePoint.Scorecards;
using Microsoft.PerformancePoint.Scorecards.DataSourceProviders;
using Microsoft.PerformancePoint.Scorecards.Extensions;
namespace SqlQueryParameterProvider
{
class SqlQueryParameterDataProvider : CustomParameterDataProvider
{
#region Provide Custom Identifiers
// Set the unique string ID for this data provider.
public static readonly string DataProviderName = "SqlQueryParameterDataProvider";
#endregion
#region Override Base Class Methods
// Return the unique string ID for this data provider.
public override string GetId()
{
return DataProviderName;
}
// Return the filter's display data as a DataTable.
public override DataTable GetDisplayData(ParameterDefinition parameterDefinition, Guid parameterSourceId)
{
// Retrieve the query.
DataTable retrievedDataTable = null;
SqlParameterDefinition customDefinition = SqlParameterDefinition.Deserialize(parameterDefinition.CustomDefinition);
if (customDefinition != null)
{
DataSource dataSource = SafeGetDataSource(parameterSourceId);
if (dataSource != null)
{
if (dataSource.SourceName == DataSourceNames.Odbc)
{
OdbcDataSourceProvider odbcDataSource = new OdbcDataSourceProvider();
odbcDataSource.SetDataSource(dataSource);
PropertyInfo connectionStringInfo =
odbcDataSource.GetType().GetProperty("ConnectionString",
BindingFlags.Instance | BindingFlags.NonPublic);
string connectionString = (string)connectionStringInfo.GetValue(odbcDataSource, null);
if (!string.IsNullOrEmpty(connectionString))
{
// Open the connection to the data source.
using (OdbcConnection connection = new OdbcConnection(connectionString))
{
using (new RevertToSelfHelper())
{
connection.Open();
}
OdbcCommand command = null;
OdbcDataReader reader = null;
try
{
// Run the query.
command = new OdbcCommand(customDefinition.Expression);
command.Connection = connection;
reader = command.ExecuteReader();
if (reader.HasRows)
{
retrievedDataTable = new DataTable();
retrievedDataTable.Load(reader);
}
}
finally
{
if (command != null)
command.Dispose();
if (reader != null)
reader.Close();
}
connection.Close();
}
}
}
}
}
DataTable dataTable = null;
if (null!= retrievedDataTable)
{
// Create the DataTable for the display data and
// populate it with the results of the query.
dataTable = new DataTable("ParamData");
if (!string.IsNullOrEmpty(parameterDefinition.KeyColumn))
{
dataTable.Columns.Add(parameterDefinition.KeyColumn);
}
if (!string.IsNullOrEmpty(parameterDefinition.DisplayColumn))
{
dataTable.Columns.Add(parameterDefinition.DisplayColumn);
}
if (!string.IsNullOrEmpty(parameterDefinition.ParentKeyColumn))
{
dataTable.Columns.Add(parameterDefinition.ParentKeyColumn);
}
if (!string.IsNullOrEmpty(parameterDefinition.IsDefaultColumn))
{
dataTable.Columns.Add(parameterDefinition.IsDefaultColumn, typeof(bool));
}
for (int i = 0; i < retrievedDataTable.Rows.Count; i++)
{
DataRow row = dataTable.NewRow();
// Use the first column as the key.
if (retrievedDataTable.Columns.Count >= 1)
{
row[parameterDefinition.KeyColumn] = retrievedDataTable.Rows[i][0];
// If there is only one column, use it as the display value.
// Otherwise, use the second column as the display value.
if (retrievedDataTable.Columns.Count == 1)
{
row[parameterDefinition.DisplayColumn] =
retrievedDataTable.Rows[i][0];
}
else
{
row[parameterDefinition.DisplayColumn] =
retrievedDataTable.Rows[i][1];
}
row[parameterDefinition.ParentKeyColumn] = null;
row[parameterDefinition.IsDefaultColumn] = (i == 0);
}
dataTable.Rows.Add(row);
}
for (int i = 0; i < dataTable.Columns.Count; i++)
{
if (!dataTable.Columns.Contains("ShowColumn"))
{
dataTable.Columns[i].ExtendedProperties.Add("ShowColumn", true);
}
}
}
return dataTable;
}
// Create the DataTable that contains the user's selections.
public override DataTable GetMessageData(ParameterMessage parameterMessage, Guid parameterSourceId, ParameterMapping parameterMapping, ParameterMessage[] parameterMessages)
{
DataTable msgTable = null;
DataTable dataTable = GetDisplayData(parameterMessage, parameterSourceId);
if (dataTable != null)
{
msgTable = dataTable.Clone();
for (int i = 0; i < parameterMessage.Values.Rows.Count; i++)
{
for (int j = 0; j < dataTable.Rows.Count; j++)
{
// Get the rows that match the keys in the filter message.
if (parameterMessage.Values.Rows[i][parameterMessage.KeyColumn].Equals
(dataTable.Rows[j][parameterMessage.KeyColumn].ToString()))
{
msgTable.ImportRow(dataTable.Rows[j]);
break;
}
}
}
}
return msgTable;
}
#endregion
}
}
See Also
Tasks
How to: Install Data Providers for Filters