0

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
34
Views
1 Year
Discussion Span
Last Post by tdrosiadis
0

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

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.