Data Binding to Local OLAP Cube

[Nate Greene]

I have been looking at the documentation and have not been able to connect the pivot grid to a locally connected SSAS Cube. In the past I have used a web connection through msmdpump.dll this is not useful in this situation. Do you have a small example where the pivotgrid datasource is a local ssas cube using a connection string rather than the msmdpump.dll route?

Thanks,

Nate

[Nate Greene]

I have figured out how to bind to the local cube without a problem but I am having difficulty narrowing the range of elements being displayed in the grid. Currently I have created the OlapReport that is displayed on the grid and it looks like this:

 private OlapReport CreateOlapReport()

        {

            OlapReport olapReport = new OlapReport() { Name = "Default Report" };

            olapReport.CurrentCubeName = "Blue Streak Sales";

            MeasureElements measureElement = new MeasureElements();

            measureElement.Elements.Add(new MeasureElement { UniqueName = "[Measures].[Amount]" });

            DimensionElement dimensionElementRow = new DimensionElement();

            dimensionElementRow.Name = "Year";

            dimensionElementRow.AddLevel("Date", "Year");

            DimensionElement dimensionElementColumn = new DimensionElement();

            dimensionElementColumn.Name = "Stores";

            dimensionElementColumn.AddLevel("Stores", "Company");

            olapReport.SeriesElements.Add(dimensionElementRow);

            olapReport.CategoricalElements.Add(measureElement);

            return olapReport;

        }

But I want to actually specify 2016 as the year and 4 as the company which are both valid elements in the cube, how do I acomplish this in my code?

Thanks for any help at all

[Ramesh Govindaraj]

Hi Nate, 

Thank you for using Syncfusion products, 

By default, PivotGrid displays all the available members under the hierarchy bind in OlapReport. Meanwhile, if we would like to display only specific members of the hierarchy, we need to add the remaining members in the excluded elements of the DimensionElement in OlapReport.  

We have prepared a sample where the member “FY 2003” from the hierarchy “Fiscal” only displayed in PivotGrid. Because, the remaining members of that hierarchy added in excluded elements. Please find the sample and code in the following links. 

Sample Link:  https://www.syncfusion.com/downloads/support/forum/126230/ze/PivotGrid-273450468  

Code: 

C# (Inside service): 

 

        private OlapReport CreateOlapReport() 

        { 

            OlapReport olapReport = new OlapReport(); 

 

            olapReport.Name = "Customer Report"; 

            olapReport.CurrentCubeName = "Adventure Works"; 

 

            DimensionElement dimensionElementColumn = new DimensionElement(); 

            dimensionElementColumn.Name = "Customer"; 

            dimensionElementColumn.HierarchyName = "Customer Geography"; 

            dimensionElementColumn.AddLevel("Customer Geography", "Country"); 

 

            MeasureElements measureElementColumn = new MeasureElements(); 

            measureElementColumn.Elements.Add(new MeasureElement { Name = "Internet Sales Amount" }); 

 

            DimensionElement dimensionElementRow = new DimensionElement(); 

            dimensionElementRow.Name = "Date"; 

            dimensionElementRow.AddLevel("Fiscal", "Fiscal Year"); 

 

            //Framing excluded dimension. 

            DimensionElement excludedElement = new DimensionElement(); 

            excludedElement.Name = "Date"; 

            excludedElement.HierarchyName = "Fiscal"; 

            excludedElement.AddLevel("Fiscal", "Fiscal Year"); 

 

            //Getting members from hierarchy. 

            string memberMdxQuery = "SELECT ADDCALCULATEDMEMBERS({" + excludedElement.HierarchyName + ".CHILDREN}) DIMENSION PROPERTIES MEMBER_NAME, MEMBER_TYPE ON 0, {} ON 1 FROM [" + olapReport.CurrentCubeName + "]"; 

            MASAC.AdomdConnection adomdConnectionObjMain = null; 

            adomdConnectionObjMain = new MASAC.AdomdConnection(@"Data Source=http://bi.syncfusion.com/olap/msmdpump.dll; Initial Catalog=Adventure Works DW 2008 SE;"); 

            adomdConnectionObjMain.Open(); 

            Microsoft.AnalysisServices.AdomdClient.CellSet set = new Microsoft.AnalysisServices.AdomdClient.AdomdCommand(memberMdxQuery, adomdConnectionObjMain).ExecuteCellSet(); 

            MASAC.Axis memberCollection = set.Axes[0]; 

 

            //Adding members in excludedElement 

            for (int i = 0; i < memberCollection.Positions.Count; i++) 

            { 

                if (memberCollection.Positions[i].Members[0].Caption != "FY 2003") 

                    excludedElement.Hierarchy.LevelElements["Fiscal Year"].Add(new MemberElement { Name = memberCollection.Positions[i].Members[0].Caption, UniqueName = memberCollection.Positions[i].Members[0].UniqueName }); 

            } 

 

            olapReport.CategoricalElements.Add(dimensionElementColumn); 

            olapReport.CategoricalElements.Add(measureElementColumn); 

            ///Adding excludedElement in Row section of OlapReport 

            olapReport.SeriesElements.Add(dimensionElementRow, excludedElement); 

 

            return olapReport; 

        } 

