Refresh a PivotGrid

[Taras]

Is there a way to refresh an Asp.Net Core PivotGri?

i.e.: I have a PivotGrid open.  I know that there are changes to the underlying tables and I want to see the changes in the PivotGrid without having to close it and then re-open it.

Using Asp.Net Core 6, C# and Telerik 2323.1.307

 

Thanks

[Aleksandar]

Hello Taras,

The client-side refresh method of the PivotGrid will re-render the component using the existing data. An approach that would force a read request to fetch new data is to use the setDataSource method and reset the current state of the PivotGrid using the initial options, as demonstrated in the following knowledgebase article:

Forcefully Reload the Data Source of the PivotGrid

You can further use the pivotDataSource API to get the rows, columns, measures, for example, from the current state and pass them to the configuration when forcefully reloading.

Regards,
Aleksandar
Progress Telerik

[Taras]

Aleksandar,

Thanks for your reply.  I tried to implement it but nothing is displayed.

To take a step back.  Here is my current code that works.  The app uses Asp.Net Core 6 with MVVM pattern

@*For local binding*@

@using Application.Models;
@using Kendo.Mvc.Extensions;
@using Kendo.Mvc.UI;

@model IEnumerable<ResponseReportPivot>

@{
    ViewBag.Title = "Response Report Pivot Grid";
}
@Html.AntiForgeryToken()

<style>
    .k-pivot-table .k-grid-content td {
        text-align: left;
    }
</style>

<button id="export" class="hidden-on-narrow">Export to Excel</button>
<hr class="k-hr" />

<div class="k-pivotgrid-wrapper">
    @(Html.Kendo().PivotConfigurator()
        .Name("configurator")
        .HtmlAttributes(new { @class = "hidden-on-narrow" })
        .Filterable(true)
        .Sortable(true)
        .Height(570)
    )

    @(Html.Kendo().PivotGrid<ResponseReportPivot>()
        .Name("pivotgrid")
        .Excel(excel => excel
            .FileName(ViewBag.ExportName)
            .Filterable(true)
            .ProxyURL(Url.Action("_ExcelExport", "ResponseReportPivotController"))
        )
        .Configurator("#configurator")
        .ColumnWidth(100)
        .Filterable(true)
        .Height(570)
        .Sortable(true)
        .BindTo(Model)
        .DataSource(dataSource => dataSource
            .Ajax()
            .Sort(s => 
            {
                s.Add("Venue").Ascending();
                s.Add("EventYear").Ascending();
            })
            .Schema(schema => schema
                .Cube(cube => cube
                    .Dimensions(dimensions =>
                    {
                        dimensions.Add(model => model.Venue).Caption("All Venues");
                        dimensions.Add(model => model.EventYear).Caption("All Years");
                    })
                    .Measures(measures =>
                    {
                        measures.Add("Count").Field(model => model.Venue).AggregateName("count");
                    })
                )
            )
            .Columns(columns =>
            {
                columns.Add("EventYear").Expand(true);
            })
            .Rows(rows => rows.Add("Venue").Expand(true))
            .Measures(measures => measures.Values("Count"))
            .Events(e => e.Error("onError"))
        )
    )
</div>
<div class="responsive-message"></div>

@*<script>
    function onError(e) {
        alert("error: " + kendo.stringify(e.errors[0]));
    }
</script>*@

<script src="//cdnjs.cloudflare.com/ajax/libs/jszip/2.4.0/jszip.min.js"></script>
<script>
    $(document).ready( function () {
        $("#export").kendoButton({
            icon: "file-xls",
            click: function() {
                $("#pivotgrid").getKendoPivotGrid().saveAsExcel();
            }
        });
    });
    function onError(e) {
        alert("error: " + kendo.stringify(e.errors[0]));
    }
</script>

