Category / Section
Value filtering for olap report on code behind
1 min read
This KB document explains that How to do the value filtering on code-behind.
Solution
The Olap report can be filtered based on measure values. Please follow the below steps.
Step 1
In the “InitializeGrid” method of WebAPI/WCF services, form a dictionary with filtering details and assign the same to Tag of OlapReport.
C#
public Dictionary<string, object> InitializeOlapGrid(Dictionary<string, object> jsonResult) { //…. Dictionary<string, object> filterParams = new Dictionary<string, object>(); List<Dictionary<string, object>> filterList = new List<Dictionary<string, object>>(); Dictionary<string, List<Dictionary<string, object>>> filterDictionary = new Dictionary<string, List<Dictionary<string, object>>>(); Item item = null; string levelUniqueName = "[Cost Centres].[Cost Centre Name].[Cost Centre Name]"; //Assign the level unique name of dimension element which you want to filter string axis = ""; var reportItems = DataManager.CurrentReport.CategoricalElements.List.Union(DataManager.CurrentReport.SeriesElements.List).Where(m => m.ElementValue is DimensionElement); foreach (var reportItem in reportItems) { item = GetReportItem(levelUniqueName, reportItem); if (item != null) break; } string currentKey = "ValueFilters";//It is a key which is used to filter the element based on measure values if (DataManager.CurrentReport.Tag == null) filterDictionary.Add(currentKey, filterList); // Add the following details to filter the elements through code behind filterParams.Add("Axis", "COLUMNS"); filterParams.Add("HierarchyUniqueName", ((item.ElementValue as Element) as DimensionElement).Hierarchy.UniqueName); filterParams.Add("LevelUniqueName", levelUniqueName); filterParams.Add("Operator", "between"); // You can use the operator here (equals, does not equals, between, not between, greater than, greater than or equal to, less than, less than or equal to) filterParams.Add("Value", "18.4,18.45"); filterParams.Add("LevelDetails", "[Cost Centres].[Cost Centre Name].[Cost Centre Name]", levelUniqueName, ref axis, false, "")); //Add the exact level detail here filterParams.Add("MeasureUniqueName", "[Measures].[Average Employment]"); filterDictionary[currentKey].Insert(0, filterParams); DataManager.CurrentReport.Tag = filterDictionary; //Assign the dictionary to Tag of Olap report //…. return temp; }