Configuring Fields in Code

One of the main strengths in OLAP applications is interactivity. Users must be able to create and modify views easily and quickly see the results. OLAP for LightSwitch enables this with its Excel-like user interface and user friendly, simple dialogs.

But in some cases you may want to configure views using code. OLAP for LightSwitch enables this with its simple yet powerful object model, especially the Field and Filter classes.

In the previous topic, you learned how to use the Created method and the ControlAvailable event to ensure proper access to the underlying Silverlight control. Similarly, if you need to configure the field lists that comprise the OLAP view, you should use the Loaded method of the screen’s data source as in the following code example:

C#	Copy Code
partial void SalesData_Loaded(bool succeeded) { if (succeeded) { Microsoft.LightSwitch.Threading.Dispatchers.Main.BeginInvoke(() => { // show sales by country and category var olap = _olapPage.OlapEngine; olap.BeginUpdate(); olap.RowFields.Add("Country"); olap.ColumnFields.Add("Category"); olap.ValueFields.Add("Sales"); olap.Fields["Sales"].Format = "n0"; olap.EndUpdate(); }); } }

Copy Code

partial void SalesData_Loaded(bool succeeded)
{
    if (succeeded)
    {
        Microsoft.LightSwitch.Threading.Dispatchers.Main.BeginInvoke(() =>
        {
            // show sales by country and category
            var olap = _olapPage.OlapEngine;
            olap.BeginUpdate();
            olap.RowFields.Add("Country");
            olap.ColumnFields.Add("Category");
            olap.ValueFields.Add("Sales");
            olap.Fields["Sales"].Format = "n0";
            olap.EndUpdate();
        });
    }
}

The code first checks the succeeded parameter to verify that data is available. If it is, then the remaining statements are executed on the UI thread via BeginInvoke (otherwise, a cross-thread access error will occur). The first call is to the BeginUpdate method, which suspends automatic updates to the output table. Next, it adds fields for the Row, Column and Value field collections so that the user does not have to perform these actions. This code also applies a numeric format to the “Sales” field, and finally calls the EndUpdate method.

Since the OLAP view is configured in code when the screen loads, you could hide the C1OlapPanel portion of the C1OlapPage control to restrict the user from changing the view:

C#	Copy Code
_olapPage.OlapPanel.Visibility = System.Windows.Visibility.Collapsed;

Next, let us use the OLAP forLightSwitch object model to change the format used to display the order dates and extended prices:

C#	Copy Code
// format order date var field = olap.Fields["OrderDate"]; field.Format = "yyyy"; // format extended price and change the Subtotal type // to show the average extended price (instead of sum) field = olap.Fields["Sales"]; field.Format = "c"; field.Subtotal = Subtotal.Average;

Copy Code

// format order date
var field = olap.Fields["OrderDate"];
field.Format = "yyyy";

// format extended price and change the Subtotal type
// to show the average extended price (instead of sum)
field = olap.Fields["Sales"];
field.Format = "c";
field.Subtotal = Subtotal.Average;

This code retrieves individual fields from the Fields collection, which contains all of the fields specified in the data source. Then it assigns the desired values to the Format and Subtotal properties. Format takes a regular .NET format string, and Subtotal determines how values are aggregated for display in the OLAP view. By default, values are added, but many other aggregate statistics are available including average, maximum, minimum, standard deviation, and variance.

Suppose that you are only interested in a subset of the data, say a few products and one year. At runtime, the user could right-click the fields and apply filters to them. However, you can do the exact same thing in code as follows:

C#	Copy Code
// apply value filter to show only a few products C1OlapFilter filter = olap.Fields["Product"].Filter; filter.Clear(); filter.ShowValues = "Chai,Chang,Geitost,Ikura".Split(','); // apply condition filter to show only some dates filter = olap.Fields["OrderDate"].Filter; filter.Clear(); filter.Condition1.Operator = ConditionOperator.GreaterThanOrEqualTo; filter.Condition1.Parameter = new DateTime(1996, 1, 1); filter.Condition2.Operator = ConditionOperator.LessThanOrEqualTo; filter.Condition2.Parameter = new DateTime(1996, 12, 31); filter.AndConditions = true;

Copy Code

// apply value filter to show only a few products
C1OlapFilter filter = olap.Fields["Product"].Filter;
filter.Clear();
filter.ShowValues = "Chai,Chang,Geitost,Ikura".Split(',');

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

This code first retrieves the C1OlapFilter object that is associated with the “Product” field. Then it clears the filter and sets its ShowValues property. This property takes an array of values that should be shown by the filter. In OLAP for LightSwitch we call this a “value filter”. Next, the code retrieves the filter associated with the “OrderDate” field. This time, we want to show values for a specific year. But we don’t want to enumerate all days in the target year. Instead, we use a “condition filter” which is defined by two conditions. The first condition specifies that the “OrderDate” should be greater than or equal to January 1st, 1996. The second condition specifies that the “OrderDate” should be less than or equal to December 31st, 1996. The AndConditions property specifies how the first and second conditions should be applied (AND or OR operators). In this case, we want dates where both conditions are true, so AndConditions is set to true.