Can I use this as the base?  Or do I need to switch to the linked version? (Note: I tried to adapt but run into the issue that dataSourceConfig isn't defined or that pivotgrid.refresh() is not a function.)

When I tried to adapt the link to Forcefull Reload the data, I came up with the code below. (I also used the Local Binding example).

I get an error that responsereportpivot is undefined at data: responsereportpivot,.  I've tried this without the AutoBind and Bindto

@using Application.Models;
@using Kendo.Mvc.Extensions;
@using Kendo.Mvc.UI;

@model IEnumerable<ResponseReportPivot>

@{
    ViewBag.Title = "Response Report Pivot Grid";
}
@Html.AntiForgeryToken()

<button id="export" class="hidden-on-narrow">Export to Excel</button>
@*<button id="refresh" class="hidden-on-narrow">Refresh Data</button>*@

<hr class="k-hr" />

<div id="resposereportpivotgrid">
    <div id="refrsh">Refresh Data</div>
    <div id="pivotgrid"></div>

    <script>
        $(document).ready(function () {

            $("#refresh").kendoButton({
                click: function () {
                    var pivot = $("#pivotgrid").data("kendoPivotGrid");
                    reset(pivot, dataSourceConfig);
                }
            });

            $("#export").kendoButton({
                icon: "file-xls",
                click: function () {
                    $("#pivotgrid").getKendoPivotGrid().saveAsExcel();
                }
            });

            function reset(pivot, config) {
                // Call the setDataSource method and reset the current state of the PivotGrid using the initial options.
                // This will cause the data to reload with the exact same configuration.
                pivot.setDataSource(new kendo.data.PivotDataSource(config));
            };

            var dataSourceConfig = {
                type: "ajax",
                dataSource: {
                    data: ResponseReportPivot,
                    schema: {
                        model: {
                            fields: {
                                Id: { type: "string" },
                                Venue: { type: "string" },
                                EventYear: { type: "number" }
                            }
                        },
                        cube: {
                            dimensions: {
                                Venue: { caption: "All Venues" },
                                EventYear: { caption: "All Years" }
                            },
                            measures: {
                                "Count": { field: "Venue", aggregate: "count" }
                            }
                        }
                    },
                },
                columns: [{ name: "EventYear", expand: true }],
                rows: [{ name: "Venue", expand: true }],
                measures: ["Count"]
            };

            var pivotgrid = $("#pivotgird").kendoPivotGrid({
                filterable: true,
                sortable: true,
                columnWidth: 200,
                height: 500,
                dataSource: dataSourceConfig
            }).data("kendoPivotGrid");

        });

    </script>
    <style>
        .k-pivot-table .k-grid-content td {
                text-align: left;
            }

        #refresh {
            margin-bottom: 10px;
        }
    </style>

</div>

<div class="responsive-message"></div>
<script src="//cdnjs.cloudflare.com/ajax/libs/jszip/2.4.0/jszip.min.js"></script>

How do I specify that IEnumerable<ResponseReportPivot> should be the input source?

[Aleksandar]

Thank you for the additional clarification. The article shared above demonstrates how to refresh a PivotGrid that is bound to a XMLA PivotDataSource. From the details shared I see the PivotGrid is bound to flat data and is using remote Ajax binding. In this scenario you can use the dataSource data method to make a read request to the specified endpoint, for example:

<script>
    function reloadData(){
        var pivot = $("#pivotgrid").getKendoPivotGrid();
        pivot.dataSource.read();
    }
</script>

@(Html.Kendo().Button().Name("btn")
    .Content("Reload Data")
    .Events(ev=>ev.Click("reloadData"))
)

Would this approach work in the scenario you have?

[Taras]

Hi Aleksandar,

It does not work.

The pivotgrid I posted at the top of my March 30th reply using AJAX Local Binding.  The controller for the pivotgrid is shown below.

namespace Application.Controllers
{
    public class ResponseReportPivotController : AppController<Models.ResponseReportPivot, Guid, ResponseReportPivotViewModel>
    {
        private readonly DatabaseContext _db;

        public ResponseReportPivotController(IControllerServices services, DatabaseContext db)
            : base(services)
        {
            _db = db;
        }

        public IActionResult PivotGrid()
        {
            ViewBag.ExportName = "VenuePivotGrid-" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xlsx";
            IEnumerable<ResponseReportPivot> objResponseList = _db.ResponseReportPivot;
            var sortedResponseList = objResponseList.OrderBy(s => s.Venue).ThenBy(s => s.EventYear);
            return View(sortedResponseList);
        }

        [HttpPost]
        public ActionResult _ExcelExport(string contentType, string base64, string fileName)
        {
            var fileContents = Convert.FromBase64String(base64);

            return File(fileContents, contentType, fileName);
        }
    }
}

When I add the code you suggest in your April 4th response, I get the attached display..

When I click on the Refresh Data button, I have an alert display that confirms I've entered the function, but the data is not refreshed.

The code that works, but doesn't refresh, is based on this thread Pivot Grid from SQL data table - assistance required in UI for ASP.NET Core | Telerik Forums

[Aleksandar]

I've missed that the PivotGrid is actually bound to the model. In this scenario you can use the dataSource.data method to set the dataItems for the PivotGrid.

In this example the PivotGrid is initially bound to a collection of items. Upon an ajax call to an endpoint returning a DataSourceResult object the dataSource.data() method is used to set the data of the PivotGrid to the Data field of the response:

@{
    var data = Enumerable.Range(1,100).Select(x=>new CustomerViewModel()
    {
                    CustomerID = x.ToString(),
                    CompanyName = "CompanyName "+x,
                    ContactName = "ContactName "+x,
                    ContactTitle = "ContactTitle "+x,
                    Address = "Address "+x,
                    City = "City "+x,
                    Region = "Region "+x,
                    PostalCode = "PostalCode "+x,
                    Country = "Country "+x,
                    Phone = 123456789.ToString(),
                    Fax = "Fax "+x,
                    Bool = x % 2 == 0 ? true : false
    });
}
<script>
    function reloadData(){
        var pivot = $("#pivotgrid").getKendoPivotGrid();
        $.ajax({
        url:"@Url.Action("Customers_Read", "PivotGrid")",
        type:"POST",
        success:function(response){
            pivot.dataSource.data(response.Data)
        }
    })
    }
