We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy.
Unfortunately, activation email could not send to your email. Please try again.

Dynamic filtering Olap data

Thread ID:

Created:

Updated:

Platform:

Replies:

127520 Nov 21,2016 07:11 AM Nov 25,2016 06:18 AM JavaScript 7
loading
Tags: ejPivotClient
B Nielander
Asked On November 21, 2016 07:11 AM

Hello,
I have a html page with multiple widgets (a pivotgrid and pivotcharts). Olap data is retrieved through a Web Api REST service. 
A list is shown to users with values to filter. I want a user to be able to filter all widgets upon changing the value selection. 
The samples I have found sofar are all related to relational data. Can someone provide me with a sample to apply a filter dynamically on Olap based pivot widgets?

Sastha Prathap Selvamoorthy [Syncfusion]
Replied On November 22, 2016 06:59 AM

Hi Nielander,   
   
Thank you for contacting Syncfusion support.   
   
The PivotClient control includes both PivotGrid and PivotChart widgets which works dynamically with same Olap report. You can perform many operations like filtering, sorting, toggle axis, dynamically adding/ removing/ changing report, ChartTypes, DeferUpdate, etc.., in PivotClient which will reflects on both the PivotGrid and PivotChart widgets. Please find the below online sample for your reference.   
   
   
Installed sample location: <System drive>:\Users\<user name>\AppData\Local\Syncfusion\EssentialStudio\14.X.X.XX\JavaScript\samples\web\pivotgrid\olapwebapi.html   
Web API: <System drive>:\Users\<user name>\AppData\Local\Syncfusion\EssentialStudio\14.X.X.XX\JavaScript\ejservices\Controllers\OlapGridController.cs   
   
Also, please find the below user guide document on creating a sample for PivotClient using WebAPI.    
  
  
   
Regards,   
Sastha Prathap S.   


B Nielander
Replied On November 22, 2016 09:20 AM

Hello,

I have checked all the samples you provided, but none of them covers the scenario I described.
We already succesfully implemented a Web Api REST service and a webpage with a ejPivotGrid and a ejPivotChart widget, showing data from an Olap Cube. The attachment shows the basic structure of the webpage.
Now we need to implement the functionality of the dropdown list where a user can select a filter value. When a value is selected, both the PivotGrid and the PivotChart needs to be filtered for the specified value. So, it's a dynamic filter.
How can we do this?

Regards,
Bas

Attachment: syncfusion__olap_filter_example_f520ac75.zip

Sastha Prathap Selvamoorthy [Syncfusion]
Replied On November 23, 2016 10:59 AM

Hi Nielander, 

Your requirement can be achieved by filtering the particular level members using Slicer elements in the OlapReport. Please find the sample code snippet that can be used to achieve the same by passing the “customObject” parameter.  


Example Code for Sample level: 
function applyFilter(args) { 
    var PivotGridObj = $("#PivotGrid1").data("ejPivotGrid"); 
    var PivotChartObj = $("#PivotChart1").data("ejPivotChart"); 
    var levelUniqueName = args.selectedValue; // specify the selected level UniqueName 
    var customObj = JSON.stringify(levelUniqueName); 
    // Render PivotGrid 
    PivotGridObj.doAjaxPost("POST", "/OlapService/InitializeGrid", JSON.stringify({ "action": "initializeGrid", "gridLayout": PivotGridObj.layout(), "enablePivotFieldList": PivotGridObj.model.enablePivotFieldList, "customObject": customObj }), PivotGridObj._renderControlSuccess); 
    // Render PivotChart 
    PivotChartObj.doAjaxPost("POST", "/OlapService/InitializeChart", JSON.stringify({ "action": "initialize", "currentReport": PivotChartObj.model.currentReport, "customObject": customObj }), PivotChartObj.renderControlSuccess); 
} 
 