For more information, kindly refer the below online user-guide documentation. 

Documentation Link:  https://help.syncfusion.com/aspnet-classic/olapcommon/olapreport#report-with-dicing-operation  

Note: If the above doesn’t meets your requirement kindly, provide us more information about your requirement which would be helpful us to proceed further. 

Regards,
Ramesh G. 

[Nate Greene]

Hi Ramesh,

Thanks for the last example that you provided I was able to implement it and it works for some of my elements but not all of them. I created my report using yours as a guide : 

private OlapReport CreateOlapReport()

        {

            OlapReport olapReport = new OlapReport();

            olapReport.Name = "Company Sales";

            olapReport.CurrentCubeName = "Blue Streak Sales";

            DimensionElement dimensionElementColumn = new DimensionElement();

            dimensionElementColumn.Name = "Stores";

            dimensionElementColumn.HierarchyName = "Stores";

            dimensionElementColumn.AddLevel("Company", "Company");

            MeasureElements measureElementColumn = new MeasureElements();

            measureElementColumn.Elements.Add(new MeasureElement { Name = "Amount" });

            DimensionElement dimensionElementRow = new DimensionElement();

            dimensionElementRow.Name = "Date";

            dimensionElementRow.AddLevel("Year", "Year");

            //Framing excluded dimension. 

            DimensionElement excludedElement = new DimensionElement();

            excludedElement.Name = "Date";

            excludedElement.HierarchyName = "Date";

            excludedElement.AddLevel("Year", "Year");

            //Getting members from hierarchy. 

            string memberMdxQuery = "SELECT ADDCALCULATEDMEMBERS({" + excludedElement.HierarchyName + ".CHILDREN}) DIMENSION PROPERTIES MEMBER_NAME, MEMBER_TYPE ON 0, {} ON 1 FROM [" + olapReport.CurrentCubeName + "]";

            MASAC.AdomdConnection adomdConnectionObjMain = null;

            adomdConnectionObjMain = new MASAC.AdomdConnection(connectionString);

            adomdConnectionObjMain.Open();

            Microsoft.AnalysisServices.AdomdClient.CellSet set = new Microsoft.AnalysisServices.AdomdClient.AdomdCommand(memberMdxQuery, adomdConnectionObjMain).ExecuteCellSet();

            MASAC.Axis memberCollection = set.Axes[0];

            //Adding members in excludedElement 

            for (int i = 0; i < memberCollection.Positions.Count; i++)

            {

                if (memberCollection.Positions[i].Members[0].Caption != "2016")

                    excludedElement.Hierarchy.LevelElements["Year"].Add(new MemberElement { Name = memberCollection.Positions[i].Members[0].Caption, UniqueName = memberCollection.Positions[i].Members[0].UniqueName });

            }

            olapReport.CategoricalElements.Add(dimensionElementColumn);

            olapReport.CategoricalElements.Add(measureElementColumn);

            ///Adding excludedElement in Row section of OlapReport 

            olapReport.SeriesElements.Add(dimensionElementRow, excludedElement);

            return olapReport;

        }

