We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on: November 16, 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

How to filter the members in the dimension using OlapReport?

Platform: JavaScript |
Control: ejPivotChart |
Published Date: March 7, 2016 |
Last Revised Date: June 17, 2019

This KB illustrates that how to filter the members in the dimension using OlapReport.

Solution:

You can filter the members in the dimension using OlapReport by the following appropriate rules and description with code snippets.

Rules:

  1. To achieve this, we need to specify “Excluded Elements” and “Included Elements” in OlapReport. If the dimension is in Row/Column region, it is enough to specify “Excluded Elements” alone and for slicer region we need to specify both “Excluded elements” and “Included elements” in OlapReport.
  2. Also, we need to specify level name, member name and unique name of the desired members in-order to exclude or include.

 

The code sample below illustrates on how to filter the members in a dimension which is in column. NOTE: This format is applicable for row as well but not for slicer.

C#

OLAP REPORT:
 
DimensionElement dimensionElementColumn = new DimensionElement();
dimensionElementColumn.Name = "Customer";
dimensionElementColumn.HierarchyName = "Customer Geography";
dimensionElementColumn.AddLevel("Customer Geography", "Country");
 
//Specifying the excluded members in column.
DimensionElement excludedColumnElement = new DimensionElement();
excludedColumnElement.Name = "Customer";
excludedColumnElement.HierarchyName = "Customer Geography";
excludedColumnElement.AddLevel("Customer Geography", "Country");
 
excludedColumnElement.Hierarchy.LevelElements["Country"].Add(new MemberElement { Name = "Australia", UniqueName = "[Customer].[Customer Geography].[Country].&[Australia]" });
excludedColumnElement.Hierarchy.LevelElements["Country"].Add(new MemberElement { Name = "France", UniqueName = "[Customer].[Customer Geography].[Country].&[France]" });
 
olapReport.CategoricalElements.Add(dimensionElementColumn, excludedColumnElement);
 

 

VB.NET

OLAP REPORT:
 
Dim dimensionElementColumn As New DimensionElement()
dimensionElementColumn.Name = "Customer"
dimensionElementColumn.HierarchyName = "Customer Geography"
dimensionElementColumn.AddLevel("Customer Geography", "Country")
 
'Specifying the excluded members in column.
Dim excludedColumnElement As New DimensionElement()
excludedColumnElement.Name = "Customer"
excludedColumnElement.HierarchyName = "Customer Geography"
excludedColumnElement.AddLevel("Customer Geography", "Country")
 
excludedColumnElement.Hierarchy.LevelElements("Country").Add(New MemberElement() With { _
Key .Name = "Australia", _
Key .UniqueName = "[Customer].[Customer Geography].[Country].&[Australia]" _
})
excludedColumnElement.Hierarchy.LevelElements("Country").Add(New MemberElement() With { _
Key .Name = "France", _
Key .UniqueName = "[Customer].[Customer Geography].[Country].&[France]" _
})
 
olapReport.CategoricalElements.Add(dimensionElementColumn, excludedColumnElement)
 

 

The code sample below illustrates on how to filter the members in a dimension which is in slicer.

C#

OLAP REPORT:
 
DimensionElement dimensionElementSlicer = new DimensionElement();
dimensionElementSlicer.Name = "Date";
dimensionElementSlicer.HierarchyName = "Fiscal";
dimensionElementSlicer.AddLevel("Fiscal", "Fiscal Year");
 
//Specifying the included members in slicer.
dimensionElementSlicer.Hierarchy.LevelElements[0].MemberElements.Add(new MemberElement() { Name = "FY 2002", UniqueName = "[Date].[Fiscal].[Fiscal Year].&[2002]" });
dimensionElementSlicer.Hierarchy.LevelElements[0].MemberElements.Add(new MemberElement() { Name = "FY 2003", UniqueName = "[Date].[Fiscal].[Fiscal Year].&[2003]" });
dimensionElementSlicer.Hierarchy.LevelElements[0].MemberElements[0].ChildMemberElements.Add(new MemberElement() { Name = "H1 FY 2002", UniqueName = "[Date].[Fiscal].[Fiscal Semester].&[2002]&[1]" });
 