Example Code for Server side: 
    public class OlapServiceController : ApiController 
    { 
        Syncfusion.JavaScript.PivotGrid htmlHelper = new Syncfusion.JavaScript.PivotGrid(); 
        Syncfusion.JavaScript.PivotChart pivotChart = new Syncfusion.JavaScript.PivotChart(); 
        string filteredReport = null; 
        string connectionString = "Data Source=http://bi.syncfusion.com/olap/msmdpump.dll; Initial Catalog=Adventure Works DW 2008 SE;"; 
        JavaScriptSerializer serializer = new JavaScriptSerializer(); 
        string conStringforDB = ""; //Enter appropriate connection string to connect database for saving and loading operation of reports 
 
        [System.Web.Http.ActionName("InitializeGrid")] 
        [System.Web.Http.HttpPost] 
       public Dictionary<string, object> InitializeOlapGrid(Dictionary<string, object> jsonResult) 
        { 
            OlapDataManager DataManager = null; 
            dynamic customData = serializer.Deserialize<dynamic>(jsonResult["customObject"].ToString()); 
            if (customData as string != null) 
                filteredReport = customData; 
            DataManager = new OlapDataManager(connectionString); 
            DataManager.SetCurrentReport(CreateOlapReport()); 
            DataManager.OverrideDefaultFormatStrings = true; 
            return htmlHelper.GetJsonData(jsonResult["action"].ToString(), DataManager, jsonResult.ContainsKey("gridLayout") ? jsonResult["gridLayout"].ToString() : null, Convert.ToBoolean(jsonResult["enablePivotFieldList"].ToString())); 
        } 
 
        [System.Web.Http.ActionName("InitializeChart")] 
        [System.Web.Http.HttpPost] 
        public Dictionary<string, object> InitializeChart(Dictionary<string, object> jsonResult) 
        { 
            dynamic customData1 = serializer.Deserialize<dynamic>(jsonResult["customObject"].ToString()); 
            if (customData1 as string != null) 
                filteredReport = customData1; 
            OlapDataManager DataManager = new OlapDataManager(connectionString); 
            DataManager.SetCurrentReport(CreateOlapReport()); 
            return pivotChart.GetJsonData(jsonResult["action"].ToString(), DataManager); 
        } 
 
        // 
        // 
 
       private OlapReport CreateOlapReport() 
        { 
            OlapReport olapReport = new OlapReport(); 
            olapReport.CurrentCubeName = "Adventure Works"; 
 
            MeasureElements measureElement = new MeasureElements(); 
            measureElement.Elements.Add(new MeasureElement { UniqueName = "[Measures].[Internet Sales Amount]" }); 
 
            DimensionElement dimensionElementRow = new DimensionElement(); 
            dimensionElementRow.Name = "Date"; 
            dimensionElementRow.AddLevel("Fiscal", "Fiscal Year"); 
 
            DimensionElement dimensionElementSlicer = new DimensionElement(); 
            dimensionElementSlicer.Name = "Customer"; 
            dimensionElementSlicer.AddLevel("Customer Geography", "Country"); 
            if (filteredReport as string != null) 
            dimensionElementSlicer.Hierarchy.LevelElements["Country"].Add(new MemberElement() { UniqueName = filteredReport }); 
 
 
            olapReport.SeriesElements.Add(dimensionElementRow); 
            olapReport.SlicerElements.Add(dimensionElementSlicer); 
            olapReport.CategoricalElements.Add(measureElement); 
 
            return olapReport; 
        } 
    } 
} 
 
Also, We have prepared a sample for your reference. Please find the sample in the below link. 


Regards, 
Sastha Prathap S. 


B Nielander
Replied On November 23, 2016 12:13 PM

