Click here to Skip to main content
15,848,264 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi!


I have a DataTable that fetches the data in the below format.

Region Asset Class Dividends PnL Bonus
Asia ex-Japan Bonds 10000 10000 10000
Asia ex-Japan Bonds 20000 20000 20000
Asia ex-Japan Bonds 30000 30000 30000
Asia ex-Japan Bonds 40000 40000 40000
Asia ex-Japan Cash Equities 11500 11500 11500
Asia ex-Japan Cash Equities 20500 20500 20500
Asia ex-Japan Cash Equities 16200 16200 16200
Asia ex-Japan Convertibles 3000 3000 3000
Asia ex-Japan Convertibles 2000 2000 2000
Americas Cash Equities 18000 18000 18000
Americas Cash Equities 17000 17000 17000
Americas Bonds 6600 6600 6600
Americas Bonds 5700 5700 5700
Japan Convertibles 14000 14000 14000
Japan Convertibles 14500 14500 14500


What we need is the total for each group & sub-group within the above DataTable as shown below :

Region Asset Class Dividends PnL Bonus
Asia ex-Japan Bonds 10000 10000 10000
Asia ex-Japan Bonds 20000 20000 20000
Asia ex-Japan Bonds 30000 30000 30000
Asia ex-Japan Bonds 40000 40000 40000
Bonds Total 100000 100000 100000
Asia ex-Japan Cash Equities 11500 11500 11500
Asia ex-Japan Cash Equities 20500 20500 20500
Asia ex-Japan Cash Equities 16200 16200 16200
Cash Equities Total 48200 48200 48200
Asia ex-Japan Convertibles 3000 3000 3000
Asia ex-Japan Convertibles 2000 2000 2000
Convertibles Total 5000 5000 5000
Asia ex-Japan Total 153200 153200 153200
Americas Cash Equities 18000 18000 18000
Americas Cash Equities 17000 17000 17000
Cash Equities Total 35000 35000 35000
Americas Bonds 6600 6600 6600
Americas Bonds 5700 5700 5700
Bonds Total 12300 12300 12300
Americas Total 47300 47300 47300
Japan Convertibles 14000 14000 14000
Japan Convertibles 14500 14500 14500
Convertibles Total 28500 28500 28500
Japan Total 43000 43000 43000


Also, we need to print the above datatable to excel (i.e. we are not using any reporting tool where the totals and sub-totals could be easily computed).

How could we achieve this? Any help would be much appreciated.


Thanks in advance,

Charvi.

What I have tried:

I have tried looping through the datatble & calculating the sum but this seems to work only for the inner group & not the outer group.
Posted
Updated 5-Feb-17 20:13pm

//Try This
var _result = from r1 in DT.AsEnumerable()
                         group r1 by new
                         {
                             RegionAsset = r1.Field<string>("Region Asset"),
                             Class = r1.Field<string>("Class"),


                         } into g
                         select new
                         {
                             RegionAsset = g.Key.RegionAsset,
                             Class = g.Key.Class,
                             TotalDividends = g.Sum(x => x.Field<int>("Dividends")),
                             TotalPnL = g.Sum(x => x.Field<int>("PnL")),
                             TotalBonus = g.Sum(x => x.Field<int>("Bonus"))

                         };


//For Excel Download
 using Excel = Microsoft.Office.Interop.Excel;