//Specifying the excluded members in slicer.
DimensionElement excludedSlicerElement = new DimensionElement();
excludedSlicerElement.Name = "Date";
excludedSlicerElement.AddLevel("Fiscal", "Fiscal Year");
excludedSlicerElement.AddLevel("Fiscal", "Fiscal Semester");
 
excludedSlicerElement.Hierarchy.LevelElements["Fiscal Year"].Add(new MemberElement { Name = "FY 2004", UniqueName = "[Date].[Fiscal].[Fiscal Year].&[2004]" });
excludedSlicerElement.Hierarchy.LevelElements["Fiscal Year"].Add(new MemberElement { Name = "FY 2005", UniqueName = "[Date].[Fiscal].[Fiscal Year].&[2005]" });
excludedSlicerElement.Hierarchy.LevelElements["Fiscal Semester"].Add(new MemberElement { Name = "H2 FY 2002", UniqueName = "[Date].[Fiscal].[Fiscal Semester].&[2002]&[2]" });
 
olapReport.SlicerElements.Add(dimensionElementSlicer, excludedSlicerElement);
 

 

 

VB.NET

OLAP REPORT:
 
Dim dimensionElementSlicer As New DimensionElement()
dimensionElementSlicer.Name = "Date"
dimensionElementSlicer.HierarchyName = "Fiscal"
dimensionElementSlicer.AddLevel("Fiscal", "Fiscal Year")
 
'Specifying the included members in slicer.
dimensionElementSlicer.Hierarchy.LevelElements(0).MemberElements.Add(New MemberElement() With { _
 Key .Name = "FY 2002", _
 Key .UniqueName = "[Date].[Fiscal].[Fiscal Year].&[2002]" _
})
dimensionElementSlicer.Hierarchy.LevelElements(0).MemberElements.Add(New MemberElement() With { _
 Key .Name = "FY 2003", _
 Key .UniqueName = "[Date].[Fiscal].[Fiscal Year].&[2003]" _
})
dimensionElementSlicer.Hierarchy.LevelElements(0).MemberElements(0).ChildMemberElements.Add(New MemberElement() With { _
 Key .Name = "H1 FY 2002", _
 Key .UniqueName = "[Date].[Fiscal].[Fiscal Semester].&[2002]&[1]" _
})
 
'Specifying the excluded members in slicer.
Dim excludedSlicerElement As New DimensionElement()
excludedSlicerElement.Name = "Date"
excludedSlicerElement.AddLevel("Fiscal", "Fiscal Year")
excludedSlicerElement.AddLevel("Fiscal", "Fiscal Semester")
 
excludedSlicerElement.Hierarchy.LevelElements("Fiscal Year").Add(New MemberElement() With { _
 Key .Name = "FY 2004", _
 Key .UniqueName = "[Date].[Fiscal].[Fiscal Year].&[2004]" _
})
excludedSlicerElement.Hierarchy.LevelElements("Fiscal Year").Add(New MemberElement() With { _
 Key .Name = "FY 2005", _
 Key .UniqueName = "[Date].[Fiscal].[Fiscal Year].&[2005]" _
})
excludedSlicerElement.Hierarchy.LevelElements("Fiscal Semester").Add(New MemberElement() With { _
 Key .Name = "H2 FY 2002", _
 Key .UniqueName = "[Date].[Fiscal].[Fiscal Semester].&[2002]&[2]" _
})
 
olapReport.SlicerElements.Add(dimensionElementSlicer, excludedSlicerElement)
 

 

 

ADD COMMENT
You must log in to leave a comment

Please sign in to access our KB

This page will automatically be redirected to the sign-in page in 10 seconds.

Up arrow icon

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon