You can use field formatting to group data. Suppose you have a bound list of products and you want to group all the items ordered within a year together. You can use the Field Settings dialog box at run time or code. In this example, we'll use a C1OlapPage control bound to the C1Nwind.mdb installed with the product.

To group data by the year at run time:

1. Add the following fields to the grid view by selecting them in the C1OlapPanel area of the C1OlapPage: OrderDate, Product, and Sales. Click the Olap Grid tab, if necessary, to view the grid.
2. Right-click the Order Date field under Row Fields and select Field Settings. The Field Settings dialog box appears.
3. Make sure Select All is selected on the Filter tab.
4. Click the Format tab and select Custom.
5. Enter "yyyy" in the Custom Format text box and click OK.

The following images show the grid before grouping and after grouping.

The Before Grouping image displays data that is not grouped. The After Grouping image displays data where products are grouped by the year they were purchased.

Before Grouping

After Grouping

To group data in code:

You can also group data in code. Here is the code that would be used for the example above:

Imports C1.Olap
Imports System.Data.OleDb
Namespace WindowsFormsApplication1
    Public Partial Class Form1
        Inherits Form
        Public Sub New()
            InitializeComponent()

            ' get data          

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

            ' bind to olap page          

            Me.c1OlapPage1.DataSource = dt

            ' build view          

            Dim olap = Me.c1OlapPage1.OlapEngine
            olap.ValueFields.Add("UnitPrice")
            olap.RowFields.Add("OrderDate", "ProductName")

            ' format order date to group data          

            Dim field = olap.Fields("OrderDate")
            field.Format = "yyyy"
        End Sub
        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
    End Class
End Namespace

using C1.Olap;
using System.Data.OleDb;
namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            // get data           
            var da = new OleDbDataAdapter("select * from invoices", GetConnectionString());
            var dt = new DataTable();
            da.Fill(dt);
           
            // bind to olap page           
            this.c1OlapPage1.DataSource = dt;
           
            // build view           
            var olap = this.c1OlapPage1.OlapEngine;
            olap.ValueFields.Add("UnitPrice");
            olap.RowFields.Add("OrderDate", "ProductName");
           
            // format order date to group data           
            var field = olap.Fields["OrderDate"];
            field.Format = "yyyy";
        }
        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);
        }
    }
}

Collapse and Expand Groups

C1OlapGrid also provides users the functionality to display only summary or detail data in a group through code, by using following methods:

• CollapseAllRows: This method is used to collapse group of rows when there are many levels of data in a group of rows. For example, using CollapseAllRows, you can view year-wise total sales as shown below:
  
  
• CollapseAllCols: This method is used to collapse group of columns when only summary data is required to be viewed from many levels of data in a group of columns.
• ExpandAllRows: This method is used to expand group of rows to view the detailed data in the collapsed rows. Alternatively, you can click '+' button at runtime.
• ExpandAllCols: This method is used to expand group of columns to view the detailed data in the collapsed columns. Alternatively, you can click '+' button at runtime.

The following codes illustrates how to set these properties:

• To collapse group of rows

  VB	Copy Code
  c1OlapPage1.OlapGrid.CollapseAllRows()

  
  

  C#	Copy Code
  c1OlapPage1.OlapGrid.CollapseAllRows();

• To expand group of rows

  VB	Copy Code
  c1OlapPage1.OlapGrid.ExpandAllRows()

  
  

  C#	Copy Code
  c1OlapPage1.OlapGrid.ExpandAllRows();

Similarly, properties for collapsing and expanding of group of columns can be set.