Cross tabulation
 Next   Menu    Previous 

In this example we use the Aggregate column property and built-in database functions to create a cross-tabulation example. It also use the CellTransform client-side event to highlight values over 100
Loading...
Razor code

var invoiceColumns = new List<GridColumn>
{
    new GridColumn("ProductName", "Product")
};

for (var i =1; i<=12; i++)
{
    var monthName = new DateTime(2024, i, 1).ToString("MMMM");
    var column = new GridColumn($"(case when strftime ('%d', OrderDate) = '{i.ToString("00")}' then Quantity else 0 end) as {monthName.ToLower()}", monthName) { Aggregate = AggregateType.Sum, DataType = typeof(Int32) };
    invoiceColumns.Add(column);
}

var invoiceGrid = new GridModel(DataSourceType.SQLite, "Northwind", "invoices");
invoiceGrid.Columns = invoiceColumns;
invoiceGrid.Caption = "Product Sales By Month";
invoiceGrid.ClientEvents[ClientEvent.CellTransform] = "highlightQuantity";
@(await new DbNetSuiteCore.GridControl(HttpContext).Render(invoiceGrid))