Using the 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.
The main idea for creating this provider is to be used when the data source for RadPivotGrid is in database. With QueryableDataProvider all calculations and aggregations are executed on the database server. This way the whole collection of items is saved only on the server and not in the memory of the application. This makes QueryableDataProvider the preferred provider when you have a huge database with several thousands or millions of records. As the aggregation of data and the calculations are performed on the server, there are some limitations for the functions that you can use.
Defining QueryableDataProvider
You can create an object of type QueryableDataProvider and assign it to RadPivotGrid. The QueryableDataProvider has a Source property and you would need to set it in order to display any data. The following code snippet demonstrates this:
Set IQueryable Source
this.queryableDataProvider = new QueryableDataProvider() { Source = dataset.Orders.AsQueryable() };
Me.queryableDataProvider = New QueryableDataProvider() With {
.Source = dataset.Orders.AsQueryable()
}
The Source can be any collection that implements the IQuearyable interface.
Adding Group Descriptions Collections
When initializing the QueryableDataProvider it is a good idea to wrap all modifications in BeginInit/EndInit. This will cause only one refresh of the data provider and it will be when the EndInit is called. 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.
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 the QueryableGroupDescription class.
Here's how to define the RowGroupDescriptions in your application:
Adding Queryable Row Descriptions
QueryablePropertyGroupDescription shipCountryGroupDescription = new QueryablePropertyGroupDescription();
shipCountryGroupDescription.PropertyName = "ShipCountry";
QueryableDoubleGroupDescription freightCountryGroupDescription = new QueryableDoubleGroupDescription();
freightCountryGroupDescription.PropertyName = "Freight"; ;
QueryableDateTimeGroupDescription orderDateCountryGroupDescription = new QueryableDateTimeGroupDescription();
orderDateCountryGroupDescription.PropertyName = "OrderDate";
using (queryableDataProvider.DeferRefresh())
{
queryableDataProvider.RowGroupDescriptions.Add(shipCountryGroupDescription);
queryableDataProvider.RowGroupDescriptions.Add(freightCountryGroupDescription);
queryableDataProvider.RowGroupDescriptions.Add(orderDateCountryGroupDescription);
}
Dim shipCountryGroupDescription As New QueryablePropertyGroupDescription()
shipCountryGroupDescription.PropertyName = "ShipCountry"
Dim freightCountryGroupDescription As New QueryableDoubleGroupDescription()
freightCountryGroupDescription.PropertyName = "Freight"
Dim orderDateCountryGroupDescription As 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 the QueryableGroupDescription class.
Here's how to define the ColumnGroupDescriptions in your application:
Adding Queryable Column Descriptions
this.queryableDataProvider.ColumnGroupDescriptions.Add(new QueryableDoubleGroupDescription
{
PropertyName = "Freight"
});
Me.queryableDataProvider.ColumnGroupDescriptions.Add(New QueryableDoubleGroupDescription() With {
.PropertyName = "Freight"
})
- 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 the QueryableAggregateDescription class.
The QueryablePropertyAggregateDescriptionBase defines an IgnoreNullValues property determining whether the aggregate function will ignore null values when calculating the result. The default value of the property is false.
Here's how to define the AggregateDescriptions in your application:
Adding Queryable Aggregate Descriptions
QueryablePropertyAggregateDescription freightAggregateDescription = new QueryablePropertyAggregateDescription
{
PropertyName = "Freight",
StringFormat = "C",
AggregateFunction = QueryableAggregateFunction.Max
};
QueryablePropertyAggregateDescription shipViaAggregateDescription = new QueryablePropertyAggregateDescription
{
PropertyName = "ShipVia"
};
using (queryableDataProvider.DeferRefresh())
{
queryableDataProvider.AggregateDescriptions.Add(freightAggregateDescription);
queryableDataProvider.AggregateDescriptions.Add(shipViaAggregateDescription);
}
Dim freightAggregateDescription As New QueryablePropertyAggregateDescription() With {
.PropertyName = "Freight",
.StringFormat = "C",
.AggregateFunction = QueryableAggregateFunction.Max
}
Dim shipViaAggregateDescription As New QueryablePropertyAggregateDescription() With {
.PropertyName = "ShipVia"
}
Using queryableDataProvider.DeferRefresh()
queryableDataProvider.AggregateDescriptions.Add(freightAggregateDescription)
queryableDataProvider.AggregateDescriptions.Add(shipViaAggregateDescription)
End Using
- 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.
Here's how to define the FilterDescriptions in your application:
Adding Queryable Filter Descriptions
QueryableIntervalCondition intervalCondition = new QueryableIntervalCondition();
intervalCondition.From = 10;
intervalCondition.To = 200;
QueryablePropertyFilterDescription freightFilterDescription = new QueryablePropertyFilterDescription();
freightFilterDescription.PropertyName = "Freight";
freightFilterDescription.Condition = intervalCondition;
this.queryableDataProvider.FilterDescriptions.Add(freightFilterDescription);
Dim intervalCondition As New QueryableIntervalCondition()
intervalCondition.From = 10
intervalCondition.[To] = 200
Dim freightFilterDescription As New QueryablePropertyFilterDescription()
freightFilterDescription.PropertyName = "Freight"
freightFilterDescription.Condition = intervalCondition
Me.queryableDataProvider.FilterDescriptions.Add(freightFilterDescription)
Adding Property Descriptors
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 represented 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:
PropertyGroupDescription: Available for RowGroupDescriptions and ColumnGroupDescriptions.
DoubleGroupDescription: Available for RowGroupDescriptions and ColumnGroupDescriptions. Used when the data is of type Double. One of the important properties is Step - it is used to define the size of the generated groups.
DateTimeGroupDescription: Available for RowGroupDescriptions and ColumnGroupDescriptions. Used when the data is of type DateTime. Very useful is the Step property where you can set if the grouping should be on Day, Month or Year.
PropertyFilterDescription: Available for FilterDescriptions only. The important property here is Condition as the filtering is done based on it. You can use four conditions: ComparisonCondition, IntervalCondition, SetCondition, TextCondition.
PropertyAggregateDescription: Available for AggregateDescriptions only. You have to define the aggregate function that will be used. You can choose between various predefined functions like Average, Sum, Min, Max etc.
Applying the Data Provider to RadPivotGrid
To apply the already defined data provider, use the following property:
Set Data Provider
this.radPivotGrid1.PivotGridElement.DataProvider = queryableDataProvider;
Me.RadPivotGrid1.PivotGridElement.DataProvider = queryableDataProvider