1. Home
  2. Forum
  3. ASP.NET MVC - EJ 2
  4. Adding/Removing Values/Measures from PivotView on Button Click

Adding/Removing Values/Measures from PivotView on Button Click

Hi:

I'm new to the Syncfusion suite of components and I'm having some trouble wrapping my head around satisfying a requirement.  I have the below PivotView control with 5 different Values/Measures.  I'm hoping there's something I can do in JS to access the datasource of the control and manipulate it, but I need to be able to add and remove Values/Measures on button click to achieve the following results:

Initial Load
     Values: BidQuantity, UnitPrice, Total

Button A
     Values: BidQuantity, UnitPrice, Total, BidPercentage

Button B
     Values: BidQuantity, UnitPrice, Total, TotalCostPerPiece

Button C (reset to default)
     Values: BidQuantity, UnitPrice, Total


@Html.EJS().PivotView("pivotview_" + Model).HtmlAttributes(pivotView).Width("100%").DataSource(dataSource => dataSource.Data((IEnumerable<object>)ViewBag.Data)
    .ExpandAll(true)
    .EmptyCellsTextContent("*")
    .FormatSettings(formatsettings =>
    {
        formatsettings.Name("BidQuantity").MinimumFractionDigits(0).MaximumFractionDigits(4).UseGrouping(true).Add();
        formatsettings.Name("UnitPrice").Format("C").MinimumFractionDigits(2).MaximumFractionDigits(4).UseGrouping(true).Add();
        formatsettings.Name("Total").Format("C").MinimumFractionDigits(2).MaximumFractionDigits(4).UseGrouping(true).Add();
        formatsettings.Name("BidPercentage").Format("P").MinimumFractionDigits(0).MaximumFractionDigits(4).UseGrouping(true).Add();
        formatsettings.Name("TotalCostPerPiece").Format("C").MinimumFractionDigits(2).MaximumFractionDigits(4).UseGrouping(true).Add();
    })
    .Rows(rows =>
    {
        rows.Name("CostComponentSubCategory").Add();
        rows.Name("CostComponent").Add();
    })
    .Columns(columns =>
    {
        columns.Name("Supplier").Add();
    })
    .Values(values =>
    {
        values.Name("BidQuantity").Caption("Quantity").Type(Syncfusion.EJ2.PivotView.SummaryTypes.Avg).Add();
        values.Name("UnitPrice").Caption("Unit $").Type(Syncfusion.EJ2.PivotView.SummaryTypes.Avg).Add();
        values.Name("Total").Caption("Total").Type(Syncfusion.EJ2.PivotView.SummaryTypes.Avg).Add();//sum
        values.Name("BidPercentage").Caption("%").Type(Syncfusion.EJ2.PivotView.SummaryTypes.Avg).Add();//sum
        values.Name("TotalCostPerPiece").Caption("$/pc").Type(Syncfusion.EJ2.PivotView.SummaryTypes.Avg).Add();
    }).ConditionalFormatSettings(format =>
    {
        //GLOBAL CONDITIONS
        format.Conditions(Syncfusion.EJ2.PivotView.Condition.NotEquals).Value1(-1).Style(style => { style.FontSize("12px"); }).Add();

    })).GridSettings(new Syncfusion.EJ2.PivotView.PivotViewGridSettings
    {
        ColumnWidth = 70,
        RowHeight = 20,
        ClipMode = "EllipsisWithTooltip"
    }
    ).AllowConditionalFormatting(true).Render()

7 Replies
JP Jagadeesan Pichaimuthu Syncfusion Team June 10, 2019 06:40 AM UTC

Hi Doug, 
  
You can add/remove value fields like in the below code snippet. 
  
Code Snippet: 
<script> 
    document.getElementById("refresh-btn").addEventListener('click', function () { 
        var pivotGridObj = document.getElementById("PivotGrid").ej2_instances[0]; 
        pivotGridObj.dataSource.values = [{ name: "Sold", caption: "Units Sold"}, { name: "Amount", caption: "Sold Amount" }, 
        { name: "Total", caption: "Total Price", type: "CalculatedField" }]; 
    }); 
    document.getElementById("reset-btn").addEventListener('click', function () { 
        var pivotGridObj = document.getElementById("PivotGrid").ej2_instances[0]; 
        pivotGridObj.dataSource.values = [{ name: "Sold", caption: "Units Sold"}, { name: "Amount", caption: "Sold Amount" }]; 
    }); 
</script> 
  
  
Please let us know if you need further assistance on this. 
  
Regards, 
Jagadeesan

