Category / Section
How to filter the members in the dimension using OlapReport in JavaScript PivotGrid?
2 mins read
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:
- 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.
- 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)
Conclusion
I hope you enjoyed learning about how to filter the members in the dimension using OlapReport in JavaScript PivotGrid.
You can refer to our JavaScript PivotGrid feature tour page to know about its other groundbreaking feature representations and documentation, and how to quickly get started for configuration specifications. You can also explore our JavaScript PivotGrid example to understand how to create and manipulate data.
For current customers, you can check out our components from the License and Downloads page. If you are new to Syncfusion, you can try our 30-day free trial to check out our other controls.
If you have any queries or require clarifications, please let us know in the comments section below. You can also contact us through our support forums, Direct-Trac, or feedback portal. We are always happy to assist you!
