Controls / RadPivotGrid / Populating with Data

New to Telerik UI for WPF? Download free 30-day trial

Using QueryableDataProvider

QueryableDataProvider is one of the data source providers that can be used with RadPivotGrid and RadPivotFieldList. QueryableDataProvider provides an easy way to use data from database with RadPivotGrid. In fact any collection that implements IQueryable interface can be used as a source of QueryableDataProvider.

Defining QueryableDataProvider

You have to add reference to the following Telerik assembly to be able to use QueryableDataProvider in your application:

  • Telerik.Pivot.DataProviders.Queryable

You can define the QueryableDataProvider as a StaticResource in your XAML if it will be used in more than one controls (for example if you have RadPivotGrid and RadPivotFieldList controls in your application) or define it directly for any of the controls you are planning to use:

The pivot namespace is URI namespace: xmlns:pivot="http://schemas.telerik.com/2008/xaml/presentation/pivot". It is mandatory to define it if you are using the QueryableDataProvider in your XAML.

  • Defined as a StaticResource and used in RadPivotGrid and RadPivotFieldList:

<Grid> 
    <Grid.ColumnDefinitions> 
        <ColumnDefinition Width="*"/> 
        <ColumnDefinition Width="250"/> 
    </Grid.ColumnDefinitions> 
    <Grid.Resources> 
        <pivot:QueryableDataProvider x:Key="QueryableDataProvider"> 
        </pivot:QueryableDataProvider> 
    </Grid.Resources> 
 
    <pivot:RadPivotGrid Name="radPivotGrid" DataProvider="{StaticResource QueryableDataProvider}"> 
    <pivot:RadPivotFieldList Name="radPivotFieldList" Grid.Column="1"  DataProvider="{StaticResource QueryableDataProvider}"/> 
</Grid>

If you set different DataProviders for RadPivotGrid and RadPivotFieldList you will not be able to see any changes in RadPivotGrid even when you change something in RadPivotFieldList.

You can also create an object of type QueryableDataProvider in the background and after that to use it for your controls: 

QueryableDataProvider queryableDataProvider = new QueryableDataProvider(); 
this.radPivotGrid1.DataProvider = queryableDataProvider; 
this.radPivotFieldList1.DataProvider = queryableDataProvider; 

Dim queryableDataProvider As New QueryableDataProvider() 
Me.radPivotGrid1.DataProvider = queryableDataProvider 
Me.radPivotFieldList1.DataProvider = queryableDataProvider

Adding Data to QueryableDataProvider

The QueryableDataProvider has Source property and it is mandatory to set it if you want to display any data. Source can be any instance of a class that implements IQueryable interface. 

QueryableDataProvider queryableDataProvider = new QueryableDataProvider(); 
queryableDataProvider.Source = MyCollection; 
this.radPivotGrid1.DataProvider = queryableDataProvider; 
this.radPivotFieldList1.DataProvider = queryableDataProvider; 

Dim queryableDataProvider As New QueryableDataProvider() 
queryableDataProvider.Source = MyCollection 
Me.radPivotGrid1.DataProvider = queryableDataProvider 
Me.radPivotFieldList1.DataProvider = queryableDataProvider

Adding Group Descriptions Collections

When initializing the QueryableDataProvider in the code behind it is a good idea to wrap all modifications in BeginInit() - EndInit() section. This will cause only one refresh of the DataProvider and it will be when the EndInit() is reached. If you are applying only modifications (more than one) on already initialized QueryableDataProvider you should use the DeferRefresh() method which will cause delay of the Refresh and this way all your changes will be applied simultaneously. More information for these methods is available in our Populating with Data - Overview article.

The QueryableDataProvider is using four different collections for the data that it holds:

  • RowGroupDescription - the data added to this description will be shown as rows headers in RadPivotGrid and RadPivotFieldList. The properties can be defined as QueryablePropertyGroupDescription, QueryableDateTimeGroupDescription, QueryableDoubleGroupDescription or you can create custom implementation of QueryableGroupDescription class.

Here's how to define the RowGroupDescriptions in your application: 

<pivot:QueryableDataProvider.RowGroupDescriptions> 
    <pivot:QueryablePropertyGroupDescription PropertyName="ShipCountry" /> 
    <pivot:QueryableDoubleGroupDescription PropertyName="Freight" /> 
    <pivot:QueryableDateTimeGroupDescription PropertyName="OrderDate" /> 
</pivot:QueryableDataProvider.RowGroupDescriptions> 


var shipCountryGroupDescription = new QueryablePropertyGroupDescription(); 
shipCountryGroupDescription.PropertyName = "ShipCountry"; 
 