That report works with no problems and it isolates the Year 2016 ignoring the other ones but when I try to limit what companies by making these changes to the report essentially just flipping the year and company elements:

DimensionElement dimensionElementColumn = new DimensionElement();

            dimensionElementColumn.Name = "Date";

            dimensionElementColumn.HierarchyName = "Date";

            dimensionElementColumn.AddLevel("Year", "Year");

            MeasureElements measureElementColumn = new MeasureElements();

            measureElementColumn.Elements.Add(new MeasureElement { Name = "Amount" });

            DimensionElement dimensionElementRow = new DimensionElement();

            dimensionElementRow.Name = "Stores";

            dimensionElementRow.AddLevel("Company", "Company");

            //Framing excluded dimension. 

            DimensionElement excludedElement = new DimensionElement();

            excludedElement.Name = "Stores";

            excludedElement.HierarchyName = "Stores";

            excludedElement.AddLevel("Company", "Company");

I get a 500 error in the browser, the control renders with no data in it and I traced the error with Fiddler and got:

{"Message":"An error has occurred.","ExceptionMessage":"Object reference not set to an instance of an object.","ExceptionType":"System.NullReferenceException","StackTrace":"   at BlueStreakReporting.WebAPI.OlapGridController.CreateOlapReport() in C:\\Users\\administrator.BLUESTREAK\\Documents\\Syncfusion MVC\\Projects\\BlueStreakReporting\\WebAPI\\OlapGridController.cs:line 224\r   at BlueStreakReporting.WebAPI.OlapGridController.InitializeOlapGrid(Dictionary`2 jsonResult) in C:\\Users\\administrator.BLUESTREAK\\Documents\\Syncfusion MVC\\Projects\\BlueStreakReporting\\WebAPI\\OlapGridController.cs:line 35\r   at lambda_method(Closure , Object , Object[] )\r   at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass10.<GetExecutor>b__9(Object instance, Object[] methodParameters)\r   at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.Execute(Object instance, Object[] arguments)\r   at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary`2 arguments, CancellationToken cancellationToken)\r--- End of stack trace from previous location where exception was thrown ---\r   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r   at System.Web.Http.Controllers.ApiControllerActionInvoker.<InvokeActionAsyncCore>d__0.MoveNext()\r--- End of stack trace from previous location where exception was thrown ---\r   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r   at System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__2.MoveNext()\r--- End of stack trace from previous location where exception was thrown ---\r   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r   at System.Web.Http.Dispatcher.HttpControllerDispatcher.<SendAsync>d__1.MoveNext()"}

Any help would be greatly appreciated.

Thanks,

Nate

[Nate Greene]

Just a quick addition to my earlier post, I changed the values in the loop that adds excluded elements to this:

//Adding members in excludedElement 

            for (int i = 0; i < memberCollection.Positions.Count; i++)

            {

                if (memberCollection.Positions[i].Members[0].Caption != "4")

                    excludedElement.Hierarchy.LevelElements["Company"].Add(new MemberElement { Name = memberCollection.Positions[i].Members[0].Caption, UniqueName = memberCollection.Positions[i].Members[0].UniqueName });

            }

And now the page just hangs on the spinning icon and does not load any data at all. I have attached a picture of the cube hierarchy in case I am misunderstanding how to point to specific elements.

Thanks,

Nate

Attachment: cubeElements_7b341520.zip

[Ramesh Govindaraj]

Hi Nate, 

Thanks for the screen-shot and OlapReport. 

