Pivot Grid from SQL data table - assistance required

[Taras]

I'm having some issues with pivot grid and need some assistance. 

I'm trying to create a pivot grid that looks something like this.  It's a simple example that I would be expanding on.  It shows the total commission received from a company by year.

 I've two questions:

• 1) Can I get the data directly from the SQL Table without going into a list
• 2) Why is nothing showing up in the pivot grid

Right now I'm testing in Asp.Net Core .Net 6 and Telerik.UI.for.AspNet.Core 2022.1.301

The model looks like this

    public class CommRecd
    {
        public Guid Id { get; set; }
        public DateTime ReceivedDate { get; set; }
        public string? CompanyName { get; set; }
        public Decimal ReceivedAmount { get; set; }
        public int ReceivedYear { get; set; }
    }

The controller method is below and I've confirmed that objCommList contains the data

        public IActionResult PivotGrid()
        {
            IEnumerable<CommRecd> objCommList = _db.CommRecd;
            return View(objCommList);
        }

I'm basing my code on this example Remote binding in ASP.NET Core PivotGrid Component Demo | Telerik UI for ASP.NET Core with the code shown below.

@using Application.Models;
@model IEnumerable<CommRecd>
@{ ViewBag.Title = "Commission Received Report"; }
@Html.AntiForgeryToken()

<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<CommRecd>()
        .Name("pivotgrid")
        .Configurator("#configurator")
        .ColumnWidth(120)
        .Filterable(true)
        .Height(570)
        .DataSource(dataSource => dataSource
            .Ajax()
            .Schema(schema => schema
                .Cube(cube => cube
                    .Dimensions(dimensions => {
                        dimensions.Add(model => model.CompanyName).Caption("All Companies");
                        dimensions.Add(model => model.ReceivedAmount).Caption("All Amounts");
                        dimensions.Add(model => model.ReceivedYear).Caption("All Years");
                    })
                    .Measures(measures =>
                    {
                        measures.Add("Sum").Format("{0:c}").Field(model => model.ReceivedAmount).AggregateName("sum");
                    })
                ))
            .Columns(columns =>
            {
                columns.Add("ReceivedDate").Expand(true);
            })
            .Rows(rows => rows.Add("CompanyName").Expand(true))
            .Measures(measures => measures.Values("Sum"))
            .Events(e => e.Error("onError"))
        )
    )
</div>
<div class="responsive-message"></div>

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

My output looks like this

Thanks for any suggestions

 

[Alexander]

Hi Taras,

Thank you for the provided screenshot, code, and information.

In regards to your first question:

In general, the DataSource expects an object as an argument rather than an SQL table. Having this in mind, it is required to materialize the data into a collection in order to pass it within the View successfully. Additionally, note that as mentioned in the Data Binding documentation article, when the PivotGrid is bound to flat data, it converts it to a client-side cube. 

In regards to your second question:

Normally, such an issue may be caused by the JSON Serialization configuration that is used for the property name casing of data-bound components. If the serializer changes the casing to camelCase, but PascalCase is inspected instead, the component will not bind to the data, resulting in an unsuccessful attempt to display it.

In addition, I would also recommend making sure if there are any errors logged within the developer's console that could potentially indicate the cause of the encountered behavior. If this is the case, I would recommend making sure that both the client-side files and NuGet package version are corresponding. For example:

	<link href="https://cdn.kendostatic.com/2022.1.301/styles/kendo.bootstrap-main.min.css" rel="stylesheet" type="text/css" />
	<script src="https://cdn.kendostatic.com/2022.1.301/js/jquery.min.js"></script>
	<script src="https://cdn.kendostatic.com/2022.1.301/js/jszip.min.js"></script>
	<script src="https://cdn.kendostatic.com/2022.1.301/js/kendo.all.min.js"></script>
	<script src="https://cdn.kendostatic.com/2022.1.301/js/kendo.aspnetmvc.min.js"></script>

With that said, I have also noticed that the sent implementation of the PivotGrid is configured for local binding rather than remote data binding. In this regard, you can review the Pivot Grid Local Binding Demo that showcases how this can be achieved.

Please give these suggestions a try and let me know how it works out for you.

Regards,
Alexander
Progress Telerik

[Taras]

Hi Alexander,

I started with a new test project and confirmed that all packages match.  There are no errors showing in the Console.  

As to case, I have this in my Program.cs file, which was referenced in the Json serialization link you provided.

builder.Services.AddControllersWithViews()
    // Maintain property names during serialization. See:
    // https://github.com/aspnet/Announcements/issues/194
    .AddNewtonsoftJson(options => options.SerializerSettings.ContractResolver = new DefaultContractResolver());

I'll admit to not having extensive .Net Core knowledge and am beginning to explore .Net 6.
From what I can see the difference between the Local & Remote binding is how data is obtained.  The input to the pivot grid in both cases is an IEnumerable list

@model IEnumerable<Kendo.Mvc.Examples.Models.ProductViewModel> in Local binding
private static IEnumerable<CustomerViewModel> GetCustomers() in Remote binding

So I am unclear as to why I can't create an iEnumerable list like this (that works in an HTML table) and pass it into either the local or remote binding method
 IEnumerable<CommRecdViewModel> objCommList = _db.CommRecd.ToList();

I have tried both local and remote binding (with numerous other issues) and get the same result.

[Alexander]

Hi Taras,

The core difference between the Local Data binding and Remote Data binding is that for the Remote Data binding, the DataSource serves as a mediator between the PivotGrid and the underlying data. Having this in mind, in order to configure it successfully the following needs to be taken into account:

• A parameter of type "Kendo.Mvc.UI.DataSourceRequest" needs to be added to the respective action method from which the data will be fetched. It will contain the current PivotGrid request information.

• The parameter needs to be decorated through the "Kendo.Mvc.UI.DataSourceRequestAttribute". This is required, as the attribute will populate the DataSourceRequest object from the posted data.

• The "ToDataSourceRequest()" extension method needs to be used in order to convert the data to a DataSourceResult object and retrieves the data in JSON format.

The Local Data binding, on the other hand, fetches the data directly into the view by passing in an "IEnumerable" of the model type. From there, the passed in model needs to be bound to the PivotGrid using the .BindTo() configuration API method. For example:

@model IEnumerable<Kendo.Mvc.Examples.Models.ProductViewModel>

<div class="k-pivotgrid-wrapper">
    @(Html.Kendo().PivotGrid<Kendo.Mvc.Examples.Models.ProductViewModel>()
        .Name("pivotgrid")
        ...
        .BindTo(Model)    
    )
</div>

In addition, if issues still persist, could you please elaborate more on what you mean by "numerous other issues". This will help me get a better understanding of the case and provide further guidance. 

[Taras]

So the missing piece in my original code was the .BindTo(Model) directive.

The issue I had in trying to follow the Remote example started was trying to replicate the private static IEnumerable code.  It required additional code that I didn't have in my text app.  I gave up trying to replicate it.

[Alexander]

Could you please confirm whether the encountered issue has been resolved on your side? If not please consider reproducing in a minimal isolated environment and send it back for further investigation.

[Taras]

It has been resolved.  I thought that would have been implied by accepting the answer.

Thanks.