Dynamic filtering Olap data

[B Nielander]

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]

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.   

   

PivotClient: http://js.syncfusion.com/demos/web/#!/azure/pivotclient/olap/webapi   

   

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.    

  

Online document: https://help.syncfusion.com/js/pivotclient/olap-getting-started#creating-a-simple-application-with-pivotclient-and-olap-datasource-server-mode   

  

More about PivotClient: https://help.syncfusion.com/js/pivotclient/olap-client-elements   

   

Regards,   

Sastha Prathap S.   

[B Nielander]

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]

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.  

[B Nielander]

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]

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]

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]

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. 

 

Sample Link: http://www.syncfusion.com/downloads/support/directtrac/168036/ze/PivotControls-1296893815 

 

Regards, 

Sastha Prathap S.