- Home
- Forum
- ASP.NET MVC - EJ 2
- 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()
SIGN IN To post a reply.
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
SIGN IN To post a reply.
- 7 Replies
- 3 Participants
-
DL Doug Lenos
- Jun 7, 2019 07:57 PM UTC
- Jun 14, 2019 10:58 AM UTC