We have tested the given OlapReport with your cube (given from older incident) and found that the reported problem is occurred due to adding excluded members in OlapReport whose hierarchy is not included in OlapReport. In your cube, two dimensions “Menu Items” and “Stores” has the same hierarchy name - “Company”. And inside the OlapReport, hierarchy name is used to get its members using MDX Query. So, by default it retrieves the members from “Menu Items” even if we want members from “Stores” which throws the exception. But, the problem can be solved by using “hierarchy unique name” instead of “hierarchy name” while getting members like below.   

memberMdxQuery = "SELECT ADDCALCULATEDMEMBERS({" + excludedElementCol.Hierarchy.UniqueName + ".CHILDREN}) DIMENSION PROPERTIES MEMBER_NAME, MEMBER_TYPE ON 0, {} ON 1 FROM [" + olapReport.CurrentCubeName + "]";  

And we have prepared a sample where, excluding the members from the mentioned two hierarchies “Year” and “Company” for your convenience. Please find the sample in the following link. 

Sample Link:  https://www.syncfusion.com/downloads/support/forum/126230/ze/PivotGrid-1294603133  

OlapReport: 

private OlapReport CreateOlapReport() 

        { 

            OlapReport olapReport = new OlapReport(); 

 

            olapReport.Name = "Company Sales"; 

            olapReport.CurrentCubeName = "Blue Streak Sales"; 

 

            DimensionElement dimensionElementColumn = new DimensionElement(); 

            dimensionElementColumn.Name = "Stores"; 

            dimensionElementColumn.AddLevel("Company", "Company"); 

 

            MeasureElements measureElementColumn = new MeasureElements(); 

            measureElementColumn.Elements.Add(new MeasureElement { Name = "Amount" }); 

 

            DimensionElement dimensionElementRow = new DimensionElement(); 

            dimensionElementRow.Name = "Date"; 

            dimensionElementRow.AddLevel("Year", "Year"); 

 

            //Opening adomd connection. 

            MASAC.AdomdConnection adomdConnectionObjMain = null; 

            adomdConnectionObjMain = new MASAC.AdomdConnection(connectionString); 

            adomdConnectionObjMain.Open(); 

 

            //Framing excluded dimension “Date”.  

            DimensionElement excludedElement = new DimensionElement(); 

            excludedElement.Name = "Date"; 

            excludedElement.AddLevel("Year", "Year"); 

 

            //Getting members from hierarchy “Year”.  

            string memberMdxQuery = "SELECT ADDCALCULATEDMEMBERS({" + excludedElement.Hierarchy.UniqueName + ".CHILDREN}) DIMENSION PROPERTIES MEMBER_NAME, MEMBER_TYPE ON 0, {} ON 1 FROM [" + olapReport.CurrentCubeName + "]"; 

            Microsoft.AnalysisServices.AdomdClient.CellSet set = new Microsoft.AnalysisServices.AdomdClient.AdomdCommand(memberMdxQuery, adomdConnectionObjMain).ExecuteCellSet(); 

            MASAC.Axis memberCollection = set.Axes[0]; 

 

            //Adding members in excludedElement  

            for (int i = 0; i < memberCollection.Positions.Count; i++) 

            { 

                if (memberCollection.Positions[i].Members[0].Caption != "2013") 

                    excludedElement.Hierarchy.LevelElements["Year"].Add(new MemberElement { Name = memberCollection.Positions[i].Members[0].Caption, UniqueName = memberCollection.Positions[i].Members[0].UniqueName }); 

            } 

 

            //Framing excluded dimension “Stores”.  

            DimensionElement excludedElementCol = new DimensionElement(); 

            excludedElementCol.Name = "Stores"; 

            excludedElementCol.AddLevel("Company", "Company"); 

 

            //Getting members from hierarchy “Company”.  

            memberMdxQuery = "SELECT ADDCALCULATEDMEMBERS({" + excludedElementCol.Hierarchy.UniqueName + ".CHILDREN}) DIMENSION PROPERTIES MEMBER_NAME, MEMBER_TYPE ON 0, {} ON 1 FROM [" + olapReport.CurrentCubeName + "]"; 

            set = new Microsoft.AnalysisServices.AdomdClient.AdomdCommand(memberMdxQuery, adomdConnectionObjMain).ExecuteCellSet(); 

            memberCollection = set.Axes[0]; 

 

            //Adding members in excludedElement  

            for (int i = 0; i < memberCollection.Positions.Count; i++) 

            { 

                if (memberCollection.Positions[i].Members[0].Caption != "Freshii") 

                    excludedElementCol.Hierarchy.LevelElements["Company"].Add(new MemberElement { Name = memberCollection.Positions[i].Members[0].Caption, UniqueName = memberCollection.Positions[i].Members[0].UniqueName }); 

            } 

 

            ///Adding excludedElement for the dimension “Stores”  

            olapReport.CategoricalElements.Add(dimensionElementColumn, excludedElementCol); 

            olapReport.CategoricalElements.Add(measureElementColumn); 

            ///Adding excludedElement for the dimension “Date”  

            olapReport.SeriesElements.Add(dimensionElementRow, excludedElement); 

 

            return olapReport; 

        } 

