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
  • 297 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by tdrosiadis
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.