Version

Calculated Measures (xamPivotGrid)

Topic Overview

Purpose

This topic demonstrates, with code examples, how to perform calculations with xamPivotGrid™ members.

Required background

The following table lists the topics required as a prerequisite to understanding this topic.

Topic Purpose

This topic explains how to bind the xamPivotGrid control to data.

Calculated Measures Feature Overview

Calculated Measures feature summary

The Calculated Measures feature offers a way to create a member that can be added to rows or columns, which represent the result of the defined measures. The data that the calculated member represents is cross-joined with the data of other items in the rows or columns to display the calculated result.

The calculated measures give you an option to create your own calculations used for the values that appear in the cells. The calculated measures are added to Measures’ collection.

The xamPivotGrid supports other data sources, but the Calculated Measures feature supports only XMLA data source.

Note
Note:

The member hierarchy, used in measures calculation, cannot be the same as the members already defined in rows or columns.

Creating Calculated Measures – Conceptual Overview

Introduction

The procedure involves creating measures for calculation, and members to display the result from the calculated measures as a separate column.

Note
Note:

Calculated measures can be implemented in C# or VB; they cannot be implemented in XAML at this time.

The implementation of calculated measures is a two-step process. The first step is to create the measures with the calculated expression, and the second step is to create the members associated with the calculated measures and expression.

By default, all calculated measures are attached to the Measures’ dimension, which can be changed by using the 4th and 5th parameters in the constructor of CalculatedMeasureViewModel.

Requirements

An XMLA data source bound to the xamPivotGrid.

Creating Calculated Measures – Code Example

Introduction

The example that follows uses the measures of the InternetSalesAmount column to double its values and display the result in a separate column for year period 2005 – 2008 which was used as an expression for calculation. The member that represents the calculated measures is named Doubled Sales Amount.

Preview

Following is a preview of the final result.

xamPivotGrid CalculatedMeasures 01.png

Requirements

A xamPivotGrid bound to an XMLA data source.

Overview

Following is a conceptual overview of the process:

1. Creating the Calculated Measures

2. Creating the Calculated Members

Steps

  1. Create the Calculated Measures.

    Create a calculated measure that defines the expression of calculation in the Measures hierarchy. In the example here, the Internet Sales Amount in the measures hierarchy is part of the expression, multiplying the Internet Sales Amount by 2.

    In C#:

    ICalculatedItemViewModel calcMeasureViewModel =
        new CalculatedMeasureViewModel(
            "Doubled Amount",
            "Doubled Sales Amount",
            "[Measures].[Internet Sales Amount] * 2");
    this.pivotGrid.DataSource.Measures.Add(calcMeasureViewModel);

    In Visual Basic:

    Dim calcMeasureViewModel As ICalculatedItemViewModel = New CalculatedMeasureViewModel("Doubled Amount", "Doubled Sales Amount", "[Measures].[Internet Sales Amount] * 2")
    Me.pivotGrid.DataSource.Measures.Add(calcMeasureViewModel)
  2. Create the Calculated Members.

    Create a calculated member with the hierarchy to which it is attached.

    In C#:

    IHierarchy dateCalendar = this.pivotGrid.DataSource.Cube.Dimensions["[Date]"].Hierarchies["Calendar"];
    ICalculatedItemViewModel dateCalendarCalcItem = new CalculatedMemberViewModel(
        dateCalendar,
        "2005-2008",
        "Period 2005-2008",
        "[Date].[Calendar].[Calendar Year].[CY 2008] - [Date].[Calendar].[Calendar Year].[CY 2005]",
        false);
    this.pivotGrid.DataSource.Rows.Add(dateCalendarCalcItem);

    In Visual Basic:

    Dim dateCalendar As IHierarchy = Me.pivotGrid.DataSource.Cube.Dimensions("[Date]").Hierarchies("Calendar")
    Dim dateCalendarCalcItem As ICalculatedItemViewModel = New CalculatedMemberViewModel(dateCalendar, "2005-2008", "Period 2005-2008", "[Date].[Calendar].[Calendar Year].[CY 2008] - [Date].[Calendar].[Calendar Year].[CY 2005]", True)
    Me.pivotGrid.DataSource.Rows.Add(dateCalendarCalcItem)
    Note
    Note:

    The hierarchy in the above calculated members is defined for [Date]. [Calendar]. The same hierarchy cannot be defined in the rows or columns. Her are the defined hierarchies for Rows and Columns in this example:

    In XAML:

    <igOlap:XmlaDataSource
         x:Key="DataSource"
         ServerUri="http://sampledata.infragistics.com/olap/msmdpump.dll"
         Database="Adventure Works DW Standard Edition"
         Cube="Adventure Works"
         Columns="[Geography].[City]"
         Rows="[Product].[Product Categories]"
         Measures="Internet Sales Amount" />

Related Content

Topics

The following topics provide additional information related to this topic.

Topic Purpose

This topic explains how to bind the xamPivotGrid control to data.