Note: If still the reported problem occurs, kindly provide us the complete OlapReport using your sample which would be helpful for us to proceed further. 

  

Regards,
Ramesh G. 

 

[Nate Greene]

Ramesh,

You are a life saver your solution worked like a charm! One quick question though, I notice that if I click on the filter button on the control itself It opens up a list of checkboxes where the user can still select the companies that were removed from the report(screenshot enclosed). How can I limit what goes into the filter popup as well? Disabling the filter for the Company element would be fine but for other elements I would just need some of the values removed but not all of them.

Many Thanks,

Nate

Attachment: filterList_13262c0f.zip

[Sastha Prathap Selvamoorthy]

Hi Nate,   

  

Glad to know that our solution worked.  

   

You can use the parameter “Roles” to the connectionString that can easily remove the members from the dimensions directly from the cube. Please find the following link that shows the steps to configure the cube using Roles.    

 https://www.mssqltips.com/sqlservertip/2776/configuring-permissions-for-sql-server-analysis-services/   

   

Also, please find the documentation link for role-based connection string (prototype) for your reference.   

   

https://help.syncfusion.com/aspnet-classic/olapcommon/how-to/establish-role-based-connection   

   

OlapDataManager DataManager = new OlapDataManager(@"Data Source=http://bi.syncfusion.com/olap/msmdpump.dll; Roles=Role1; Initial Catalog=Adventure Works DW 2008 SE;");   

  

   

Regards,   

Sastha Prathap S. 

[Nate Greene]

Sorry about the delay in responding but I am not the database admin and it took awhile to hear back about the potential of using server roles.

The database admin did not want to use server roles to accomplish this task and we may partition our data cube in the future. But for the time being can I use css to hide the elements I do not want in the checkbox list?

Thanks,

Nate

[Sastha Prathap Selvamoorthy]

Hi Nate,   

   

You can hide the respective member elements from the “Editor Dialog” by using the event function “renderSuccess” at the sample level itself. Please find the sample code snippet below for your reference.   

   

Sample code to hide the member elements in “Editor Dialog”:   

<script type="text/javascript">   

    var pivotClient;   

   

    $(function () {   

        $("#PivotClient").ejPivotClient({ url: '../api/OlapClient', renderSuccess:"memberEditorFilter" });   

    });   

   

    function memberEditorFilter(args) {   

    pivotClient = $("#PivotClient").data("ejPivotClient");   

    var excludeElements = ["FY 2003", "FY 2004"]; // Specify the excluded elements   

   

    if (!ej.isNullOrUndefined(pivotClient.memberTreeObj))   

        if (pivotClient.memberTreeObj.dataSource().length > 0) {   

            var liList = JSON.stringify(pivotClient.memberTreeObj.dataSource());   

            liList = JSON.parse(liList);   

            $.each(excludeElements, function (e, excludeElement) {   

                for (i = 0; i < liList.length; i++) {   

                    if (liList[i].name.length > 0 && liList[i].name == excludeElement)   

                        $(pivotClient.element.find(".editorTreeView ul li")[i]).hide();   

                }   

                for (j = 0; j < pivotClient.memberTreeObj.dataSource().length; j++) {   

                    if (pivotClient.memberTreeObj.dataSource()[j].name == excludeElement)   

                        pivotClient.memberTreeObj.dataSource().splice(j, 1);   

                }   

            });   

        }   

    }   

