This is a migrated thread and some comments may be shown as answers.

Column Sorting A-Z

20 Answers 280 Views

PivotGrid

This is a migrated thread and some comments may be shown as answers.

20 Answers, 1 is accepted

answered on 05 Apr 2018, 02:28 PM

Hi Lee,

Indeed, during my tests, I was not able to achieve the desired behavior by using the MVC wrappers. I will need additional time to investigate why there is no sort property in the dataSource definition. Having said that, I will update this forum as soon as I have any further information.

For the time being, you could use the following JavaScript workaround - handle the first dataBound event of the PivotGrid and manually sort it. For example:

<script>

$(document).ready(function () {

$("#pivotgrid").data("kendoPivotGrid").one("dataBound", function (e) {

e.sender.dataSource.sort({ field: "[Product].[Category]", dir: "desc" });

})

});

</script>

Regards,
Preslav
Progress Telerik

Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.

answered on 06 Apr 2018, 10:38 AM

Hello Lee,

Generally speaking, the PivotGrid has a dataBound event:

https://docs.telerik.com/kendo-ui/api/javascript/ui/pivotgrid/events/databound

However, here I believe that the problem comes from the fact that you are using AJAX binding. I assume that you are using flat data, please correct me if I am wrong. For the time being, Sorting is supported only in OLAP binding. More information about this is available here:

https://docs.telerik.com/kendo-ui/controls/data-management/pivotgrid/overview#sorting

Regards,
Preslav
Progress Telerik

Try our brand new, jQuery-free Angular components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.

answered on 08 Nov 2020, 01:40 PM

One more thing to mention is that I am using Kendo MVC PivotGrid binding a flat datasource, and trying to sort based on both the columns(area and product) and rows(country and state).

@(Html.Kendo().PivotGrid<MyProject.Web.ViewModels.Transaction>()

.Name("pivotgrid")

//.HtmlAttributes(new { @class = "hidden-on-narrow" })

.Filterable(true)

.ColumnWidth(65)

.Height(585)

.DataSource(dataSource => dataSource

.Ajax()

.Transport(transport => transport.Read(read => read.Action("Transaction_Read", "Home").Data("SearchValues")))

.Schema(schema => schema

.Cube(cube => cube

.Dimensions(dimensions =>

{

dimensions.Add(model => model.Product).Caption("Products");

dimensions.Add(model => model.Area).Caption("Areas");

dimensions.Add(model => model.Country).Caption("Countries");

dimensions.Add(model => model.State).Caption("States");

})

.Measures(measures => measures.Add("Quantity Sum").Field(model => model.Quantity).AggregateName("sum").Format("{0:n2}"))

))

.Columns(columns =>

{

columns.Add("Area").Expand(true);

columns.Add("Product");

})

.Rows(rows =>

{

rows.Add("Country").Expand(true);

rows.Add("State");

})

.Measures(measures => measures.Values("Quantity Sum"))

)

answered on 11 Nov 2020, 10:36 AM

Hi Preslav,

I updated kendo to version 2020.3.1021 and it did allow me to apply an initial sort on MVC pivot grid.

However, I it seems that I can't sort the columns and rows at the same time.

Below is the ViewModel and the MVC pivotGrid binding Ajax data. How should I sort based on two dimensions?

public class TransactionViewModel

{

public string Category { get; set; }

public string Product { get; set; }

public string Country { get; set; }

public string State { get; set; }

public decimal? Quantity { get; set; }

public int CategorySortOrder { get; set; }

public int ProductSortOrder { get; set; }

public int CountrySortOrder { get; set; }

public int StateSortOrder { get; set; }

}

@(Html.Kendo().PivotGrid<MyProject.Web.ViewModels.TransactionViewModel>()

.Name("pivotgrid")

.Filterable(true)

.ColumnWidth(65)

.Height(585)

.DataSource(dataSource => dataSource

.Ajax()

.Transport(transport => transport.Read(read => read.Action("Transaction_Read", "Home").Data("SearchValues")))

.Schema(schema => schema

.Cube(cube => cube

.Dimensions(dimensions =>

{

dimensions.Add(model => model.Category).Caption("Categories");

dimensions.Add(model => model.Product).Caption("Products");

dimensions.Add(model => model.Country).Caption("Countries");

dimensions.Add(model => model.State).Caption("States");

})

.Measures(measures => measures.Add("Quantity Sum").Field(model => model.Quantity).AggregateName("sum").Format("{0:n2}"))

))

.Columns(columns =>

{

columns.Add("Category").Expand(true);

columns.Add("Product");

})

.Rows(rows =>

{

rows.Add("Country").Expand(true);

rows.Add("State");

})

.Measures(measures => measures.Values("Quantity Sum"))

.Sort(sort =>

{

sort.Add("CategorySortOrder").Ascending(); //We have a specific order requirement for them

sort.Add("ProductSortOrder").Ascending();

sort.Add("CountrySortOrder").Ascending();

sort.Add("StateSortOrder").Ascending();

}

)

answered on 13 Nov 2020, 11:07 AM

Hello Grace,

I tested the scenario with the demo from the issue - https://demos.telerik.com/aspnet-mvc/pivotgrid

I modified the code to:

@(Html.Kendo().PivotGrid()
    .Name("pivotgrid")
    .ColumnWidth(200)
    .Height(580)
    .HtmlAttributes(new { @class = "hidden-on-narrow" })
    .Filterable(true)
    .Sortable()
    .Configurator("#configurator")
    .DataSource(dataSource => dataSource.
        Xmla()
        .Columns(columns => {
            columns.Add("[Date].[Calendar]").Expand(true);
            columns.Add("[Product].[Category]").Expand(true);
        })
        .Rows(rows => rows.Add("[Geography].[City]").Expand(true))
        .Sort(sort =>
        {
            sort.Add("[Product].[Category]").Descending();
            sort.Add("[Geography].[City]").Descending();
        })
        .Measures(measures => measures.Values(new string[]{"[Measures].[Reseller Freight Cost]"}))
        .Transport(transport => transport
            .Connection(connection => connection
                .Catalog("Adventure Works DW 2008R2")
                .Cube("Adventure Works"))
            .Read(read => read
                .Url("https://demos.telerik.com/olap/msmdpump.dll")
                .DataType("text")
                .ContentType("text/xml")
                .Type(HttpVerbs.Post)
            )
        )
        .Events(e => e.Error("onError"))
    )
)

The sorting seems to be working on my side:

Having said that, based on the provided information, I am not sure what could be causing the described faulty behavior. Could you please send me a sample project that replicates the issue? I will use this project to fully understand the case, and provide assistance to the best of my knowledge.

I look forward to your reply.

Regards,
Preslav
Progress Telerik

Virtual Classroom, the free self-paced technical training that gets you up to speed with Telerik and Kendo UI products quickly just got a fresh new look + new and improved content including a brand new Blazor course! Check it out at https://learn.telerik.com/.

answered on 15 Nov 2020, 10:10 PM

Hi Preslav,

Sorry, you example is binding OLAP data and my question was regarding Ajax binding. Anyway, I've figured out why. The sorting feature of MVC pivotGrid is only available from the latest release 2020.3.1021, and when it sorts, it can only work on the dimension columns other than additional columns. In my situation, I had Category, Country, State and Product as dimensions and tried to sort based on CategorySortOrder etc. PivotGrid doesn't support this kind of sorting, which makes sense considering Kendo is an UI framework so it only 'manipulates' what is actually on the screen.

Thanks,

Grace