At work, I was working on cool stuff, but then my boss was like "I need this report and this report and this report. Thanks."
I'm not one to turn down such a politely worded and completely fictitious request. Reports are easy until the requests become stuff like "Include subtotal line for every Apple category and Orange category"
My data set was obviously not Apples and Oranges, but here's what I did to quickly and easily make subtotals for each of these
First, I made some C# Attributes, which are nice when you like to work in the meta.
public class MyReportItem {
[SubtotalGroup(GroupName = "Fruit Type")]
public string FruitType { get; set; }
public string FruitName { get; set; }
[SubtotalSum]
public int Count { get; set; }
[SubtotalAverage]
public int SalesPerDay { get; set; }
[SubtotalSummaryDesignator]
public bool IsSubtotalLine { get; set; }
[TotalDesignator]
public bool IsTotalLine { get; set; }
}
Your SQL might look like this:
select FruitType, FruitName, StockQty, SalesPerDay from Fruits order by FruitType, FruitName
So your data looks like this
'Apple', 'Mcintosh', 12, 80
'Apple', 'Delicious Red', 22, 50
'Orange', 'Some Orange Name', 33, 90
The code I wrote allows that data to be quickly, easily, and automatically shown like this:
'Apple', 'Mcintosh', 12, 80
'Apple', 'Delicious Red', 22, 50
'Apple Subtotal', '', 34, 65
'Orange', 'Some Orange Name', 33, 90
'Orange Subtotal', '', 33, 90
Notice the "SalesPerDay" column has an average attribute on it, not a sum. Here's the meat of my code, after getting the attributes and the data all figured out.
public List<T> PopulateSubtotalItems()
{
List<T> withSubs = new List<T>();
if (this.list.Count == 0) return withSubs;
// allow multiple group by with subtotals. e.g. group by Fruit Name and say fruit type, like "Citrus"
// to subtotal Oranges and subtotal Limes and then subtotal Citrus
List<GroupSub<T>> subs = new List<GroupSub<T>>();
foreach (string key in this.groupBy.Keys)
{
T sub = new T();
GroupSub<T> groupSub = this.groupBy[key];
groupSub.SubRecord = sub;
// sets the properties which designate the group. So this subgroup might set FruitType to "Apple"
this.SetGroup(groupSub, this.list[0]);
// sets the bool property which the subtotal designator is on to true.
this.SetSummary(groupSub);
subs.Add(groupSub);
}
// if there's a bool property with the "TotalDesignator" attribute, include total
GroupSub<T> totals = null;
if (this.includeTotal)
{
T sub = new T();
totals = new GroupSub<T>();
totals.SubRecord = sub;
totals.IsTotal = true;
this.SetTotal(totals); // sets the property which the TotalDesignator is on to true
}
subs = subs.OrderBy(grp => grp.Sequence).ToList();
int grpCount = 0;
for (int i = 0; i < this.list.Count; i++)
{
bool added = false, last = i == this.list.Count - 1;
foreach (GroupSub<T> grp in subs)
{
bool same = SameGroup(grp, this.list[i]);
if (!same)
{
this.Average(grp, grpCount); // set the average properties to the sum / grpCount
withSubs.Add(grp.SubRecord); // add the subtotal record to the group
grpCount = 0; // start afresh
grp.SubRecord = new T();
this.SetSummary(grp);
SetGroup(grp, this.list[i]);
}
Increment(grp, this.list[i]);
if (last) // special handling on the last one.
{
this.Average(grp, grpCount);
if (!added)
{
Increment(totals, this.list[i]);
withSubs.Add(this.list[i]);
added = true;
}
withSubs.Add(grp.SubRecord);
grpCount++;
}
}
if (!added)
{
Increment(totals, this.list[i]);
withSubs.Add(this.list[i]);
added = true;
grpCount++;
}
}
// add the total line
if (this.includeTotal)
{
this.Average(totals, this.list.Count); // average the total record
withSubs.Add(totals.SubRecord);
}
return withSubs; // that's it!!
}
As you can see, I no longer have to dread doing subtotals on reports!