</script>   

  

Regards,   

Sastha Prathap S. 

[Nate Greene]

Hey guys,

The code that was provided last time to use Javascript to filter the members worked for the PivotClient but when I tried to change it to work for the PivotGrid it did not work at all. Here is the code I used for the PivotGrid

unction memberEditorFilter(args) {

            

            pivotClient = $("#PivotGrid1").data("ejPivotGrid");

            var excludeElements = ["Bakewell", "Coldstone"]; // Specify the excluded elements   

            if (!ej.isNullOrUndefined(pivotClient.memberTreeObj))

                if (pivotClient.memberTreeObj.dataSource().length > 0) {

                    var liList = JSON.stringify(pivotClient.memberTreeObj.dataSource());

                    liList = JSON.parse(liList);

                    $.each(excludeElements, function (e, excludeElement) {

                        for (i = 0; i < liList.length; i++) {

                            if (liList[i].name.length > 0 && liList[i].name == excludeElement)

                                $(pivotClient.element.find("#editorTreeView ul li")[i]).hide();

                        }

                        for (j = 0; j < pivotClient.memberTreeObj.dataSource().length; j++) {

                            if (pivotClient.memberTreeObj.dataSource()[j].name == excludeElement)

                                pivotClient.memberTreeObj.dataSource().splice(j, 1);

                        }

                    });

                }

        }

I would like to extend this functionality to the pivot grids as well so please can you tell me the correct syntax so I can filter out the elements I do not want from the editor popup list.

Any help will be greatly appreciated.

Thanks,

Nate

[Sastha Prathap Selvamoorthy]

Hi Nate, 

 

The provided sample code for hiding member elements for PivotClient in our last update only works with PivotClient control alone. You can hide the member elements by using the event “OnAfterServiceInvoke” at the sample level itself for PivotGrid using the below code snippet. 

 

Sample code to hide the member elements in “Editor Dialog” (PivotGrid with GroupingBar): 

 

@Html.EJ().Pivot().PivotGrid("PivotGrid1").Url(Url.Content("~/api/OlapGrid")).ClientSideEvents(events => events.AfterServiceInvoke("OnAfterServiceInvoke")).EnableGroupingBar(true) 

 

<script type="text/javascript"> 

    OnAfterServiceInvoke = function (evt) { 

 

        pivotGrid = $("#PivotGrid1").data("ejPivotGrid"); 

 

        var excludeElements = ["FY 2002", "FY 2003"]; // Specify the excluded elements    

 

        if (!ej.isNullOrUndefined(pivotGrid._memberTreeObj)) 

            if (pivotGrid._memberTreeObj.dataSource().length > 0) { 

                var liList = JSON.stringify(pivotGrid._memberTreeObj.dataSource()); 

                liList = JSON.parse(liList); 

                $.each(excludeElements, function (e, excludeElement) { 

                    for (i = 0; i < liList.length; i++) { 

                        if (liList[i].name.length > 0 && liList[i].name == excludeElement) 

                            $(pivotGrid.element.find("#editorTreeView ul li")[i]).hide(); 

                    } 

                    for (j = 0; j < pivotGrid._memberTreeObj.dataSource().length; j++) { 

                        if (pivotGrid._memberTreeObj.dataSource()[j].name == excludeElement) 

                            pivotGrid._memberTreeObj.dataSource().splice(j, 1); 

                    } 

                }); 

            } 

    } 

</script> 

    

Sample code to hide the member elements in “Editor Dialog” (PivotGrid with PivotSchemadesigner):    

 

 

@Html.EJ().Pivot().PivotGrid("PivotGrid1").Url(Url.Content("~/api/OlapGrid")).ClientSideEvents(events => events.AfterServiceInvoke("OnAfterServiceInvoke")).EnableGroupingBar(true) 

     