DL Doug Lenos June 10, 2019 02:59 PM UTC

Jagadeesan:

Your solution works perfect!  Though the refresh is a touch harsh (more so with conditional formatting than your sample).  I moved two of my Values into the Calculated Fields, so they wouldn't show on default.  I just set the formula = to the column it was previously:

.Values(values =>
    {
        values.Name("BidQuantity").Caption("Quantity").Type(Syncfusion.EJ2.PivotView.SummaryTypes.Avg).Add();
        values.Name("UnitPrice").Caption("Unit $").Type(Syncfusion.EJ2.PivotView.SummaryTypes.Avg).Add();
        values.Name("Total").Caption("Total").Type(Syncfusion.EJ2.PivotView.SummaryTypes.Avg).Add();//sum
        //values.Name("BidPercentage").Caption("%").Type(Syncfusion.EJ2.PivotView.SummaryTypes.Avg).Add();//sum
        //values.Name("TotalCostPerPiece").Caption("$/pc").Type(Syncfusion.EJ2.PivotView.SummaryTypes.Avg).Add();
    }).CalculatedFieldSettings(calculatedFieldSettings =>
    {
        calculatedFieldSettings.Name("%").Formula("BidPercentage").Add();
        calculatedFieldSettings.Name("$/pc").Formula("TotalCostPerPiece").Add();
    })

Below is what I ended up having my script look like.  I noticed that the Aggregates/Grand Totals didn't carry over the Average (Avg) Type unless I specified it in the JS.

<script>
    document.getElementById("div_toggleView1_" + @Model).addEventListener('click', function () {
        debugger;
        var pivotGridObj = document.getElementById("pivotview_" + @Model).ej2_instances[0];

        pivotGridObj.dataSource.values = [
            { name: "BidQuantity", caption: "Quantity", type: "Avg" },
            { name: "UnitPrice", caption: "Unit $", type: "Avg" },
            { name: "Total", caption: "Total", type: "Avg" },
            { name: "BidPercentage", caption: "%", type: "Avg" }
        ];
    });
    document.getElementById("div_toggleView2_" + @Model).addEventListener('click', function () {
        debugger;
        var pivotGridObj = document.getElementById("pivotview_" + @Model).ej2_instances[0];

        pivotGridObj.dataSource.values = [
            { name: "BidQuantity", caption: "Quantity", type: "Avg" },
            { name: "UnitPrice", caption: "Unit $", type: "Avg" },
            { name: "Total", caption: "Total", type: "Avg" },
            { name: "TotalCostPerPiece", caption: "$/pc", type: "Avg" }
        ];
    });
    document.getElementById("div_toggleView3_" + @Model).addEventListener('click', function () {
        debugger;
        var pivotGridObj = document.getElementById("pivotview_" + @Model).ej2_instances[0];

        pivotGridObj.dataSource.values = [
            { name: "BidQuantity", caption: "Quantity", type: "Avg" },
            { name: "UnitPrice", caption: "Unit $", type: "Avg" },
            { name: "Total", caption: "Total", type: "Avg" }
        ];
    });
</script>


JP Jagadeesan Pichaimuthu Syncfusion Team June 11, 2019 08:53 AM UTC

Hi Doug, 
  
Thanks for the update. 
  
You can change aggregation type for calculated field in formula itself and you need to specify type as ‘CalculatedField’ while adding to values. Please refer below code snippet. 
  
Code Snippet: 
<div> 
    @{var amount = "\"" + "Avg(Amount)" + "\"";} 
   @Html.EJS().PivotView("PivotGrid").Width("100%").Height("300").DataSource(dataSource => dataSource.Data((IEnumerable<object>)ViewBag.Data).ExpandAll(false).EnableSorting(true) 
    .CalculatedFieldSettings(calculatedfieldsettings => 
    { 
        calculatedfieldsettings.Name("Total").Formula(amount).Add(); 
    })).AllowCalculatedField(true).ShowFieldList(true).Render() 
</div> 
<script> 
    document.getElementById("refresh-btn").addEventListener('click', function () { 
        var pivotGridObj = document.getElementById("PivotGrid").ej2_instances[0]; 
        pivotGridObj.dataSource.values = [{ name: "Sold", caption: "Units Sold" }, { name: "Amount", caption: "Sold Amount" }, 
        { name: "Total", caption: "Average of Amount", type: "CalculatedField" }]; 
    }); 
</script> 
  
You can set aggregation type for value fields (other than calculated field) using type property. Please refer the below code snippet. 
  
