Configuring Fields in Code

FlexPivot allows users to configure fields programmatically. The control comes with a powerful object model that enables developers in configuring fields, applying filters, and specifying format of data fields in code.

To configure fields in code, complete the following steps.

Create a new Windows Forms Application project.
Drag-and-drop FlexPivotPage control (see the C1FlexPivotPage icon in the Toolbox) onto the form.

Switch to the code view and add the following code to set up a connection string with c1nwind.mdb database.

Visual Basic
C#

Visual Basic

Private Shared Function GetConnectionString() As String
    Dim path As String = Environment.GetFolderPath(Environment.SpecialFolder.Personal) + "\ComponentOne Samples\Common"
    Dim conn As String = "provider=microsoft.jet.oledb.4.0;data source={0}\c1nwind.mdb;"
    Return String.Format(conn, path)
End Function

static string GetConnectionString()
{
    string path = Environment.GetFolderPath(Environment.SpecialFolder.Personal) + @"\ComponentOne Samples\Common";
    string conn = @"provider=microsoft.jet.oledb.4.0;data source={0}\c1nwind.mdb;";
    return string.Format(conn, path);
}

Add the following code within the Form's constructor to load data (Invoices view) from the database, assign it to the FlexPivotPage control, and initialize a default view.

Visual Basic
C#

Visual Basic

' get data
Dim da = New OleDbDataAdapter("select * from invoices", GetConnectionString())
Dim dt = New DataTable()
da.Fill(dt)

' bind to FlexPivot page
Me.C1FlexPivotPage1.DataSource = dt

' build view
Dim fp = Me.C1FlexPivotPage1.FlexPivotEngine
fp.ValueFields.Add("ExtendedPrice")
fp.RowFields.Add("OrderDate", "ProductName")

// get data
var da = new OleDbDataAdapter("select * from invoices", GetConnectionString());
var dt = new DataTable();
da.Fill(dt);

// bind to FlexPivot page
this.c1FlexPivotPage1.DataSource = dt;

// build view
var fp = this.c1FlexPivotPage1.FlexPivotEngine;
fp.ValueFields.Add("ExtendedPrice");
fp.RowFields.Add("OrderDate", "ProductName");

Use the following code to format the ExtendedPrice and OrderDate fields. This code sets the format of the ExtendedPrice field to Currency and that of the OrderDate field to Year.

Visual Basic
C#

Visual Basic

' format order date and extended price
Dim field = fp.Fields("OrderDate")
field.Format = "yyyy"
field = fp.Fields("ExtendedPrice")
field.Format = "c"

' show average price (instead of sum)
field = fp.Fields("ExtendedPrice")
field.Subtotal = C1.FlexPivot.Subtotal.Average

// format order date and extended price
var field = fp.Fields["OrderDate"];
field.Format = "yyyy";
field = fp.Fields["ExtendedPrice"];
field.Format = "c";

// show average price (instead of sum)
field = fp.Fields["ExtendedPrice"];
field.Subtotal = C1.FlexPivot.Subtotal.Average;

Add the following code to apply filter on products. This code applies filter to display only 4 products that include Chai, Chang, Geitost and Ikura.

Visual Basic
C#

Visual Basic

' apply value filter to show only a few products
Dim filter As C1.FlexPivot.C1FlexPivotFilter = fp.Fields("ProductName").Filter
filter.Clear()
filter.ShowValues = "Chai,Chang,Geitost,Ikura".Split(","c)

// apply value filter to show only a few products
C1.FlexPivot.C1FlexPivotFilter filter = fp.Fields["ProductName"].Filter;
filter.Clear();
filter.ShowValues = "Chai,Chang,Geitost,Ikura".Split(',');

Add the following code to apply filter on OrderDate field. This code filters OrderDate from January 1st, 2013 to December 31st, 2014.

Visual Basic
C#

Visual Basic

' apply range filter to show only some dates
filter = fp.Fields("OrderDate").Filter
filter.Clear()
filter.Condition1.[Operator] = C1.FlexPivot.ConditionOperator.GreaterThanOrEqualTo
filter.Condition1.Parameter = New DateTime(2014, 1, 1)
filter.Condition2.[Operator] = C1.FlexPivot.ConditionOperator.LessThanOrEqualTo
filter.Condition2.Parameter = New DateTime(2014, 12, 31)
filter.AndConditions = True

// apply range filter to show only some dates
filter = fp.Fields["OrderDate"].Filter;
filter.Clear();
filter.Condition1.Operator = C1.FlexPivot.ConditionOperator.GreaterThanOrEqualTo;
filter.Condition1.Parameter = new DateTime(2014, 1, 1);
filter.Condition2.Operator = C1.FlexPivot.ConditionOperator.LessThanOrEqualTo;
filter.Condition2.Parameter = new DateTime(2014, 12, 31);
filter.AndConditions = true;

Run the application. The form appears with a custom view showing fields set in the code.