Monthly Sales By Category
Razor
string columnTemplate = "case when strftime('%m', OrderDate) = '{month}' then (Invoices.UnitPrice*Quantity) end as period{month}";
string fromPart = "Invoices join Products on Products.ProductID = Invoices.ProductId";
var columns = new List() { "CategoryId" };
var labels = new List() { "Category" };
for (int month = 1; month <= 12; month++)
{
columns.Add(columnTemplate.Replace("{month}", month.ToString("00")));
labels.Add(CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(month));
}
DbNetGridCore productSalesByCategoryGrid = new DbNetGridCore("northwind", fromPart, "salesGrid");
productSalesByCategoryGrid.Columns = columns;
productSalesByCategoryGrid.Labels = labels;
productSalesByCategoryGrid.GroupBy = true;
for (int month = 1; month <= 12; month++)
{
productSalesByCategoryGrid.Column($"period{month.ToString("00")}").Format("c").DataType(typeof(Decimal)).Aggregate(AggregateType.Sum);
}
productSalesByCategoryGrid.Column("CategoryId").Lookup(new Lookup("Categories", "CategoryId", "CategoryName"));
productSalesByCategoryGrid.GoogleChartOptions = new GoogleChartOptions() { Type = GoogleChartType.LineChart, PanelId = "chartarea", FunctionName = "chartOptions" };
@productSalesByCategoryGrid.Render()