9/27/2013

Using LINQ to get roll-up data from SharePoint List (DataSet, DataTable, Excel, etc.)

Using LINQ to get roll-up data from SharePoint List (DataSet, DataTable, Excel, etc.)
Hey Guys,
You may find lots of topics and questions posted by [Me] onto the internet for this particular question.

How to use LINQ to get roll-up data?

So, here is the scenario.

I have one SharePoint List as below (you can make DataSet, DataTable, Excel anything you want)

ColumnA     ColumnB      Status
Project1      Task1.1           Completed
Project1      Task1.2           Pending
Project1      Task1.3           Completed
Project2      Task2.1           Completed
Project3      Task3.1           Completed
Project3      Task3.2           Completed
Project3      Task3.3           Completed
Project3      Task3.4           Pending
Project3      Task3.5           Pending

What I need from the above data is:

Project1 66.67 ((Completed Task / Total Task) * 100)
Project2 100
Project3 60

So basically, all I am looking for is rolling up the data and finding the total percentage of project completion.

Easy, huh?

Let's see, so here's the code: (Thanks to Paul Choquette)

var returndata = (from geo in dt.AsEnumerable()
orderby geo["ColumnA"] descending
group geo by geo["ColumnA"] into itemgroup
select new
{
Project = itemgroup.Key,
Percentage = (double)itemgroup.Count(i => (string)i["Status"] == "Complete") / (double)itemgroup.Count() * 100,
}).ToList();

That's it!!

Let me know in case you want anything more from this code ;)

Have fun!!