data table calculation?

Question

Hi All,

I have a below data table,

class Program
    {
        static void Main(string[] args)
        {
            // Get the DataTable.
            DataTable table = GetTable();
        }

        static DataTable GetTable()
        {
            DataTable table = new DataTable();
            table.Columns.Add("GRP_ID", typeof(int));
            table.Columns.Add("SEQ", typeof(string));
            table.Columns.Add("NAME", typeof(string));
            table.Columns.Add("PROJ_ID", typeof(int));
            table.Columns.Add("A_COST", typeof(decimal));
            table.Columns.Add("A_Actual", typeof(decimal));
            table.Columns.Add("B_COST", typeof(decimal));
            table.Columns.Add("B_Actual", typeof(decimal));

            table.Rows.Add(1, "1.0", "A1", 9001, 20, 30, 40, 10);
            table.Rows.Add(1, "1.0", "A1", 9002, 40, 20, 50, 10);
            table.Rows.Add(2, "4.0", "B1", 9003, 50, 20, 70, 50);
            return table;
        }
    }

I need to show output like below, sum of cost base on GRP_ID and then Grand Total within C# code, please help...

1. TOTAL_COST = A_COST + B_COST

2. TOTAL_ACTUAL = A_ACTUAL + B_ACTUAL

3. % DIFF = ((TOTAL_COST - TOTAL ACTUAL)/TOTAL_COST) * 100

Answer 1

Add new column "TOTAL_COST"

Set table.Columns["TOTAL_COST "].Expression property with required expression.

---

Ashutosh Tripathi

Answer 2

thanx for reply.

could you please focus more?

mainly how to loop under loop...i think i need to do multi level looping...help me here

i don't want full 100% solution, just guidline with some line of code.

Answer 3

Yazdani : This is not 100% solution but does this help ?

         
            var A_COST = table.AsEnumerable().Sum(x => x.Field<decimal>("A_COST"));
            var B_COST = table.AsEnumerable().Sum(x => x.Field<decimal>("B_COST"));
            var TOTAL_COST = A_COST + B_COST;

            var A_ACTUAL = table.AsEnumerable().Sum(x => x.Field<decimal>("A_ACTUAL"));
            var B_ACTUAL = table.AsEnumerable().Sum(x => x.Field<decimal>("B_ACTUAL"));
            var TOTAL_ACTUAL = A_ACTUAL + B_ACTUAL;
            var diff = ((TOTAL_COST - TOTAL_ACTUAL) / TOTAL_COST) * 100;

            Console.WriteLine(TOTAL_COST);

Also i did start this but could not complete using linq.. may be this is a partial solution which you can modify as needed to your needs

public static void Main(string[] args)
        {
            DataTable table = GetTable();
 
        
            var query = from x in table.AsEnumerable()   
                        group x  by new 
                            { 
                              PROJ_ID = x["PROJ_ID"],
                              TOTAL_COST =  Convert.ToDecimal( x["A_COST"]) + Convert.ToDecimal(x["B_COST"]),
                              TOTAL_ACTUAL = Convert.ToDecimal(x["A_ACTUAL"]) + Convert.ToDecimal(x["B_ACTUAL"]),
                              NAME = x["SEQ"] + "_" + x["NAME"]
                            } 
                            into G
                            select new 
                             {
                                PROJ_ID= G.Key.PROJ_ID,
                                NAME=   G.Key.NAME,
                                TOTAL_COST=  G.Key.TOTAL_COST,
                                TOTAL_ACTUAL =    G.Key.TOTAL_ACTUAL,
                                DIFF = G.Key.TOTAL_ACTUAL/G.Key.TOTAL_COST * 100
                             };

            DataTable newTable = GetNewTable();
            foreach (var row in query)
            {
                newTable.Rows.Add(row.PROJ_ID, row.NAME, row.TOTAL_COST, row.TOTAL_ACTUAL, row.DIFF);
            }
            List<string> namesList = newTable.AsEnumerable().Select(x => x.Field<string>("NAME")).Distinct().ToList();

            if(newTable.Rows.Count > 0)
            {
                var sumTotal_Cost = newTable.AsEnumerable().Sum(x => x.Field<decimal>("TOTAL_COST"));
                var sumTotal_Actual = newTable.AsEnumerable().Sum(x => x.Field<decimal>("TOTAL_ACTUAL"));
                DataRow totalRow = newTable.Rows.Add(0,"GRAND TOTAL",sumTotal_Cost,sumTotal_Actual,sumTotal_Actual/sumTotal_Cost * 100 );
                newTable.AcceptChanges();
            }
            foreach (var name in namesList)
            {
                DataRow[] objRows = (from row in newTable.AsEnumerable()
                                    where row["NAME"].Equals(name)
                                    select row).ToArray();
                 if (objRows.Length > 0)
                {
                    var subTOTAL_COST = newTable.AsEnumerable().Where(x => x.Field<string>("NAME").Equals(name)).Sum(x => x.Field<decimal>("TOTAL_COST"));
                    var subTOTAL_ACTUAL = newTable.AsEnumerable().Where(x => x.Field<string>("NAME").Equals(name)).Sum(x => x.Field<decimal>("TOTAL_ACTUAL"));
                    DataRow subTotalRows = newTable.Rows.Add(objRows[0]["PROJ_ID"], name + " TOTAL", subTOTAL_COST, subTOTAL_ACTUAL,  subTOTAL_ACTUAL/subTOTAL_COST * 100 );
                    newTable.AcceptChanges();
                 }
            }
            newTable.DefaultView.Sort = "NAME" + " " + "ASC";
            newTable = newTable.DefaultView.ToTable();
            foreach (DataRow row in newTable.Rows)
            {
                Console.WriteLine("{0} {1} {2} {3} {4}", row["NAME"], row["PROJ_ID"].ToString(),row["TOTAL_COST"], row["TOTAL_ACTUAL"], row["DIFF"]);
            }

       }
         static DataTable GetNewTable()
         {
             DataTable table = new DataTable();
             table.Columns.Add("PROJ_ID", typeof(int));
             table.Columns.Add("NAME", typeof(string));
             table.Columns.Add("TOTAL_COST", typeof(decimal));
             table.Columns.Add("TOTAL_ACTUAL", typeof(decimal));
             table.Columns.Add("DIFF", typeof(decimal));           
             return table;
         }

Answer 4

Thank You Very Much for ur detail and nice reply and ur effort...help me a lot...i will able to complete it. Thanks