Business object for pivoted data

Business object for pivoted data

Old forum URL: forums.lhotka.net/forums/t/4158.aspx


wjcomeaux posted on Friday, January 11, 2008

Hello all:

Is it possible (or even a good idea) to design business objects that allow for pivoted data? For example, I have a timesheet editable root object. timesheet has an editable root list of timesheetdetails

I've also got a timesheetdetails editable root object with the properties of EmployeeId, Date, HoursWorked.

Binding this to a grid is simple. However, our users want to see a grid that pivots the date across a range of days. For instance, they would load timesheet for a two week period and they want to see a grid where the columns are like this

EmployeeId  | Date1 | HoursWorked1 | Date2 | HoursWorked2 | Total Hours | Other stuff

So, their grid will always have 2 + (2 * numDaysSelected).

How would you go about this with CSLA? We are just doing case studies now so our BOs are still bery simple.

Thanks in advance,

Will

KKoteles replied on Wednesday, January 16, 2008

Will,

Can you 'restrict' the date range choice (like 'Last Week', 'Last 2 Weeks', etc) or is it wide open (Start Date - End Date)?  If you can 'restrict' it, then you should be able to create the associated business object for each selection (since you know the 'properties' you will need to support each grid). 

If you can't restrict it, then I would try to see if I could influence their UI a little.  Make the first grid EmployeeId | Date1 | Date2 | Total Hours | Other stuff.  The second grid would be the 'details' grid for one particular row of the first grid - being EmployeeId | Date | Hours | Other stuff (where there would be one entry listed for every date worked - or even every date within the date range where Hours would be 0 for those dates not worked).

If it truly needs to be dynamic, then I'm at a loss as to how you would accomplish this with a CSLA object...

Ken

pinchers1jm replied on Wednesday, January 16, 2008

Another option is to create a ReadOnlyList of the required properties (EmployeeId, EntryDate, HoursWorked, OtherStuff). The list could then have a function that returns a DataTable. The function would look something like this.

public DataTable GetPivotTable()
{
    string keyColumn = "EmployeeId", nameColumn = "EmployeeName";
    string totalColumn = "Total", otherColumn = "OtherStuff";
    DataTable dt = new DataTable();
 
    dt.Columns.Add(keyColumn, typeof(Guid));
    dt.Columns.Add(nameColumn, typeof(string));
    dt.Columns.Add(otherColumn, typeof(string));
    dt.Columns.Add(totalColumn, typeof(decimal));
    dt.PrimaryKey = new DataColumn[] { dt.Columns[keyColumn] };

    //dt.BeginLoadData();
    foreach (ItemInfo info in this)
    {
        string pivotColumn = info.EntryDate.ToShortDateString();
        if (!dt.Columns.Contains(pivotColumn))
            dt.Columns.Add(pivotColumn, typeof(decimal));

        // Look for primary key
        DataRow row = dt.Rows.Find(info.EmployeeId);
        if (row == null)
        {
            row = dt.NewRow();
            row[keyColumn] = info.EmployeeId;
            row[nameColumn] = info.EmployeeId;
            row[otherColumn] = info.EmployeeId;
            row[totalColumn] = Convert.ToDecimal(row[totalColumn]) + info.HoursWorked;
            row[pivotColumn] = info.HoursWorked;
           
            dt.Rows.Add(row);
        }
        else
            row[pivotColumn] = info.HoursWorked;
    }
    //dt.EndLoadData();

    return dt;
}

KKoteles replied on Friday, January 18, 2008

pinchers1jm,

Interesting solution!  I love the intellect demonstrated daily by those participating in this forum.  There is always more than one way to 'skin a cat' - and I am greatful how everyone here keeps forcing me to think outside the box I keep placing myself in with deadline tunnel vision...

wjcomeaux replied on Friday, January 18, 2008

Thanks guys. We have decided that limiting the timeframe would be easily acceptable. This definitely makes it easier to design.

 

Will

Copyright (c) Marimer LLC