var freightCountryGroupDescription = new QueryableDoubleGroupDescription(); 
freightCountryGroupDescription.PropertyName = "Freight";; 
 
var orderDateCountryGroupDescription = new QueryableDateTimeGroupDescription(); 
orderDateCountryGroupDescription.PropertyName = "OrderDate"; 
 
using (queryableDataProvider.DeferRefresh()) 
{ 
    queryableDataProvider.RowGroupDescriptions.Add(shipCountryGroupDescription); 
    queryableDataProvider.RowGroupDescriptions.Add(freightCountryGroupDescription); 
    queryableDataProvider.RowGroupDescriptions.Add(orderDateCountryGroupDescription); 
}; 

Dim shipCountryGroupDescription = New QueryablePropertyGroupDescription() 
shipCountryGroupDescription.PropertyName = "ShipCountry" 
 
Dim freightCountryGroupDescription = New QueryableDoubleGroupDescription() 
freightCountryGroupDescription.PropertyName = "Freight" 
 
Dim orderDateCountryGroupDescription = New QueryableDateTimeGroupDescription() 
orderDateCountryGroupDescription.PropertyName = "OrderDate" 
 
Using queryableDataProvider.DeferRefresh() 
    queryableDataProvider.RowGroupDescriptions.Add(shipCountryGroupDescription) 
    queryableDataProvider.RowGroupDescriptions.Add(freightCountryGroupDescription) 
    queryableDataProvider.RowGroupDescriptions.Add(orderDateCountryGroupDescription) 
End Using
  • ColumnGroupDescription - the data added to this description will be shown as columns headers in RadPivotGrid and RadPivotFieldList. The properties can be defined as QueryablePropertyGroupDescription, QueryableDateTimeGroupDescription, QueryableDoubleGroupDescription or you can create custom implementation of QueryableGroupDescription class.

Here's how to define the ColumnGroupDescriptions in your application: 

<pivot:QueryableDataProvider.ColumnGroupDescriptions> 
    <pivot:QueryableDoubleGroupDescription PropertyName="Freight"/> 
</pivot:QueryableDataProvider.ColumnGroupDescriptions> 


QueryableDoubleGroupDescription doubleGroupDescription = new QueryableDoubleGroupDescription(); 
doubleGroupDescription.PropertyName = "Freight"; 
queryableDataProvider.ColumnGroupDescriptions.Add(doubleGroupDescription); 

Dim doubleGroupDescription As New DoubleGroupDescription() 
doubleGroupDescription.PropertyName = "Freight" 
queryableDataProvider.ColumnGroupDescriptions.Add(doubleGroupDescription)
  • AggregateDescriptions - the data added to this description will be aggregated and included in RadPivotGrid as Cells. The properties can be defined as QueryablePropertyAggregateDescription or you can create custom implementation of QueryableAggregateDescription class.

Here's how to define the AggregateDescriptions in your application: 

<pivot:QueryableDataProvider.AggregateDescriptions> 
    <pivot:QueryablePropertyAggregateDescription AggregateFunction="Count" StringFormat="C" PropertyName="Freight"/> 
    <pivot:QueryablePropertyAggregateDescription PropertyName="ShipVia"/> 
</pivot:QueryableDataProvider.AggregateDescriptions> 


var freightAggregateDescription = new QueryablePropertyAggregateDescription(); 
freightAggregateDescription.PropertyName = "Freight"; 
freightAggregateDescription.StringFormat = "C"; 
freightAggregateDescription.AggregateFunction = QueryableAggregateFunction.Max; 
 
var shipViaAggregateDescription = new QueryablePropertyAggregateDescription(); 
freightAggregateDescription.PropertyName = "ShipVia"; 
 
 
using (queryableDataProvider.DeferRefresh()) 
{ 
    queryableDataProvider.AggregateDescriptions.Add(freightAggregateDescription); 
    queryableDataProvider.AggregateDescriptions.Add(shipViaAggregateDescription); 
}; 

Dim freightAggregateDescription = New QueryablePropertyAggregateDescription() 
freightAggregateDescription.PropertyName = "Freight" 
freightAggregateDescription.StringFormat = "C" 
freightAggregateDescription.AggregateFunction = QueryableAggregateFunction.Max 
 
Dim shipViaAggregateDescription = New QueryablePropertyAggregateDescription() 
freightAggregateDescription.PropertyName = "ShipVia" 
 
Using queryableDataProvider.DeferRefresh() 
    queryableDataProvider.AggregateDescriptions.Add(freightAggregateDescription) 
    queryableDataProvider.AggregateDescriptions.Add(shipViaAggregateDescription) 
End Using

