تهيه گزارشات Crosstab به كمك LINQ

در گزارشات Crosstab، رديف‌هاي يك گزارش، تبديل به ستون‌هاي آن مي‌شوند؛ به همين جهت به آن‌ها Pivot tables هم مي‌گويند.
براي مثال فرض كنيد كه قصد داريد گزارش تعداد ساعت كاركرد را به ازاي هر پروژه در طول چند ماه تعيين كنيد. گزارش متداول از اين نوع اطلاعات، يك ليست بلند بالاي بي‌مفهوم است. اين گزارش تشكيل شده از صدها ركورد به ازاي كاركنان مختلف در پروژه‌هاي مختلف و ... هيچ ارزش آماري خاصي ندارد. يك گزارش بدوي است. زمانيكه اين گزارش را تبديل به حالت crosstab مي‌كنيم، اولين ستون فقط يك شماره پروژه خواهد بود و ستون‌هاي بعدي، مثلا نام ماه‌ها و مقادير آن‌ها هم جمع كاركرد افراد بر روي يك پروژه مشخص.

مثال اول) تهيه گزارش Crosstab جمع هزينه‌هاي واحدهاي مختلف به تفكيك ماه

كلاس هزينه‌هاي زير را در نظر بگيريد كه به كمك آن مي‌توان به ازاي هر واحد يا دپارتمان در تاريخ‌هاي متفاوت، هزينه‌اي را مشخص ساخت:

using System;

namespace Pivot.Sample1
{
    public class Expense
    {
        public DateTime Date { set; get; }
        public string Department { set; get; }
        public decimal Expenses { set; get; }
    }
}

با توجه به اين كلاس، يك منبع داده آزمايشي جهت تهيه گزارشات، مي‌تواند به صورت زير باشد:

using System;
using System.Collections.Generic;

namespace Pivot.Sample1
{
    public class ExpenseDataSource
    {
        public static IList<Expense> ExpensesDataSource()
        {
            return new List<Expense>
            {
                new Expense { Date = new DateTime(2011,11,1), Department = "Computer", Expenses = 100 },
                new Expense { Date = new DateTime(2011,11,1), Department = "Math", Expenses = 200 },
                new Expense { Date = new DateTime(2011,11,1), Department = "Physics", Expenses = 150 },

                new Expense { Date = new DateTime(2011,10,1), Department = "Computer", Expenses = 75 },
                new Expense { Date = new DateTime(2011,10,1), Department = "Math", Expenses = 150 },
                new Expense { Date = new DateTime(2011,10,1), Department = "Physics", Expenses = 130 },

                new Expense { Date = new DateTime(2011,9,1), Department = "Computer", Expenses = 90 },
                new Expense { Date = new DateTime(2011,9,1), Department = "Math", Expenses = 95 },
                new Expense { Date = new DateTime(2011,9,1), Department = "Physics", Expenses = 100 }
            };
        }
    }
}

و اگر اين ليست را به همين شكلي كه هست نمايش دهيم، خروجي زير را خواهيم داشت:

كه ... خروجي مطلوبي نيست. در اينجا ما فقط 9 ركورد داريم؛ اما در عمل به ازاي هر روز، يك ركورد مي‌تواند وجود داشته باشد و اين ليست طولاني، هيچ ارزش آماري خاصي ندارد. مي‌خواهيم سرستون‌هاي گزارش ما مطابق جدول زير باشند:

يعني اگر سه ماه را در نظر بگيريم با هر تعداد ركورد، فقط سه رديف به ازاي هر ماه بايد حاصل شود و ستون‌هاي ديگر هم نام بخش‌ها يا واحدهاي موجود باشند.
براي رسيدن به اين خروجي Crosstab، مي‌توان كوئري LINQ زير را به كمك امكانات گروه بندي اطلاعات آن تهيه كرد:

using System.Collections;
using System.Linq;

