You can use field formatting to group data. Suppose you have a bound list of products and you want to group all of 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, ProductName, and UnitPrice. 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:

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.