Member Avatar for programmerUSM

Hi everyone,
i need someone to help me. my code below will loop to fill the data .my problem now is i need to sum the total of the amount based on the region. How to get the amount based on region without enter the loop and only print out the amount if the region are different. Your help are highly appreciated.

 if (sqlQuery != null)
                {
                    // initialize the report data set
                    ReportDataSet ds = new ReportDataSet();

                    // initialize table two
                    var table2 = ds.TableTwo;

                    // looping to bind query value to table row
                    foreach (var x in sqlQuery)
                    {
                        // initilize table row 
                        var row = table2.NewTableTwoRow();

                        row.String1 = x.region;
                        row.String2 = x.txnState;
                        row.String3 = x.port;
                        row.String4 = x.txnmonth;
                        row.Decimal13 = x.txnAmount != null ? Convert.ToDecimal(x.txnAmount) : 0;

                        row.Decimal1 = x.JAN != null ? Convert.ToDecimal(x.JAN) : 0;
                        row.Decimal2 = x.FEB != null ? Convert.ToDecimal(x.FEB) : 0;
                        row.Decimal3 = x.MAR != null ? Convert.ToDecimal(x.MAR) : 0;
                        row.Decimal4 = x.APR != null ? Convert.ToDecimal(x.APR) : 0;
                        row.Decimal5 = x.MEI != null ? Convert.ToDecimal(x.MEI) : 0;
                        row.Decimal6 = x.JUN != null ? Convert.ToDecimal(x.JUN) : 0;
                        row.Decimal7 = x.JUL != null ? Convert.ToDecimal(x.JUL) : 0;
                        row.Decimal8 = x.AUG != null ? Convert.ToDecimal(x.AUG) : 0;
                        row.Decimal9 = x.SEP != null ? Convert.ToDecimal(x.SEP) : 0;
                        row.Decimal10 = x.OCT != null ? Convert.ToDecimal(x.OCT) : 0;
                        row.Decimal11 = x.NOV != null ? Convert.ToDecimal(x.NOV) : 0;
                        row.Decimal12 = x.DEC != null ? Convert.ToDecimal(x.DEC) : 0;

                        table2.AddTableTwoRow(row);


                    }
  • 2 Contributors
  • 1 Reply
  • 312 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by tdrosiadis

Dani AI

Generated

built the report row-by-row and wants a single subtotal printed per region without repeatedly re‑summing inside the detail loop. Two practical patterns fit that need: compute region totals in the database (best for large data) or compute them once in memory and then emit details plus a single subtotal row per group.

A concise in‑memory approach using LINQ (materialize the query first so it isn’t enumerated multiple times):

var list = sqlQuery.ToList();                     // materialize
var groups = list.GroupBy(r => r.region)
                 .OrderBy(g => g.Key);

foreach (var grp in groups)
{
    foreach (var item in grp)
    {
        // create & add the detail row for 'item'
    }

    // create a subtotal row for the group and add it once
    var subtotal = grp.Sum(i => i.txnAmount ?? 0m);
    // create subtotal row (mark it as subtotal so the report can style it)
}

If the database should do the work (recommended for big result sets), return details with a per‑region total column and let the report show that value only on the first/last row of each region or use a group footer. Example SQL to return a RegionTotal column:

SELECT region, txnState, port, txnmonth, txnAmount,
       SUM(CAST(txnAmount AS DECIMAL(18,2))) OVER (PARTITION BY region) AS RegionTotal
FROM   YourTable
ORDER  BY region, txnState;

Notes and troubleshooting: ensure rows are ordered by region before rendering, normalize region keys (Trim/ToUpperInvariant) to avoid duplicate groups, handle nullable or string amounts (parse/cast) and materialize IQueryable when running non‑translatable projections. For very large data sets, follow ’s advice and push grouping/summing to SQL; for moderate sizes the LINQ grouping pattern keeps logic simple and guarantees each region’s subtotal is produced once.

Member Avatar for tdrosiadis

Hi,
Generally you don't make calculations in the code behind of the report. You let SQL handle the summing process and you just display the results.
If I have understand correctly you want the data of the database's table to display in rows and at the end of the region column to have the amount summed!
So bind a gridview to an sql query that will display the amounts of the region and in the footer of your gridview bind a sum query that will sum the amounts for that region!
Generally the syntax for the MS SQL of Sum function is:
SUM ( [ ALL | DISTINCT ] expression )
OVER ( [ partition_by_clause ] order_by_clause )
Hope I helped you
Tasos

Edited by tdrosiadis
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.