@Html.EJ().Pivot().PivotSchemaDesigner("PivotSchemaDesigner").Layout(PivotSchemaDesignerLayout.Excel).ClientSideEvents(events => events.AfterServiceInvoke("OnAfterServiceInvoke1")) 

 

<script type="text/javascript"> 

    OnAfterServiceInvoke = function (evt) { 

        if (evt.action == "initialize") { 

            var PivotSchemaDesigner = $("#PivotSchemaDesigner").data('ejPivotSchemaDesigner'); 

            if (PivotSchemaDesigner.model.pivotControl == null) { 

                PivotSchemaDesigner.model.pivotControl = this; 

                PivotSchemaDesigner.model.enableWrapper = true; 

                PivotSchemaDesigner.model.layout = "excel"; 

                PivotSchemaDesigner._load(); 

            } 

        } 

    } 

 

    function OnAfterServiceInvoke1(args) { 

        pivotSchema = $("#PivotSchemaDesigner").data("ejPivotSchemaDesigner") 

        var excludeElements = ["FY 2002", "FY 2003"]; // Specify the excluded elements    

 

        if (!ej.isNullOrUndefined(pivotSchema._currentMembers)) 

            if (JSON.parse(pivotSchema._currentMembers).length > 0) { 

                var liList = JSON.parse(pivotSchema._currentMembers); 

                $.each(excludeElements, function (e, excludeElement) { 

                    for (j = 0; j < liList.length; j++) { 

                        if (liList[j].name == excludeElement) 

                            liList.splice(j, 1); 

                    } 

                }); 

                pivotSchema._currentMembers = JSON.stringify(liList); 

            } 

    } 

</script> 

 

Please let us know if you have any concern. 

 

Regards, 

Sastha Prathap S. 

[Nate Greene]

Thanks for the response, the first example without the PivotSchemaDesigner worked as expected and filtered the elements as needed. But the second one that includes both the grouping bar and the schema designer failed to work. I have included the code from my view with all the javascript that was suggested. 

  @Html.EJ().Pivot().PivotGrid("PivotGrid1").EnableGrandTotal(false).EnableDeferUpdate(true).IsResponsive(true).EnableConditionalFormatting(true).Url(Url.Content("~/api/OLAPGrid")).ClientSideEvents(events => events.AfterServiceInvoke("OnAfterServiceInvoke").RenderSuccess("renderSuccess")).EnableGroupingBar(true)

    @Html.EJ().Pivot().PivotSchemaDesigner("PivotSchemaDesigner").Layout(PivotSchemaDesignerLayout.Excel).ClientSideEvents(events => events.AfterServiceInvoke("OnAfterServiceInvoke1")) 

   

    <script>

        

   function OnAfterServiceInvoke(evt) { 

        if (evt.action == "initialize") { 

            var PivotSchemaDesigner = $("#PivotSchemaDesigner").data('ejPivotSchemaDesigner'); 

            if (PivotSchemaDesigner.model.pivotControl == null) { 

                PivotSchemaDesigner.model.pivotControl = this; 

                PivotSchemaDesigner.model.enableWrapper = true; 

                PivotSchemaDesigner.model.layout = "excel"; 

                PivotSchemaDesigner._load(); 

            } 

        } 

    } 

    function OnAfterServiceInvoke1(args) { 

            pivotSchema = $("#PivotSchemaDesigner").data("ejPivotSchemaDesigner") 

            var excludeElements = ["Bakewell","Coldstone"]; // Specify the excluded elements    

 

            if (!ej.isNullOrUndefined(pivotSchema._currentMembers)) 

                if (JSON.parse(pivotSchema._currentMembers).length > 0) { 

                    var liList = JSON.parse(pivotSchema._currentMembers); 

                    $.each(excludeElements, function (e, excludeElement) { 

                        for (j = 0; j < liList.length; j++) { 

                            if (liList[j].name == excludeElement) 

                                liList.splice(j, 1); 

                        } 

                    }); 

                    pivotSchema._currentMembers = JSON.stringify(liList); 

                } 

        } 

     </script>