</script>

@(Html.Kendo().Button().Name("btn")
    .Content("Reload Data")
    .Events(ev=>ev.Click("reloadData"))
)
<div class="k-pivotgrid-wrapper">
    @(Html.Kendo().PivotConfigurator()
        .Name("configurator")
        .HtmlAttributes(new { @class = "hidden-on-narrow" })
        .Filterable(true)
        .Sortable(true)
        .Height(570)
    )

    @(Html.Kendo().PivotGrid<Kendo.Mvc.Examples.Models.CustomerViewModel>()
        .Name("pivotgrid")
        .HtmlAttributes(new { @class = "hidden-on-narrow" })
        .Configurator("#configurator")
        .ColumnWidth(120)
        .Filterable(true)
        .Sortable(true)
        .Height(570)
        .BindTo(@data)
        .DataSource(dataSource => dataSource
            .Ajax()
            .Schema(schema => schema
                .Cube(cube => cube
                    .Dimensions(dimensions => {
                        dimensions.Add(model => model.ContactName).Caption("All Contacts");
                        dimensions.Add(model => model.CompanyName).Caption("All Companies");
                        dimensions.Add(model => model.Country).Caption("All Countries");
                        dimensions.Add(model => model.ContactTitle).Caption("All Titles");
                    })
                    .Measures(measures => measures.Add("Contacts Count").Field(model => model.CustomerID).AggregateName("count"))
                ))
            .Columns(columns =>
            {
                columns.Add("Country").Expand(true);
                columns.Add("CompanyName");
            })
            .Rows(rows => rows.Add("ContactTitle").Expand(true))
            .Measures(measures => measures.Values("Contacts Count"))
            .Events(e => e.Error("onError"))
        )
    )
</div>

[Taras]

What does your PivotGrid/Customer_Read method in the Controller look like?

Defining data in the View is not a realistic example.

I changed added the following to the View based on your code

    function reloadData() {
        var pivot = $("#pivotgrid").getKendoPivotGrid();
        $.ajax({
            url: "@Url.Action("ReadData", "ResponseReportPivotController")",
            type: "POST",
            success: function (response) {
                pivot.dataSource.data(response.Data)
            }
        })
    }

And when its executed I get the following error in the browser console.  ReadData exists in the controller.

https://localhost:44367/ResponseReportPivotController/ReadData 400

[Aleksandar]

In the Telerik REPL we cannot yet modify the model data passed to the view. We also do not have an example with local data binding to a collection of CustomerViewModel in order for me to to use the Remote endpopint for th eRemote Binding example of the Pivot Grid, in order to demonstrate how to achieve the desired result. However, passing a collection of dummy items from the controller to the View and then binding it to the PivotGrid via the BindTo method to that collection and defining a dummy data collection in the View itself and then binding the PivotGrid using the BindTo method to this collection achieves the same result.

The @Url.Action("Customers_Read", "PivotGrid") in the ajax call points to the endpoint used for the Remote Binding example for the PivotGrid. The source is available for review when clicking on the View Source tab.

     public partial class PivotGridController : BaseController
    {
        private IProductService productService;

        public PivotGridController(
            IProductService service)
        {
            productService = service;
        }

        public ActionResult Customers_Read([DataSourceRequest]DataSourceRequest request)
        {
            return Json(GetCustomers().ToDataSourceResult(request));
        }

        private static IEnumerable<CustomerViewModel> GetCustomers()
        {
            using (var northwind = new SampleEntitiesDataContext())
            {
                return northwind.Customers.Select(customer => new CustomerViewModel
                {
                    CustomerID = customer.CustomerID,
                    CompanyName = customer.CompanyName,
                    ContactName = customer.ContactName,
                    ContactTitle = customer.ContactTitle,
                    Address = customer.Address,
                    City = customer.City,
                    Region = customer.Region,
                    PostalCode = customer.PostalCode,
                    Country = customer.Country,
                    Phone = customer.Phone,
                    Fax = customer.Fax,
                    Bool = customer.Bool
                }).ToList();
            }
        }
    }

That said, the 400 status code indicates that the server cannot or will not process the request due to something that is perceived to be a client error, though without details on the actual action method implementation I cannot provide details on why this might be happening.  

If the above is not helpful and the issue persists consider providing a runnable sample application where the behavior is isolated, either in this thread or in a support ticket, so I can run, debug locally and advise further.

[Taras]

Ticket # 1605280 has been created

[Aleksandar]

I have responded to the ticket opened and I'm sharing here the solution, for the benefit of the community. The reason for the 404 error was a call to an incorrect endpoint. Once resolved and the endpoint is called as expected, a collection is returned and it can be set as the PivotGrid data, as demonstrated previously.

[Taras]

Solution worked.  For anyone viewing this, the correct ajax call should be

    function reloadData() {
        var pivot = $("#pivotgrid").getKendoPivotGrid();
        $.ajax({
            url: "@Url.Action("_ReadData", "ResponseReportPivot")",
            type: "POST",
            success: function (response) {
                pivot.dataSource.data(response)
            }
        })
    }