Devexpress Help to create This Report

Question

I have two Table one is Target and another is Table sales

Table Target
+------+--------------+--------+
|  ID  |Category Name | Target |
+------+--------------+--------+
|  1   | AAAA         | 15000  |
|  2   | BBBB         | 50000  |
|  3   | CCCC         | 20000  |
|  4   | DDDD         | 40000  |
|  5   | EEEE         | 30000  |
+------+--------------+--------+

Table Sales

+------+---------+--------------+--------+
|  ID  |  Date   |Category Name | Sale   |
+------+---------+--------------+--------+
|  1   | 01/01/20| AAAA         | 5000   |
|  2   | 01/01/20| BBBB         | 1000   |
|  3   | 02/01/20| CCCC         | 2000   |
|  4   | 03/01/20| DDDD         | 4000   |
|  5   | 03/01/20| EEEE         | 3000   |
|  6   | 03/01/20| AAAA         | 1000   |
|  7   | 05/01/20| EEEE         | 3000   |
|  8   | 06/01/20| BBBB         | 3000   |
|  9   | 02/01/20| CCCC         | 1000   |
| 10   | 02/01/20| DDDD         | 2000   |
+------+---------|--------------+--------+

I want to create following report from above table. How to create this report by devexpress.

+----------+--------------+------+
|  Target  |Category Name | Sale |
+----------+--------------+------+
|    15000 | AAAA         | 6000 |
|    50000 | BBBB         | 4000 |
|    20000 | CCCC         | 3000 |
|    40000 | DDDD         | 6000 |
|    30000 | EEEE         | 6000 |
+----------+--------------+------+

Answer 1

Try following :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable targetTable = new DataTable();
            targetTable.Columns.Add("ID", typeof(int));
            targetTable.Columns.Add("Category Name", typeof(string));
            targetTable.Columns.Add("Target", typeof(int));

            targetTable.Rows.Add(new object[] { 1, "AAAA", 150000 });
            targetTable.Rows.Add(new object[] { 2, "BBBB", 500000 });
            targetTable.Rows.Add(new object[] { 3, "CCCC", 20000 });
            targetTable.Rows.Add(new object[] { 4, "DDDD", 40000 });
            targetTable.Rows.Add(new object[] { 5, "EEEE", 30000 });

            DataTable salesTable = new DataTable();
            salesTable.Columns.Add("ID", typeof(int));
            salesTable.Columns.Add("Date", typeof(DateTime));
            salesTable.Columns.Add("Category Name", typeof(string));
            salesTable.Columns.Add("Sale", typeof(int));

            salesTable.Rows.Add(new object[] { 1, DateTime.Parse("01/01/20"), "AAAA", 5000 });
            salesTable.Rows.Add(new object[] { 2, DateTime.Parse("01/01/20"), "BBBB", 1000 });
            salesTable.Rows.Add(new object[] { 3, DateTime.Parse("02/01/20"), "CCCC", 2000 });
            salesTable.Rows.Add(new object[] { 4, DateTime.Parse("03/01/20"), "DDDD", 4000 });
            salesTable.Rows.Add(new object[] { 5, DateTime.Parse("03/01/20"), "EEEE", 3000 });
            salesTable.Rows.Add(new object[] { 6, DateTime.Parse("03/01/20"), "AAAA", 1000 });
            salesTable.Rows.Add(new object[] { 7, DateTime.Parse("05/01/20"), "EEEE", 3000 });
            salesTable.Rows.Add(new object[] { 8, DateTime.Parse("06/01/20"), "BBBB", 3000 });
            salesTable.Rows.Add(new object[] { 9, DateTime.Parse("02/01/20"), "CCCC", 1000 });
            salesTable.Rows.Add(new object[] { 10, DateTime.Parse("02/01/20"), "DDDD", 2000 });

            DataTable table = new DataTable();
            table.Columns.Add("Target", typeof(int));
            table.Columns.Add("Category Name", typeof(string));
            table.Columns.Add("Sale", typeof(int));


            var joins = from t in targetTable.AsEnumerable()
                   join s in salesTable.AsEnumerable() on t.Field<string>("Category Name") equals s.Field<string>("Category Name")
                   select new { t = t, s = s};

            var groups = joins.GroupBy(x => x.t.Field<string>("Category Name"));

            foreach (var group in groups)
            {
                table.Rows.Add(new object[] { group.First().t.Field<int>("Target"), group.Key, group.Sum(x => x.s.Field<int>("Sale")) });
            }                                           
        }
    }
}