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