public static bool ExportDataTableToExcel(DataTable dt, string filepath)
       {
           Excel.Application oXL;
           Excel.Workbook oWB;
           Excel.Worksheet oSheet;

           try
           {
               // Start Excel and get Application object.
               oXL = new Excel.Application();

               // Set some properties
               oXL.Visible = true;
               oXL.DisplayAlerts = false;

               // Get a new workbook.
               oWB = oXL.Workbooks.Add(Missing.Value);

               // Get the Active sheet
               oSheet = (Excel.Worksheet)oWB.ActiveSheet;
               oSheet.Name = "Data";

               int rowCount = 1;
               foreach (DataRow dr in dt.Rows)
               {
                   rowCount += 1;
                   for (int i = 1; i < dt.Columns.Count + 1; i++)
                   {
                       // Add the header the first time through
                       if (rowCount == 2)
                       {
                           oSheet.Cells[1, i] = dt.Columns[i - 1].ColumnName;
                       }
                       oSheet.Cells[rowCount, i] = dr[i - 1].ToString();
                   }
               }

               oWB.SaveAs(filepath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
                           false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                           Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
               oWB.Close();
               oWB = null;
               oXL.Quit();
           }
           catch
           {
               throw;
           }
           finally
           {
               GC.WaitForPendingFinalizers();
               GC.Collect();
               GC.WaitForPendingFinalizers();
               GC.Collect();
           }
           return true;
       }
 
Share this answer
 
v2
Comments
aakar 26-Jan-17 14:36pm    
Hi Sonymon,
Thanks for the solution.
But this is a legacy application that was built in VS 2008. We cannot use LINQ here. Is there any way we can accomplish this using C#2.0?
sonymon mishra 2-Feb-17 4:02am    
Hey, Sorry I was a bit busy these days, is the issue resolved or you still need the solution. Please let me know.
aakar 5-Feb-17 11:30am    
yes, the solution you provide works like a charm. Just 1 thing though.
I am currently getting tow resultsets :

var _result1 = from r1 in DT.AsEnumerable()
group r1 by new
{
RegionAsset = r1.Field<string>("Region Asset"),
Class = r1.Field<string>("Class"),


} into g
select new
{
RegionAsset = g.Key.RegionAsset,
Class = g.Key.Class,
TotalDividends = g.Sum(x => x.Field<int>("Dividends")),
TotalPnL = g.Sum(x => x.Field<int>("PnL")),
TotalBonus = g.Sum(x => x.Field<int>("Bonus"))

};


var _result2 = from r1 in DT.AsEnumerable()
group r1 by new
{
RegionAsset = r1.Field<string>("Region Asset")

} into g
select new
{
RegionAsset = g.Key.RegionAsset,

TotalDividends = g.Sum(x => x.Field<int>("Dividends")),
TotalPnL = g.Sum(x => x.Field<int>("PnL")),
TotalBonus = g.Sum(x => x.Field<int>("Bonus"))

};

Now, how do I iterate through the above two vars (i.e. _result1 and _result2)
and get the o/p in the desired format.
The second var i.e. _result2 has the information grouped at a higher level whereas the first var i.e. _result1 has the information grouped one level down.

I would require the outer group total first in excel then followed by the inner group total. Any ideas?
sonymon mishra 6-Feb-17 0:29am    
Sorry, I got a little confused. Please provide me the desired output.
aakar 6-Feb-17 0:54am    
Region Asset Class Dividends PnL Bonus
Asia ex-Japan Bonds 10000 10000 10000
Asia ex-Japan Bonds 20000 20000 20000
Asia ex-Japan Bonds 30000 30000 30000
Asia ex-Japan Bonds 40000 40000 40000
Bonds Total 100000 100000 100000
Asia ex-Japan Total 153200 153200 153200

The o/p is required in the above format for each Region (i.e the outer group total) and the Asset Class (i.e the inner group total)

Hope I am clear...
//Hope This works with your previous code
var _result1 = (from r1 in DT.AsEnumerable()
                           group r1 by new
                           {
                               RegionAsset = r1.Field<string>("Region Asset"),
                               Class = r1.Field<string>("Class"),

                           } into g
                           select new
                           {
                               RegionAsset = g.Key.RegionAsset,
                               Class = g.Key.Class,
                               TotalDividends = g.Sum(x => x.Field<int>("Dividends")),
                               TotalPnL = g.Sum(x => x.Field<int>("PnL")),
                               TotalBonus = g.Sum(x => x.Field<int>("Bonus"))

                           }).ToList();


           var _result2 = (from r1 in DT.AsEnumerable()
                           group r1 by new
                           {
                               RegionAsset = r1.Field<string>("Region Asset")

                           } into g
                           select new
                           {
                               RegionAsset = g.Key.RegionAsset,

                               TotalDividends = g.Sum(x => x.Field<int>("Dividends")),
                               TotalPnL = g.Sum(x => x.Field<int>("PnL")),
                               TotalBonus = g.Sum(x => x.Field<int>("Bonus"))

                           }).ToList();

           DataTable DTResult = DT.Clone();
           foreach (var r1 in _result1)
           {
               foreach (DataRow dr in DT.AsEnumerable().Where(p => p.Field<string>("Region Asset") == r1.RegionAsset && p.Field<string>("Class") == r1.Class))
               {
                   DataRow dtrow = DTResult.NewRow();
                   dtrow.ItemArray = new String[] { r1.RegionAsset, r1.Class, dr.Field<int>("Dividends").ToString(), dr.Field<int>("Dividends").ToString(), dr.Field<int>("Dividends").ToString() };
                   DTResult.Rows.Add(dtrow);
               }
               DataRow dtrow1 = DTResult.NewRow();
               dtrow1.ItemArray = new String[] { r1.Class + "Total", "", r1.TotalDividends.ToString(), r1.TotalPnL.ToString(), r1.TotalBonus.ToString() };
               DTResult.Rows.Add(dtrow1);

           }
 
Share this answer
 
Comments
aakar 9-Feb-17 8:41am    
Hi Sonymon,

Thanks for the solution. It works fine, however I require 2 levels of Totals
i.e. Region Asset is Asia ex-Japan and Class is Bonds.
Therefore I would require both the Class Total as well as the Region Asset Total.

Your solution helped me get the Class Total across my dataset. How do I get the Region Asset Total as well?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900