Thanks for the sample. This helped me a lot.
Now I would like to enable the grouping bar for the ejPivotGrid, but when I do the filtered member is shown (in the sample "Customer Geography) " and users can change the filter. 

How can I hide the filter option? (in case of the sample hide the filter for the "Customer Geography" member, see attachment) 


With kind regards,

Bas

Attachment: syncfusion__olap_filter_example_2_20671a08.zip

Sastha Prathap Selvamoorthy [Syncfusion]
Replied On November 23, 2016 11:29 PM

Hi Nielander, 
 
Thanks for the response. 
  
The filter option available in the Grouping bar can be made hidden by using CSS property in sample level itself.  Please find the sample code snippet for your reference below. 
 
Code for hiding filter option for Slicer elements: 
<style> 
    #groupingBarPivot>.drag>.pivotButton>.filter{ 
        display: none; 
    } 
</style> 
 
Please let us know if you need any further assistance. 
  
Regards, 
Sastha Prathap S. 


B Nielander
Replied On November 24, 2016 03:49 AM

Thanks, your script for hiding the filter works fine. 
I have noticed that when the Customer Geography member is dragged to the rows or columns section the filter is reset. In your example, all countries are shown instead of only the filtered country.
How can this behavior be excluded? After dragging the filtered member the filter should stay applicable.


With kind regards,

Bas

Attachment: syncfusion__olap_filter_example_3_ccd27f0b.zip

Sastha Prathap Selvamoorthy [Syncfusion]
Replied On November 25, 2016 06:18 AM

Hi Nielander, 
 
Sorry for the inconvenience. 
The reported problem can be rectified by adding the required element in included members along with adding the rest of the members in excluded list in the OlapReport.  Please find the below code snippet to achieve the same.  
 
Example Code for Sample level: 
function applyFilter(args) { 
    var PivotGridObj = $("#PivotGrid1").data("ejPivotGrid"); 
    var PivotChartObj = $("#PivotChart1").data("ejPivotChart"); 
 
    var includelevelUniqueName = args.selectedValue; //filter element that you selected from the dropdownlist 
    var excludelevelUniqueName = []; //filter elements that are exclude from the dropdownlist 
 
    if (!ej.isNullOrUndefined($('#drpdwn').data("ejDropDownList"))) 
        $.each($('#drpdwn').data("ejDropDownList")._rawList, function (e, item) { if (item.value.length > 0 && item.value != includelevelUniqueName) excludelevelUniqueName.push(item.value) }); 
    var customObj = { "includedElements": includelevelUniqueName, "excludedElements": excludelevelUniqueName } 
    var serializedcustomObj = JSON.stringify(customObj); 
 
    // Render PivotGrid 
    PivotGridObj.doAjaxPost("POST", "/OlapService/InitializeGrid", JSON.stringify({ "action": "initializeGrid", "gridLayout": PivotGridObj.layout(), "enablePivotFieldList": PivotGridObj.model.enablePivotFieldList, "customObject": serializedcustomObj }), PivotGridObj._renderControlSuccess); 
    // Render PivotChart 
    PivotChartObj.doAjaxPost("POST", "/OlapService/InitializeChart", JSON.stringify({ "action": "initialize", "currentReport": PivotChartObj.model.currentReport, "customObject": serializedcustomObj }), PivotChartObj.renderControlSuccess); 
} 
 
