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);


                    }

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 8 Months Ago by tdrosiadis

This article has been dead for over six months. Start a new discussion instead.