The examples in previous sections all used the C1OlapPage control, which contains a complete UI and requires little or no code. In this section, we will walk through the creation of an OLAP application that does not use the C1OlapPage. Instead, it creates a complete custom UI using the C1OlapGrid, C1OlapChart, and some standard Silverlight controls.

The complete source code for this application is included in the “CustomUI” sample installed with OLAP for Silverlight and WPF.

The image below shows the application in design view:

Our Grid layout consists of 2 rows and 4 columns. There is a TextBlock filling to the top row showing the application title. There is a vertical StackPanel control in the left-most column with two groups of buttons. The top group allows users to pick one of three pre-defined views: sales by salesperson, by product, or by country. The next group allows users to apply a filter to the data based on product price (expensive, moderate, or inexpensive).

The remaining columns have an empty C1OlapGrid, GridSplitter and an empty C1OlapChart respectively. These are the controls that will display the view currently selected.

Once all the controls are in place, let’s add the code that connects them all and makes the application work.

In code we declare a C1OlapPanel. In previous examples the C1OlapPanel part is visible to the end-user. But in this sample we use it behind-the scenes, so users won’t ever see it. This invisible control is used as a data source for the grid and the chart, and is responsible for filtering and summarizing the data. Both the grid and the chart have their DataSource property set to the C1OlapPanel.

C1OlapPanel _olapPanel = new C1OlapPanel();

The code below first loads Northwind data from an XML data schema file. We use Data for Silverlight, which provides us the familiar DataSet and DataTable objects to read the data in. We also use Zip for Silverlight to unpackage the zipped XML file on the client. We assign the resulting DataTable to the C1OlapPanel.DataSource property. We also assign our C1OlapPanel control to our C1OlapGrid and C1OlapChart controls DataSource property. Finally, we simulate clicks on two buttons to initialize the current view and filter.

Public MainPage()
    InitializeComponent()

    Dim ds = New DataSet()
    Dim asm = Assembly.GetExecutingAssembly()
    Using s = asm.GetManifestResourceStream("CustomUI.Resources.nwind.zip")
        Dim zip = New C1ZipFile(s)
                ' load data            ds.ReadXml(zr);
        Using zr = zip.Entries(0).OpenReader()
        End Using
    End Using
    ' bind olap grid/chart to panel    _olapChart.DataSource = _olapPanel;
    _olapGrid.DataSource = _olapPanel
    ' bind olap panel to data    _olapPanel.DataSource = ds.Tables[0].DefaultView;
    ' start with the SalesPerson view, all products    _btnSalesperson_Click(this, null);
    _btnAllPrices_Click(Me, Nothing)
End Sub
'The event handlers for the buttons that select the current view look like this:
Private Sub _btnSalesperson_Click(sender As Object, e As RoutedEventArgs)
    BuildView("SalesPerson")
End Sub
Private Sub _btnProduct_Click(sender As Object, e As RoutedEventArgs)
    BuildView("ProductName")
End Sub
Private Sub _btnCountry_Click(sender As Object, e As RoutedEventArgs)
    BuildView("Country")
End Sub
'All handlers use a BuildView helper method given below:
' rebuild the view after a button was clickedvoid BuildView(string fieldName)
If True Then
    ' get olap engine    var olap = _olapPanel.OlapEngine;
    ' stop updating until done    olap.BeginUpdate();
    ' clear all fields    olap.RowFields.Clear();
    olap.ColumnFields.Clear()
    olap.ValueFields.Clear()
    ' format order dates to group by year    var f = olap.Fields["OrderDate"];
    f.Format = "yyyy"
    ' build up view    olap.ColumnFields.Add("OrderDate");
    olap.RowFields.Add(fieldName)
    olap.ValueFields.Add("ExtendedPrice")
    ' restore updates    olap.EndUpdate();
End If

public MainPage()
{
    InitializeComponent();
         
    var ds = new DataSet();
    var asm = Assembly.GetExecutingAssembly();
    using (var s = asm.GetManifestResourceStream("CustomUI.Resources.nwind.zip"))
    {
        var zip = new C1ZipFile(s);
        using (var zr = zip.Entries[0].OpenReader())
        {
            // load data            ds.ReadXml(zr);
        }
    }
    // bind olap grid/chart to panel    _olapChart.DataSource = _olapPanel;
    _olapGrid.DataSource = _olapPanel;
    // bind olap panel to data    _olapPanel.DataSource = ds.Tables[0].DefaultView;
    // start with the SalesPerson view, all products    _btnSalesperson_Click(this, null);
    _btnAllPrices_Click(this, null);
}
//The event handlers for the buttons that select the current view look like this:
void _btnSalesperson_Click(object sender, RoutedEventArgs e)
{
    BuildView("SalesPerson");
}
void _btnProduct_Click(object sender, RoutedEventArgs e)
{
    BuildView("ProductName");
}
void _btnCountry_Click(object sender, RoutedEventArgs e)
{
    BuildView("Country");
}
//All handlers use a BuildView helper method given below:
// rebuild the view after a button was clickedvoid BuildView(string fieldName)
{
    // get olap engine    var olap = _olapPanel.OlapEngine;
    // stop updating until done    olap.BeginUpdate();
    // clear all fields    olap.RowFields.Clear();
    olap.ColumnFields.Clear();
    olap.ValueFields.Clear();
    // format order dates to group by year    var f = olap.Fields["OrderDate"];
    f.Format = "yyyy";
    // build up view    olap.ColumnFields.Add("OrderDate");
    olap.RowFields.Add(fieldName);
    olap.ValueFields.Add("ExtendedPrice");
    // restore updates    olap.EndUpdate();
}