Example Code for Server side: 
   public class OlapServiceController : ApiController 
    { 
        Syncfusion.JavaScript.PivotGrid htmlHelper = new Syncfusion.JavaScript.PivotGrid(); 
        Syncfusion.JavaScript.PivotChart pivotChart = new Syncfusion.JavaScript.PivotChart(); 
        string includedElements = null; 
        object excludedElements = null; 
        //string connectionString = "Data Source=http://bi.syncfusion.com/olap/msmdpump.dll; Initial Catalog=Adventure Works DW 2008 SE;"; 
        string connectionString = @"Data Source=SYNCLAPN7420; Initial Catalog=Adventure Works DW;locale identifier=1033;"; 
        JavaScriptSerializer serializer = new JavaScriptSerializer(); 
        string conStringforDB = ""; //Enter appropriate connection string to connect database for saving and loading operation of reports 
 
        [System.Web.Http.ActionName("InitializeGrid")] 
        [System.Web.Http.HttpPost] 
        public Dictionary<string, object> InitializeOlapGrid(Dictionary<string, object> jsonResult) 
        { 
            OlapDataManager DataManager = null; 
            dynamic customData = serializer.Deserialize<dynamic>(jsonResult["customObject"].ToString()); 
            if (customData is Dictionary<string, object> && customData.ContainsKey("includedElements")) 
            { 
                includedElements = customData["includedElements"]; 
                excludedElements = customData["excludedElements"]; 
            } 
            DataManager = new OlapDataManager(connectionString); 
            DataManager.SetCurrentReport(CreateOlapReport()); 
            DataManager.OverrideDefaultFormatStrings = true; 
            return htmlHelper.GetJsonData(jsonResult["action"].ToString(), DataManager, jsonResult.ContainsKey("gridLayout") ? jsonResult["gridLayout"].ToString() : null, Convert.ToBoolean(jsonResult["enablePivotFieldList"].ToString())); 
        } 
 
        [System.Web.Http.ActionName("InitializeChart")] 
        [System.Web.Http.HttpPost] 
        public Dictionary<string, object> InitializeChart(Dictionary<string, object> jsonResult) 
        { 
            dynamic customData1 = serializer.Deserialize<dynamic>(jsonResult["customObject"].ToString()); 
            if (customData1 is Dictionary<string, object> && customData1.ContainsKey("includedElements")) 
            { 
                includedElements = customData1["includedElements"]; 
                excludedElements = customData1["excludedElements"]; 
            } 
            OlapDataManager DataManager = new OlapDataManager(connectionString); 
            DataManager.SetCurrentReport(CreateOlapReport()); 
            return pivotChart.GetJsonData(jsonResult["action"].ToString(), DataManager); 
        } 
 
        private OlapReport CreateOlapReport() 
        { 
            OlapReport olapReport = new OlapReport(); 
            olapReport.CurrentCubeName = "Adventure Works"; 
 
            MeasureElements measureElement = new MeasureElements(); 
            measureElement.Elements.Add(new MeasureElement { UniqueName = "[Measures].[Internet Sales Amount]" }); 
 
            DimensionElement dimensionElementRow = new DimensionElement(); 
            dimensionElementRow.Name = "Date"; 
            dimensionElementRow.AddLevel("Fiscal", "Fiscal Year"); 
 
            DimensionElement dimensionElementSlicer = new DimensionElement(); 
            dimensionElementSlicer.Name = "Customer"; 
            dimensionElementSlicer.AddLevel("Customer Geography", "Country"); 
            if (includedElements as string != null) 
                dimensionElementSlicer.Hierarchy.LevelElements["Country"].Add(new MemberElement() { UniqueName = includedElements }); 
 
            //Spefifying the Excluded row elements 
            DimensionElement excludedSlicerElement = new DimensionElement(); 
            excludedSlicerElement.Name = "Customer"; 
            excludedSlicerElement.AddLevel("Customer Geography", "Country"); 
            if (excludedElements != null) 
            { 
                IEnumerable<object> collection = (IEnumerable<object>)excludedElements; 
                foreach (object item in collection) 
                { 
                    excludedSlicerElement.Hierarchy.LevelElements["Country"].Add(new MemberElement() { UniqueName = item.ToString() }); 
                } 
            } 
            olapReport.SeriesElements.Add(dimensionElementRow); 
            olapReport.SlicerElements.Add(dimensionElementSlicer, excludedSlicerElement); 
            olapReport.CategoricalElements.Add(measureElement); 
 
            return olapReport; 
        } 
    } 
} 
 
Also, we have prepared a sample for your reference. Please find the sample in the below link. 
 
 
Regards, 
Sastha Prathap S. 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.

;