Category / Section
How to filter the members in the dimension using OlapReport in JavaScript PivotChart?
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)