Category / Section
Value filtering for olap report on code behind
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;
}