With R2 2016 of UI for WPF a brand new property IgnoreNullValues was introduced for the PropertyAggregateDescription. This property is of type bool and it is used to determine whether a specific PropertyAggregateDescription should ignore the null values when calculating its result. The default value of the property is false, so in order to ignore the null values, you should set the property to true.

Here's how to define the AggregateDescriptions in your application with a set IgnoreNullValues property:: 

<pivot:QueryableDataProvider.AggregateDescriptions> 
    <pivot:QueryablePropertyAggregateDescription AggregateFunction="Count" StringFormat="C" PropertyName="Freight" IgnoreNullValues="true"/> 
    <pivot:QueryablePropertyAggregateDescription PropertyName="ShipVia"/> 
</pivot:QueryableDataProvider.AggregateDescriptions>

In order to set the IgnoreNullValues to true for all PropertyAggregateDescriptions you would add in the LocalDataSourceProvider you should handle the LocalDataSourceProvider.PrepareDescriptionForField event and set IgnoreNullValues in the handler:

private void QueryableDataProvider_PrepareDescriptionForField(object sender, Telerik.Pivot.Core.PrepareDescriptionForFieldEventArgs e) 
{ 
    var description = e.Description as QueryablePropertyAggregateDescription; 
    if (description != null) 
    { 
        description.IgnoreNullValues = true; 
    } 
} 

Private Sub QueryableDataProvider_PrepareDescriptionForField(sender As Object, e As Telerik.Pivot.Core.PrepareDescriptionForFieldEventArgs) 
    Dim description = TryCast(e.Description, QueryablePropertyAggregateDescription) 
    If description IsNot Nothing Then 
        description.IgnoreNullValues = True 
    End If 
End Sub
  • FilterDescriptions - the data added to this description will be filtered and after that included in RadPivotGrid. The properties can be defined as QueryablePropertyFilterDescription or you can create custom implementation of QueryableFilterDescription class.

<pivot:QueryableDataProvider.FilterDescriptions> 
    <pivot:QueryablePropertyFilterDescription PropertyName="Freight"> 
        <pivot:QueryablePropertyFilterDescription.Condition> 
            <queryableFilters:QueryableIntervalCondition From="10" To="200"/> 
        </pivot:QueryablePropertyFilterDescription.Condition> 
    </pivot:QueryablePropertyFilterDescription> 
</pivot:QueryableDataProvider.FilterDescriptions> 


var freightFilterDescription = new QueryablePropertyFilterDescription(); 
freightFilterDescription.PropertyName = "Freight"; 
var intervalCondition = new QueryableIntervalCondition(); 
intervalCondition.From = 10; 
intervalCondition.To = 200; 
freightFilterDescription.Condition = intervalCondition; 
 
queryableDataProvider.FilterDescriptions.Add(freightFilterDescription); 

Dim freightFilterDescription = New QueryablePropertyFilterDescription() 
freightFilterDescription.PropertyName = "Freight" 
Dim intervalCondition = New QueryableIntervalCondition() 
intervalCondition.From = 10 
intervalCondition.To = 200 
freightFilterDescription.Condition = intervalCondition 
 
queryableDataProvider.FilterDescriptions.Add(freightFilterDescription)

Adding Property Descriptions

All property description classes are inheriting the abstract class DescriptionBase. That's why all of them have the following properties:

  • PropertyName - this is the most important property. It must be set to the property of the data that will be represent with this property description.

  • CustomName - sets the name that will be shown instead of the property name in RadPivotGrid and RadPivotFieldList.

Here is a list of the property descriptions that you can use:

  • QueryablePropertyGroupDescription - available for RowGroupDescriptions and ColumnGroupDescriptions.

  • QueryableDoubleGroupDescription - available for RowGroupDescriptions and ColumnGroupDescriptions. Used when the data is of numeric type. One of the imporant properties is Step - it is used to define the size of the generated groups.

  • QueryableDateTimeGroupDescription - available for RowGroupDescriptions and ColumnGroupDescriptions. Used when the data is of type DateTime. Very useful is Step property where you can set if the grouping should be on year, quarter, month or day.

  • QueryablePropertyFilterDescription - available for FilterDescriptions only. The important property here is Condition as the filtering is done based on it. You can use four conditions: QueryableComparisonCondition, QueryableIntervalCondition, QueryableSetCondition, QueryableTextCondition, QueryableItemsFilterCondition.

  • QueryablePropertyAggregateDescription - available for AggregateDescriptions only. You have to define the AggregateFunction that will be used. You have five predefined functions - Average, Count, Min, Max and Sum.

In this article
Related articles
Not finding the help you need? Improve this article