Code Snippet: 
<div> 
    @{var amount = "\"" + "Avg(Amount)" + "\"";} 
   @Html.EJS().PivotView("PivotGrid").Width("100%").Height("300").DataSource(dataSource => dataSource.Data((IEnumerable<object>)ViewBag.Data).ExpandAll(false).EnableSorting(true) 
    .Values(values => 
    { 
        values.Name("Sold").Caption("Units Sold").Add(); values.Name("Amount").Caption("Sold Amount").Type(Syncfusion.EJ2.PivotView.SummaryTypes.Avg).Add(); 
    }).AllowCalculatedField(true).ShowFieldList(true).Render() 
</div> 
  
  
Document: 
  
If this is different from your requirement, then please let us know the more details, so that we can work towards fulfilling your requirement. 
  
Regards, 
Jagadeesan

DL Doug Lenos June 11, 2019 12:18 PM UTC

Jagadeesan:

I get all of my 5 Values from a SQL View.  Calculations for my purposes are conducted on the SQL side.  I don't really have a need for the client side calculation.  In the CalculatedFieldSettings I set the formula like this "calculatedFieldSettings.Name("$/pc").Formula("TotalCostPerPiece").Add();" because I need the "calculation" to just be the value of itself.  With that being said, if I don't set the aggregate in the formula (since it's not a traditional client side calculated field), I set the Type in the JS like this: { name: "TotalCostPerPiece", caption: "$/pc", type: "Avg" }.  I think the aggregate would default to Sum otherwise.  Whether this is supported functionality, or not, it appears to be functioning accurately...

With respect to my original question, I think you've solved it already - assuming my alterations are sufficient.

I do have some questions regarding aggregates, likely with the use of the CellTemplate, but I'll end up making another forum post for that to keep everything clean and separated.

MM Manikandan Murugesan Syncfusion Team June 11, 2019 02:11 PM UTC

Hi Doug, 
 
Thanks for the reply. 
 
 
Query 
Comments 
1. 
I get all of my 5 Values from a SQL View.  Calculations for my purposes are conducted on the SQL side.  I don't really have a need for the client-side calculation.  In the CalculatedFieldSettings I set the formula like this "calculatedFieldSettings.Name("$/pc").Formula("TotalCostPerPiece").Add();" because I need the "calculation" to just be the value of itself.  With that being said, if I don't set the aggregate in the formula (since it's not a traditional client side calculated field), I set the Type in the JS like this: { name: "TotalCostPerPiece", caption: "$/pc", type: "Avg" }. 
You can bind necessary value fields alone in value axis. And, you don’t need to add remaining fields in calculatedFieldSettings. 
2. 
I think the aggregate would default to Sum otherwise.  Whether this is supported functionality, or not, it appears to be functioning accurately... 
Yes. By default, values are aggregated as Sum. And, you can change aggregation using Type property. 
 
 
Please get back to us if you need further assistance on this. 
 
Regards, 
Manikandan. 


DL Doug Lenos June 13, 2019 06:41 PM UTC

Manikandan:

Thank you for your reply.  I have removed the calculated fields all together and it is functioning fine without them.  However in testing with IE, I have found that updating the datasource in this way makes the grid lose its "ColumnWidth" setting.  Oddly enough, the "RowHeight" setting does stay intact... Is there a way to make the GridSettings survive the change in datasource?  

For IE to show up in the first place I did have to add this line to my _Layout page:
<script src="https://cdn.polyfill.io/v2/polyfill.min.js"></script>

JP Jagadeesan Pichaimuthu Syncfusion Team June 14, 2019 10:58 AM UTC

Hi Doug, 
  
We are unable to reproduce the reported problem at our end. Please refer to following video link. 
  
If the video doesn’t show the appropriate replication steps, then provide us the same in the detailed manner. 
  
If you want to change the column width means, you can change it in ‘GridSettings’. Also, you can change each column width in ‘ColumnRender’ event. Please refer following code snippet. 
Code Snippet: 
@using Syncfusion.EJ2.PivotView 
  
@Html.EJS().PivotView("PivotGrid").Width("100%").Height("300").DataSource(dataSource => dataSource.Data((IEnumerable<object>)ViewBag.Data).ExpandAll(false).EnableSorting(true) 
    .GridSettings(new PivotViewGridSettings { ColumnWidth = 120, ColumnRender ="columnRender" }).AllowCalculatedField(true).ShowFieldList(true).Render() 
  
<script> 
    function columnRender(args) { 
        args.columns[0].width = 100; // You can change each column width here. 
    } 
    </script> 
  
  
  
Please let us know if any concerns. 
  
Regards, 
Jagadeesan 


Loader.
Up arrow icon