Pivot Data and Roll up 12 months of Summaries

The need was for a report that rolled up sales by item by month for the year on the MVC Razor page.  Here is how I did it.

First I created a Model that looked like this

  public  class DTO_SkuReport
    {
        public string SKU { get; set; }
        public int Month { get; set; }
        [DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:c}")]
        public decimal linetotal { get; set; }
        public decimal JanTotal { get; set; }
        public decimal FebTotal { get; set; }
        public decimal MarTotal { get; set; }
        public decimal AprTotal { get; set; }
        public decimal MayTotal { get; set; }
        public decimal JunTotal { get; set; }
        public decimal JulTotal { get; set; }
        public decimal AugTotal { get; set; }
        public decimal SepTotal { get; set; }
        public decimal OctTotal { get; set; }
        public decimal NovTotal { get; set; }
        public decimal DecTotal { get; set; }
    }

Then in my Controller Action I passed in a year

  public ActionResult AnnualSkuReport(string year)
        {
            int yr = Convert.ToInt32(year);
            ViewBag.year = year;
            List<DTO_SkuReport> model = new List<DTO_SkuReport>();
            model = orderprovider.GetAnnualSKUReport(yr).ToList();
            return View(model);

        }

My Linq looks like this:

      public List<DTO_SkuReport> GetAnnualSKUReport(int year)
        {

            var rptOrders = (from e in DGIEntities.orderdetails
                             join o in DGIEntities.orderheader on e.OrderID equals o.OrderID
                             where o.OrderDate.Value.Year == year
                             group e by new { e.PRoductname, month = o.OrderDate.Value.Month } into ord
                             select new DTO_SkuReport
                             {
                                 SKU = ord.Key.PRoductname,
                                 Month = ord.Key.month,
                                 linetotal = (decimal)ord.Sum(e => e.price)


                             });
       
Here I traversed through the data turning it into a Pivot of the original data

            List<DTO_SkuReport> listofRecords = new List<DTO_SkuReport>();
            foreach (var i in rptOrders.GroupBy(p=>p.SKU))
            { DTO_SkuReport s = new DTO_SkuReport();
                s.SKU = i.Key;
                foreach(var x in i.Where(m => m.Month == 1)) { s.JanTotal = x.linetotal;}

                foreach (var x in i.Where(m => m.Month == 2)) { s.FebTotal = x.linetotal; }
                foreach (var x in i.Where(m => m.Month ==3)) { s.MarTotal = x.linetotal; }
                foreach (var x in i.Where(m => m.Month == 4)) { s.AprTotal = x.linetotal; }
                foreach (var x in i.Where(m => m.Month == 5)) { s.MayTotal = x.linetotal; }
                foreach (var x in i.Where(m => m.Month == 6)) { s.JunTotal= x.linetotal; }
                foreach (var x in i.Where(m => m.Month == 7)) { s.JulTotal = x.linetotal; }
                foreach (var x in i.Where(m => m.Month == 8)) { s.AugTotal = x.linetotal; }
                foreach (var x in i.Where(m => m.Month == 9)) { s.SepTotal = x.linetotal; }
                foreach (var x in i.Where(m => m.Month == 10)) { s.OctTotal = x.linetotal; }
                foreach (var x in i.Where(m => m.Month == 11)) { s.NovTotal = x.linetotal; }
                foreach (var x in i.Where(m => m.Month == 12)) { s.DecTotal = x.linetotal; }
                listofRecords.Add(s);
            }
            return listofRecords;
        }

And Finally I displayed on the page using a WebGrid (you know how I love that webgrid!)