The BuildView method gets a reference to the C1OlapEngine object provided by the C1OlapPanel and immediately calls the BeginUpdate method to stop updates until the new view has been completely defined. This is done to improve performance.

The code then sets the format of the “OrderDate” field to “yyyy” so sales are grouped by year and rebuilds view by clearing the engine’s RowFields, ColumnFields, and ValueFields collections, then adding the fields that should be displayed. The “fieldName” parameter passed by the caller contains the name of the only field that changes between views in this example.

When all this is done, the code calls EndUpdate so the C1OlapPanel will update the output table.

Before running the application, let’s look at the code that implements filtering. The event handlers look like this:

Private Sub _btnExpensive_Click(sender As Object, e As RoutedEventArgs)
    SetPriceFilter("Expensive Products (price > $50)", 50, Double.MaxValue)
End Sub
Private Sub _btnModerate_Click(sender As Object, e As RoutedEventArgs)
    SetPriceFilter("Moderately Priced Products ($20 < price < $50)", 20, 50)
End Sub
Private Sub _btnInexpensive_Click(sender As Object, e As RoutedEventArgs)
    SetPriceFilter("Inexpensive Products (price < $20)", 0, 20)
End Sub
Private Sub _btnAllPrices_Click(sender As Object, e As RoutedEventArgs)
    SetPriceFilter("All Products", 0, Double.MaxValue)
End Sub

void _btnExpensive_Click(object sender, RoutedEventArgs e)
{
    SetPriceFilter("Expensive Products (price > $50)", 50, double.MaxValue);
}
void _btnModerate_Click(object sender, RoutedEventArgs e)
{
    SetPriceFilter("Moderately Priced Products ($20 < price < $50)", 20, 50);
}
void _btnInexpensive_Click(object sender, RoutedEventArgs e)
{
    SetPriceFilter("Inexpensive Products (price < $20)", 0, 20);
}
void _btnAllPrices_Click(object sender, RoutedEventArgs e)
{
    SetPriceFilter("All Products", 0, double.MaxValue);
}

All handlers use a SetPriceFilter helper method given below:

' apply a filter to the product pricevoid SetPriceFilter(string footerText, double min, double max)
If True Then
    ' get olap engine    var olap = _olapPanel.OlapEngine;
    ' stop updating until done    olap.BeginUpdate();
    ' make sure unit price field is active in the view    var field = olap.Fields["UnitPrice"];
    olap.FilterFields.Add(field)
    ' customize the filter    var filter = field.Filter;
    filter.Clear()
    filter.Condition1.[Operator] = C1.Olap.ConditionOperator.GreaterThanOrEqualTo
    filter.Condition1.Parameter = min
    filter.Condition2.[Operator] = C1.Olap.ConditionOperator.LessThanOrEqualTo
    filter.Condition2.Parameter = max
    ' restore updates    olap.EndUpdate();
End If

// apply a filter to the product pricevoid SetPriceFilter(string footerText, double min, double max)
{
    // get olap engine    var olap = _olapPanel.OlapEngine;
    // stop updating until done    olap.BeginUpdate();
    // make sure unit price field is active in the view    var field = olap.Fields["UnitPrice"];
    olap.FilterFields.Add(field);
    // customize the filter    var filter = field.Filter;
    filter.Clear();
    filter.Condition1.Operator = C1.Olap.ConditionOperator.GreaterThanOrEqualTo;
    filter.Condition1.Parameter = min;
    filter.Condition2.Operator = C1.Olap.ConditionOperator.LessThanOrEqualTo;
    filter.Condition2.Parameter = max;
    // restore updates    olap.EndUpdate();
}

As before, the code gets a reference to the C1OlapEngine and immediately calls BeginUpdate.

It then gets a reference to the “UnitPrice” field that will be used for filtering the data. The “UnitPrice” field is added to the engine’s FilterFields collection so the filter will be applied to the current view.

This is an important detail. If a field is not included in any of the view collections (RowFields, ColumnFields, ValueFields, FilterFields), then it is not included in the view at all, and its Filter property does not affect the view in any way.

The code proceeds to configure the Filter property of the “UnitPrice” field by setting two conditions that specify the range of values that should be included in the view. The range is defined by the “min” and “max” parameters. Instead of using conditions, you could provide a list of values that should be included. Conditions are usually more convenient when dealing with numeric values, and lists are better for string values and enumerations.

Finally, the code calls EndUpdate.

One last thing we’ll do is update the C1OlapChart anytime the user sorts a column on the C1OlapGrid. This way the data values appear in the same order.

Private Sub _olapGrid_SortedColumn(sender As Object, e As C1.Silverlight.FlexGrid.CellRangeEventArgs)
    _olapChart.UpdateChart()
End Sub

void _olapGrid_SortedColumn(object sender, C1.Silverlight.FlexGrid.CellRangeEventArgs e)
{
    _olapChart.UpdateChart();
}

The application is now ready. You can run it and test the different views and filtering capabilities of the application, as illustrated below:

This view shows sales for all products, grouped by year and country. Notice how the chart shows values approaching $300,000.

If you click the “$$$ Expensive” button, the filter is applied and the view changes immediately. Notice how now the chart shows values approaching $80,000 instead. Expensive values are responsible for about one third of the sales: