How to: Sort Data by Individual Columns (Rows) in OLAP Mode
4 minutes to read
The following example demonstrates how to implement Sorting by Summary in OLAP mode.
In this example, values of the Semester field are sorted by the Australia | Bendigo column summary values. To do this, two sort conditions represented by PivotGridFieldSortCondition instances are created. One of them represents an OLAP member that corresponds to the ‘Australia’ value, while another corresponds to the ‘Bendigo’ value. These sort conditions are added to the Semester field’s PivotGridFieldSortBySummaryInfo.Conditions collection to specify the column by which Semester values should be sorted. A data field that identifies the column is specified via the PivotGridFieldSortBySummaryInfo.Field property.
OLAP members corresponding to the Australia and Bendigo values are obtained using the ASPxPivotGrid.GetFieldValueOLAPMember method. Note that OLAP members can be obtained only for visible field values. For this reason, the Australia field value is expanded before initializing OLAP members in order to obtain the Bendigo OLAP member.
<%@PageLanguage="C#"AutoEventWireup="true"CodeBehind="Default.aspx.cs"Inherits="ASPxPivotGrid_OLAPSortBySummary._Default" %><%@RegisterAssembly="DevExpress.Web.ASPxPivotGrid.v14.1, Version=14.1.7.0,
Culture=neutral, PublicKeyToken=b88d1754d700e49a"Namespace="DevExpress.Web.ASPxPivotGrid"TagPrefix="dx" %><!DOCTYPE htmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><htmlxmlns="http://www.w3.org/1999/xhtml" ><headrunat="server"><title>Untitled Page</title></head><body><formid="form1"runat="server"><div><dx:ASPxPivotGridID="ASPxPivotGrid1"runat="server"DataSourceID="AccessDataSource1"OnLoad="ASPxPivotGrid1_Load"><Fields><dx:PivotGridFieldID="fieldMonth"Area="RowArea"AreaIndex="0"Caption="Month"FieldName="[Date].[Month of Year].[Month of Year]"></dx:PivotGridField><dx:PivotGridFieldID="fieldCountry"Area="ColumnArea"AreaIndex="0"Caption="Country"FieldName="[Customer].[Country].[Country]"></dx:PivotGridField><dx:PivotGridFieldID="fieldCity"Area="ColumnArea"AreaIndex="1"Caption="City"FieldName="[Customer].[City].[City]"></dx:PivotGridField><dx:PivotGridFieldID="fieldSales"Area="DataArea"AreaIndex="0"Caption="Sales"FieldName="[Measures].[Internet Sales Amount]"></dx:PivotGridField></Fields></dx:ASPxPivotGrid></div></form></body></html>
usingSystem;
usingSystem.Web.UI;
usingDevExpress.XtraPivotGrid;
namespaceASPxPivotGrid_OLAPSortBySummary {
publicpartialclass _Default : Page {
protectedoverridevoidOnInit(EventArgs e) {
base.OnInit(e);
ASPxPivotGrid1.OLAPConnectionString = "provider=MSOLAP;" +
"data source=https://demos.devexpress.com/Services/OLAP/msmdpump.dll;" +
"initial catalog='Adventure Works DW Standard Edition';cube name='Adventure Works'";
}
protectedvoidASPxPivotGrid1_Load(object sender, EventArgs e) {
if (IsCallback || IsPostBack) return;
// Expands the Australia column to be able to retrieve OLAP members // that correspond to the nested columns.
ASPxPivotGrid1.ExpandValue(true, newobject[] { "Australia" });
// Obtains OLAP members corresponding to the Australia and Bendigo values.
IOLAPMember countryMember = ASPxPivotGrid1.GetFieldValueOLAPMember(fieldCountry, 0);
IOLAPMember cityMember = ASPxPivotGrid1.GetFieldValueOLAPMember(fieldCity, 0);
// Exits if the OLAP members were not obtained successfully.if (countryMember == null || cityMember == null)
return;
// Specifies a data field whose summary values should be used to sort values// of the Month field.
fieldMonth.SortBySummaryInfo.Field = fieldSales;
// Specifies a column by which the Month field values should be sorted.
fieldMonth.SortBySummaryInfo.Conditions.Clear();
fieldMonth.SortBySummaryInfo.Conditions.Add(
new PivotGridFieldSortCondition(fieldCountry, "Australia", countryMember.UniqueName));
fieldMonth.SortBySummaryInfo.Conditions.Add(
new PivotGridFieldSortCondition(fieldCity, "Bendigo", cityMember.UniqueName));
}
}
}
<%@PageLanguage="vb"AutoEventWireup="true"CodeBehind="Default.aspx.vb"Inherits="ASPxPivotGrid_OLAPSortBySummary._Default" %><%@RegisterAssembly="DevExpress.Web.ASPxPivotGrid.v14.1, Version=14.1.7.0,
Culture=neutral, PublicKeyToken=b88d1754d700e49a"Namespace="DevExpress.Web.ASPxPivotGrid"TagPrefix="dx" %><!DOCTYPE htmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><htmlxmlns="http://www.w3.org/1999/xhtml" ><headrunat="server"><title>Untitled Page</title></head><body><formid="form1"runat="server"><div><dx:ASPxPivotGridID="ASPxPivotGrid1"runat="server"DataSourceID="AccessDataSource1"OnLoad="ASPxPivotGrid1_Load"><Fields><dx:PivotGridFieldID="fieldMonth"Area="RowArea"AreaIndex="0"Caption="Month"FieldName="[Date].[Month of Year].[Month of Year]"></dx:PivotGridField><dx:PivotGridFieldID="fieldCountry"Area="ColumnArea"AreaIndex="0"Caption="Country"FieldName="[Customer].[Country].[Country]"></dx:PivotGridField><dx:PivotGridFieldID="fieldCity"Area="ColumnArea"AreaIndex="1"Caption="City"FieldName="[Customer].[City].[City]"></dx:PivotGridField><dx:PivotGridFieldID="fieldSales"Area="DataArea"AreaIndex="0"Caption="Sales"FieldName="[Measures].[Internet Sales Amount]"></dx:PivotGridField></Fields></dx:ASPxPivotGrid></div></form></body></html>
ImportsSystemImportsSystem.Web.UIImportsDevExpress.XtraPivotGridNamespace ASPxPivotGrid_OLAPSortBySummary
PartialPublicClass _Default
Inherits Page
ProtectedOverridesSub OnInit(ByVal e As EventArgs)
MyBase.OnInit(e)
ASPxPivotGrid1.OLAPConnectionString = "provider=MSOLAP;" &
"data source=https://demos.devexpress.com/Services/OLAP/msmdpump.dll;" &
"initial catalog='Adventure Works DW Standard Edition';" &
"cube name='Adventure Works'"EndSubProtectedSub ASPxPivotGrid1_Load(ByVal sender AsObject, ByVal e As EventArgs)
If IsCallback OrElse IsPostBack ThenReturnEndIf' Expands the Australia column to be able to retrieve OLAP members ' that correspond to the nested columns.
ASPxPivotGrid1.ExpandValue(True, NewObject() { "Australia" })
' Obtains OLAP members corresponding to the Australia and Bendigo values.Dim countryMember As IOLAPMember = ASPxPivotGrid1.GetFieldValueOLAPMember(fieldCountry, 0)
Dim cityMember As IOLAPMember = ASPxPivotGrid1.GetFieldValueOLAPMember(fieldCity, 0)
' Exits if the OLAP members were not obtained successfully.If countryMember IsNothingOrElse cityMember IsNothingThenReturnEndIf' Specifies a data field whose summary values should be used to sort values' of the Month field.
fieldMonth.SortBySummaryInfo.Field = fieldSales
' Specifies a column by which the Month field values should be sorted.
fieldMonth.SortBySummaryInfo.Conditions.Clear()
fieldMonth.SortBySummaryInfo.Conditions.Add(New PivotGridFieldSortCondition(fieldCountry, _
"Australia", countryMember.UniqueName))
fieldMonth.SortBySummaryInfo.Conditions.Add(New PivotGridFieldSortCondition(fieldCity, _
"Bendigo", cityMember.UniqueName))
EndSubEndClassEndNamespace
Was this page helpful?
Thanks for your feedback!
How can we improve this help topic?
Additional comments/thoughts:
If you have any questions, submit a ticket to our Support Center.