namespace Pivot.Sample1
{
    public class PivotTable
    {
        public static IList ExpensesCrossTab()
        {
            return ExpenseDataSource
                        .ExpensesDataSource()
                        .GroupBy(t =>
                                   new
                                   {
                                       Year = t.Date.Year,
                                       Month = t.Date.Month
                                   })
                        .Select(myGroup =>
                                   new
                                   {
                                       //Year = myGroup.Key.Year,
                                       Month = myGroup.Key.Month,
                                       ComputerDepartment = myGroup.Where(x => x.Department == "Computer").Sum(x => x.Expenses),
                                       MathDepartment = myGroup.Where(x => x.Department == "Math").Sum(x => x.Expenses),
                                       PhysicsDepartment = myGroup.Where(x => x.Department == "Physics").Sum(x => x.Expenses)
                                   })
                        .ToList();
        }
    }
}

كه اينبار خروجي زير را توليد مي‌كند.

اگر علاقمند باشيد كه مثال فوق را در برنامه‌ي LINQPad آزمايش كنيد، اين فايل را دريافت نموده و در آن برنامه باز نمائيد.


مثال دوم) تهيه ليست Crosstab حضور و غياب افراد در طول يك هفته

كلاس StudentStat را جهت ثبت اطلاعات حضور يك دانشجو، مي‌توان به شكل زير تعريف كرد:

using System;

namespace Pivot.Sample2
{
    public class StudentStat
    {
        public int Id { set; get; }
        public string Name { set; get; }
        public DateTime Date { set; get; }
        public bool IsPresent { set; get; }
    }
}

و بر همين اساس يك منبع داده فرضي جهت انجام گزارشات مي‌تواند به نحو زير تهيه شود:

using System;
using System.Collections.Generic;

namespace Pivot.Sample2
{
    public class StudentsStatDataSource
    {
        public static IList<StudentStat> CreateMonthlyReportDataSource()
        {
            var result = new List<StudentStat>();
            var rnd = new Random();

            for (int day = 1; day < 6; day++)
            {
                for (int student = 1; student < 6; student++)
                {
                    result.Add(new StudentStat
                    {
                        Id = student,
                        Date = new DateTime(2011, 11, day),
                        IsPresent = rnd.Next(-1, 1) == 0 ? true : false,
                        Name = "student " + student
                    });
                }
            }

            return result;
        }
    }
}

خروجي اين گزارش هم در اين حالت ساده با 5 دانشجو و فقط 5 روز، 25 ركورد خواهد بود:

كه ... اين هم آنچنان از لحاظ آماري مطلوب و مفهوم نيست. مي‌خواهيم سطرهاي اين گزارش همانند ليست واقعي حضورغياب، فقط از نام افراد تشكيل شود و همچنين ستون‌ها مثلا شماره يا نام روزهاي يك هفته يا ماه باشند. مثلا به شكل زير:

براي رسيدن به اين خروجي Crosstab، مثلا مي‌توان از كوئري LINQ زير كمك گرفت كه بر اساس شماره دانشجويي اطلاعات را گروه بندي كرده است:

using System.Collections;
using System.Linq;

namespace Pivot.Sample2
{
    public class PivotTable
    {
        public static IList StudentsStatCrossTab()
        {
            return StudentsStatDataSource
                        .CreateWeeklyReportDataSource()
                        .GroupBy(x =>
                                  new
                                  {
                                      x.Id
                                  })
                        .Select(myGroup =>
                                  new
                                  {
                                      myGroup.Key.Id,
                                      Name = myGroup.First().Name,
                                      Day1IsPresent = myGroup.Where(x => x.Date.Day == 1).First().IsPresent,
                                      Day2IsPresent = myGroup.Where(x => x.Date.Day == 2).First().IsPresent,
                                      Day3IsPresent = myGroup.Where(x => x.Date.Day == 3).First().IsPresent,
                                      Day4IsPresent = myGroup.Where(x => x.Date.Day == 4).First().IsPresent,
                                      Day5IsPresent = myGroup.Where(x => x.Date.Day == 5).First().IsPresent,
                                      PresentsCount = myGroup.Where(x => x.IsPresent).Count(),
                                      AbsentsCount = myGroup.Where(x => !x.IsPresent).Count()
                                  })
                        .ToList();
        }
    }
}

و اين كوئري خروجي زير را توليد مي‌كند كه از هر لحاظ نسبت به ليست قبلي مفهوم‌تر است:

فايل LINQPad اين مثال را مي‌توانيد از اينجا دريافت كنيد.