I am not sure what I am missing there are no javascript errors happening at all from the browser either, Does both the grouping bar and schema designer work together with this function or do I have to choose one or the other?

Any help will be greatly appreciated.

Thanks,

Nate

[Sastha Prathap Selvamoorthy]

Hi Andreas, 

 

From the provided code snippet, we think that the code for hiding the member elements is missing for “GroupingBar”, code is available for PivotSchemaDesigner only.  So, you could apply both code snippet that we provided in our last update to working for this configuration of this combination. Also, we have provided sample code snippet below for your reference. 

 

Sample code to hide the member elements in “Editor Dialog” (PivotGrid with GroupingBar and PivotSchemadesigner combination): 

 

@Html.EJ().Pivot().PivotGrid("PivotGrid1").Url(Url.Content("~/api/OlapGrid")).ClientSideEvents(events => events.AfterServiceInvoke("OnAfterServiceInvoke")).EnableGroupingBar(true) 

     

@Html.EJ().Pivot().PivotSchemaDesigner("PivotSchemaDesigner").Layout(PivotSchemaDesignerLayout.Excel).ClientSideEvents(events => events.AfterServiceInvoke("OnAfterServiceInvoke1")) 

 

<script type="text/javascript"> 

    OnAfterServiceInvoke = function (evt) { 

        if (evt.action == "initialize") { 

            var PivotSchemaDesigner = $("#PivotSchemaDesigner").data('ejPivotSchemaDesigner'); 

            if (PivotSchemaDesigner.model.pivotControl == null) { 

                PivotSchemaDesigner.model.pivotControl = this; 

                PivotSchemaDesigner.model.enableWrapper = true; 

                PivotSchemaDesigner.model.layout = "excel"; 

                PivotSchemaDesigner._load(); 

            } 

        } 

        pivotGrid = $("#PivotGrid1").data("ejPivotGrid"); 

        var excludeElements = ["FY 2002", "FY 2003"]; // Specify the excluded elements for PivotGrid with GroupingBar   

 

        if (!ej.isNullOrUndefined(pivotGrid._memberTreeObj)) 

            if (pivotGrid._memberTreeObj.dataSource().length > 0) { 

                var liList = JSON.stringify(pivotGrid._memberTreeObj.dataSource()); 

                liList = JSON.parse(liList); 

                $.each(excludeElements, function (e, excludeElement) { 

                    for (i = 0; i < liList.length; i++) { 

                        if (liList[i].name.length > 0 && liList[i].name == excludeElement) 

                            $(pivotGrid.element.find("#editorTreeView ul li")[i]).hide(); 

                    } 

                    for (j = 0; j < pivotGrid._memberTreeObj.dataSource().length; j++) { 

                        if (pivotGrid._memberTreeObj.dataSource()[j].name == excludeElement) 

                            pivotGrid._memberTreeObj.dataSource().splice(j, 1); 

                    } 

                }); 

            } 

    } 

 

    function OnAfterServiceInvoke1(args) { 

        pivotSchema = $("#PivotSchemaDesigner").data("ejPivotSchemaDesigner") 

        var excludeElements = ["FY 2002", "FY 2003"]; // Specify the excluded elements for PivotGrid with PivotSchemadesigner 

 

        if (!ej.isNullOrUndefined(pivotSchema._currentMembers)) 

            if (JSON.parse(pivotSchema._currentMembers).length > 0) { 

                var liList = JSON.parse(pivotSchema._currentMembers); 

                $.each(excludeElements, function (e, excludeElement) { 

                    for (j = 0; j < liList.length; j++) { 

                        if (liList[j].name == excludeElement) 

                            liList.splice(j, 1); 

                    } 

                }); 

                pivotSchema._currentMembers = JSON.stringify(liList); 

            } 

    } 

</script> 

 

Please let us know if you have any concern. 

 

Regards, 

Sastha Prathap S.