MVC Pivot Results - PivotGrid or Regular Grid

[Adam]

Hi,

I'm looking for some guidance on the best way to pivot data that is being returned from a .NET MVC controller. I'm able to read it into the regular grid by doing the following :

 

 @(Html.Kendo().Grid<Project.Models.viewMyModel>()
    .Name("grid")
    .ToolBar(tools => tools.Excel())
    .Columns(columns =>
    {

        columns.Bound(p => p.Field1).Width("auto");
        columns.Bound(p => p.Field2).Width("auto");
        columns.Bound(p => p.Field3).Width("auto");
        columns.Bound(p => p.Field4).Width("auto");

    })
       .Excel(excel => excel
       .FileName("Report1.xlsx")
       .ProxyURL("Report?handler=Save")
       .AllPages(true)
       )
    .Height(550)
    .AutoBind(false)
    .Pageable() 
    .Sortable()
    .Scrollable()
    .Resizable(r => r.Columns(true))
    .Filterable()
    .DataSource(dataSource => dataSource
    .Ajax()
    .Read(read => read.Action("GetSummary", "Reports").Data("getParams"))
    .PageSize(100)
    .ServerOperation(false)
    )
    .Events(ev => ev.ExcelExport("onExcelExport"))
    .NoRecords(n => n.TemplateId("no-records-template-id"))
    )

The form post submits some date parameters to the 'GetSummary' controller and returns the json data source for the grid.

I would like to pivot these results so that Field1 results are my column headers and the other fields are my rows. Would PivotGrid be a good option or is that overkill? If so - I wasn't sure how to get it to read the json results as as I did with the grid above. Or would you suggest I pivot/manipulate the data beforehand and continue to read with the traditional grid method?

Thanks,
Adam

     

[Ivan Danchev]

Hi Adam,

Using the PivotGrid might be an overkill, but it would depend on how you want the data to be visualized. I would suggest trying out the PivotGrid configuration posted below, and you can compare it to the way the Grid visualizes the data.

Let's have the following view model with a few fields:

public class CustomerViewModel
{
    public string CustomerID { get; set; }
    public string CompanyName { get; set; }
    public string ContactName { get; set; }
    public string City { get; set; }
    public string Country { get; set; }
}

The PivotGrid's declaration that specified CompanyName as a column and the rest of the fields as rows:

@(Html.Kendo().PivotConfigurator()
	.HtmlAttributes(new { @class = "hidden-on-narrow" })
	.Name("configurator")
	.Filterable(true)
	.Height(570)
)

@(Html.Kendo().PivotGrid<PopupTest.Models.CustomerViewModel>()
	.Name("pivotgrid")
	.Filterable(true)
	.Configurator("#configurator")
	.ColumnWidth(120)
	.Height(570)
	.DataSource(dataSource => dataSource
		.Ajax()
		.Transport(transport => transport.Read("Customers_Read", "Home"))
		.Schema(schema => schema
			.Cube(cube => cube
				.Dimensions(dimensions =>
				{
					dimensions.Add(model => model.CompanyName).Caption("All Companies");
					dimensions.Add(model => model.ContactName).Caption("All Contacts");
					dimensions.Add(model => model.Country).Caption("All Countries");
					dimensions.Add(model => model.City).Caption("All Cities");
				})
				.Measures(measures => measures.Add("Contacts Count").Field(model => model.CustomerID).AggregateName("count"))
			))
		.Columns(columns =>
		{
			columns.Add("CompanyName").Expand(true);
		})
		.Rows(rows =>
		{
			rows.Add("ContactName").Expand(true);
			rows.Add("Country").Expand(true);
			rows.Add("City").Expand(true);
		})
		.Measures(measures => measures.Values("Contacts Count"))
	)
)

The Customers_Read action in the Home controller that populates the PivotGrid with exemplary data:

public ActionResult Customers_Read([DataSourceRequest]DataSourceRequest request)
{
    List<CustomerViewModel> data = new List<CustomerViewModel>()
    {
        new CustomerViewModel()
        {
            CustomerID = "1",
            CompanyName = "BMW",
            ContactName = "John Doe",
            Country = "Germany",
            City = "Munich"
        },
        new CustomerViewModel()
        {
            CustomerID = "2",
            CompanyName = "Audi",
            ContactName = "Jane Doe",
            Country = "Germany",
            City = "Ingolstadt"
        },
        new CustomerViewModel()
        {
            CustomerID = "3",
            CompanyName = "Ford",
            ContactName = "Johny Doe",
            Country = "USA",
            City = "Dearborn"
        },
        new CustomerViewModel()
        {
            CustomerID = "4",
            CompanyName = "Toyota",
            ContactName = "Jenny Doe",
            Country = "Japan",
            City = "Toyota"
        },
    };

    return Json(data.ToDataSourceResult(request));
}

Regards,
Ivan Danchev
Progress Telerik