GridView Footer Data using Paging, Need Grand Total...

HellFireClub

Hi Folks,

I've run into a bit of a problem on an aspx page I've created for running reports on data I have in my MS SQL 2005 DB.

Basically I have a GridView that works fine (with paging set to 20 records), and I have it working to display summary data at the footer, and it does this perfectly, UNTIL the report returns a list of records that are greater than my Paging value for the GridView. So I have my GridView set up to handle 20 rows per page, and if I get 40 rows of data back, I have 2 pages in my Gridview as I have my Gridview set to Paging = "true".

When I am dealing with more than one page, the Footer data only relates to the particular page I am on, and not the entire range of data that is displayed on all pages of my Gridview, whatever number of pages (or collectively, rows), that may be.

So... What I have are running totals but what I need is only one set of totals for the entire Gridview.

I've done a good bit of searching through the internet over the last 24 hours, and the closest I can find in terms of a solution is:

http://shawpnendu.blogspot.com/2009/10/show-grand-total-running-total-in.html

However I'm a bit unhappy using this part of the code:

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string connectionString = ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString;
DataTable dt = new DataTable();
SqlConnection conn = new SqlConnection(connectionString);
using (conn)
{
SqlDataAdapter ad = new SqlDataAdapter(
"SELECT OrderID Code,B.Name,OrderDate Date,Amount from "+
"orders A INNER JOIN customer B ON A.Customerid=B.ID", conn);
ad.Fill(dt);
}

decimal GrandTotal = 0;
foreach (DataRow oRow in dt.Rows)
GrandTotal += Convert.ToDecimal(oRow["Amount"]);

ViewState["GrandTotal"] = GrandTotal;

gvEdit.DataSource = dt;
gvEdit.DataBind();
ViewState["dt"] = dt;
}

The problem with the above is that I already have an Sqldatasource set up for my Gridview in my aspx page, which uses 3 control parameters, connected to 3 DropDownLists to control the data I get back, which is then displayed in my Gridview. This all works fine btw...

Ideally, I would use this same SqlDataSource, or another one that I'd set up on my aspx page, using the same 3 control parameters and dropdownlists, to populate my Grand Total data???

I thought this would be easy, actually I'm a bit surprised that Microsoft only appear to let me use footer total data per page and not across the whole table, sure what use is footer total data, given per page, to anyone?!?!? It's only a function of the number of rows you have set up for your GridView per page and the sum of whatever comes back for that number of rows, stupid stupid stupid! :D:D

My code (extracts):

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:xxxxxxxxxxxxxxxxxxxxxx %>"
SelectCommand="SELECT * FROM [Orders] WHERE (([PaymentStatus] = @PaymentStatus) AND ([TransactionOwner] = @userName) AND ([Timestamp] > @Start) AND ([Timestamp] < @End))&quot;>
<SelectParameters>
<asp:ControlParameter Name="Start" ControlId="FirstDate" Type="DateTime"
PropertyName="Text" />
<asp:ControlParameter Name="End" ControlId="LastDate" Type="DateTime"
PropertyName="Text" />

<asp:ControlParameter ControlID="DropDownList3" Name="PaymentStatus"
PropertyName="SelectedValue" Type="String" />
<asp:ControlParameter ControlID="DropDownList4" Name="UserName"
PropertyName="SelectedValue" Type="String" />
</SelectParameters>
</asp:SqlDataSource>

<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
AutoGenerateEditButton="True" AutoGenerateColumns="false" ShowFooter="true" OnRowDataBound="GridView1_RowDataBound" OnPageIndexChanging="GridView1_PageIndexChanging"
DataKeyNames="ID" DataSourceID="SqlDataSource1" BorderStyle="Dashed" BorderColor="SlateGray" BorderWidth="1"
Width="970px" style="margin-right: 7px" PageSize="18" >

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
decimal rowTotal = Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "LinePrice"));
grdTotal = grdTotal + rowTotal;

int QuantTotal = Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "Quantity"));
QuantityTotal = QuantityTotal + QuantTotal;

decimal ProfTotal = Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "TransactionProfit"));
TotalProfit = TotalProfit + ProfTotal;

decimal SellTotal = Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "PurchasePrice"));
TotalBuy = TotalBuy + SellTotal;

decimal BuyTotal = Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "Cost"));
TotalSell = TotalSell + BuyTotal;
}
if (e.Row.RowType == DataControlRowType.Footer)
{
Label lbl = (Label)e.Row.FindControl("TestFooter");
lbl.Text = grdTotal.ToString("c");

Label lbl2 = (Label)e.Row.FindControl("QuantityFooter");
lbl2.Text = QuantityTotal.ToString();

Label lbl3 = (Label)e.Row.FindControl("ProfitFooter");
lbl3.Text = TotalProfit.ToString("c");

Label lbl4 = (Label)e.Row.FindControl("PurchasePriceLabelFooter");
lbl4.Text = TotalBuy.ToString("c");

Label lbl5 = (Label)e.Row.FindControl("CostLabelFooter");
lbl5.Text = TotalSell.ToString("c");

Label lbl6 = (Label)e.Row.FindControl("ProfitMarginLabelFooter");
ProfitMargin = (TotalProfit / TotalBuy) * 100;
lbl6.Text = ProfitMargin.ToString("F2");

amen

A few things

1: Select * is generally bad. You should always select the columns you explicity want
2: You do not have an order by clause. This means if you run the report multiple times with the same data there is guarantee that you will get the data back in the same order each time. Disaster for any user and for you
3: I would add a GrandTotal column to your SQL String which is included for every row which can then be accessed for the Grand Total on screen.
4: I'd consider using a SQL Stored Procedure as well

Galtee

HellFireClub wrote: »

Hi Folks,

I've run into a bit of a problem on an aspx page I've created for running reports on data I have in my MS SQL 2005 DB.

Basically I have a GridView that works fine (with paging set to 20 records), and I have it working to display summary data at the footer, and it does this perfectly, UNTIL the report returns a list of records that are greater than my Paging value for the GridView. So I have my GridView set up to handle 20 rows per page, and if I get 40 rows of data back, I have 2 pages in my Gridview as I have my Gridview set to Paging = "true".

When I am dealing with more than one page, the Footer data only relates to the particular page I am on, and not the entire range of data that is displayed on all pages of my Gridview, whatever number of pages (or collectively, rows), that may be.

So... What I have are running totals but what I need is only one set of totals for the entire Gridview.

I've done a good bit of searching through the internet over the last 24 hours, and the closest I can find in terms of a solution is:

http://shawpnendu.blogspot.com/2009/10/show-grand-total-running-total-in.html

However I'm a bit unhappy using this part of the code:

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string connectionString = ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString;
DataTable dt = new DataTable();
SqlConnection conn = new SqlConnection(connectionString);
using (conn)
{
SqlDataAdapter ad = new SqlDataAdapter(
"SELECT OrderID Code,B.Name,OrderDate Date,Amount from "+
"orders A INNER JOIN customer B ON A.Customerid=B.ID", conn);
ad.Fill(dt);
}

decimal GrandTotal = 0;
foreach (DataRow oRow in dt.Rows)
GrandTotal += Convert.ToDecimal(oRow["Amount"]);

ViewState["GrandTotal"] = GrandTotal;

gvEdit.DataSource = dt;
gvEdit.DataBind();
ViewState["dt"] = dt;
}

The problem with the above is that I already have an Sqldatasource set up for my Gridview in my aspx page, which uses 3 control parameters, connected to 3 DropDownLists to control the data I get back, which is then displayed in my Gridview. This all works fine btw...

Ideally, I would use this same SqlDataSource, or another one that I'd set up on my aspx page, using the same 3 control parameters and dropdownlists, to populate my Grand Total data???

I thought this would be easy, actually I'm a bit surprised that Microsoft only appear to let me use footer total data per page and not across the whole table, sure what use is footer total data, given per page, to anyone?!?!? It's only a function of the number of rows you have set up for your GridView per page and the sum of whatever comes back for that number of rows, stupid stupid stupid! :D:D

My code (extracts):

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:xxxxxxxxxxxxxxxxxxxxxx %>"
SelectCommand="SELECT * FROM [Orders] WHERE (([PaymentStatus] = @PaymentStatus) AND ([TransactionOwner] = @userName) AND ([Timestamp] > @Start) AND ([Timestamp] < @End))&quot;>
<SelectParameters>
<asp:ControlParameter Name="Start" ControlId="FirstDate" Type="DateTime"
PropertyName="Text" />
<asp:ControlParameter Name="End" ControlId="LastDate" Type="DateTime"
PropertyName="Text" />

<asp:ControlParameter ControlID="DropDownList3" Name="PaymentStatus"
PropertyName="SelectedValue" Type="String" />
<asp:ControlParameter ControlID="DropDownList4" Name="UserName"
PropertyName="SelectedValue" Type="String" />
</SelectParameters>
</asp:SqlDataSource>

<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
AutoGenerateEditButton="True" AutoGenerateColumns="false" ShowFooter="true" OnRowDataBound="GridView1_RowDataBound" OnPageIndexChanging="GridView1_PageIndexChanging"
DataKeyNames="ID" DataSourceID="SqlDataSource1" BorderStyle="Dashed" BorderColor="SlateGray" BorderWidth="1"
Width="970px" style="margin-right: 7px" PageSize="18" >

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
decimal rowTotal = Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "LinePrice"));
grdTotal = grdTotal + rowTotal;

int QuantTotal = Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "Quantity"));
QuantityTotal = QuantityTotal + QuantTotal;

decimal ProfTotal = Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "TransactionProfit"));
TotalProfit = TotalProfit + ProfTotal;

decimal SellTotal = Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "PurchasePrice"));
TotalBuy = TotalBuy + SellTotal;

decimal BuyTotal = Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "Cost"));
TotalSell = TotalSell + BuyTotal;
}
if (e.Row.RowType == DataControlRowType.Footer)
{
Label lbl = (Label)e.Row.FindControl("TestFooter");
lbl.Text = grdTotal.ToString("c");

Label lbl2 = (Label)e.Row.FindControl("QuantityFooter");
lbl2.Text = QuantityTotal.ToString();

Label lbl3 = (Label)e.Row.FindControl("ProfitFooter");
lbl3.Text = TotalProfit.ToString("c");

Label lbl4 = (Label)e.Row.FindControl("PurchasePriceLabelFooter");
lbl4.Text = TotalBuy.ToString("c");

Label lbl5 = (Label)e.Row.FindControl("CostLabelFooter");
lbl5.Text = TotalSell.ToString("c");

Label lbl6 = (Label)e.Row.FindControl("ProfitMarginLabelFooter");
ProfitMargin = (TotalProfit / TotalBuy) * 100;
lbl6.Text = ProfitMargin.ToString("F2");

If you want ot summarise the whole dataset then you need to move your code to the databound event of the gridview and loop through the whole dataset as only the active rows (current page) are databound which is why you're only getting the actve page, or as Amen says, build it into your query and use that GrandTotal column(s) as the source of summary etc. if you can.

HellFireClub

amen wrote: »

A few things

1: Select * is generally bad. You should always select the columns you explicity want
2: You do not have an order by clause. This means if you run the report multiple times with the same data there is guarantee that you will get the data back in the same order each time. Disaster for any user and for you
3: I would add a GrandTotal column to your SQL String which is included for every row which can then be accessed for the Grand Total on screen.
4: I'd consider using a SQL Stored Procedure as well

1 & 2 accepted! I'm trying to think out your option 3 with a view to using this to fix my particular issue here, but I'm not sure I fully understand what you are suggesting... Can I run a query in my SQL statement to SUM all the data returned across all pages in the GridView, in conjunction with the Select statement that I have? Or do I have to set up a new SQL statement to get this SUM data? If I do have to use a new SqlDataSource to do this, can I put the data from this into my GridView footer, even though my GridView is using a different SqlDataSource for the SELECT query in my GridView?

I have to say (again!), this idea of only providing for the running totals on a page basis is one of the stupidest things I've ever seen in this development world. What use is summary/running total data on a page per page basis to anyone? It's clearly useful if you only have one page but not under any other circumstances, how on earth nobody copped this when developing VWD I think is pretty incredible.

amen

[PHP]
SELECT ColumnA,ColumnB,Sum(TotalColumn) AS GrandTotal FROM [Orders] WHERE (([PaymentStatus] = @PaymentStatus) AND ([TransactionOwner] = @userName) AND ([Timestamp] > @Start) AND ([Timestamp] < @End)
group by columnA,ColumnB

[/PHP]

replacing ColumnA, columnB with the relevant columns you need

HellFireClub

amen wrote: »

[PHP]
SELECT ColumnA,ColumnB,Sum(TotalColumn) AS GrandTotal FROM [Orders] WHERE (([PaymentStatus] = @PaymentStatus) AND ([TransactionOwner] = @userName) AND ([Timestamp] > @Start) AND ([Timestamp] < @End)
group by columnA,ColumnB

[/PHP]replacing ColumnA, columnB with the relevant columns you need

Thanks a mil for that Amen, but say if I want to sum more than one column, can I do this as you have outlined above, like in the same sqldatasource?

mewso

HellFireClub wrote: »

I have to say (again!), this idea of only providing for the running totals on a page basis is one of the stupidest things I've ever seen in this development world. What use is summary/running total data on a page per page basis to anyone? It's clearly useful if you only have one page but not under any other circumstances, how on earth nobody copped this when developing VWD I think is pretty incredible.

I'm not sure I understand your complaint here to be honest. The code you posted is doing a rudimentary summing of the values while each row is iterated through so obviously that particular way of doing things is never going to stretch beyond the rows being looped for that particular page.

The SqlDatasource and the wizardry that comes with it will always have limitations and for any kind of serious development should be dumped as soon as possible. It may be that you just needed a quick grid of data for your own purposes and so on but if so you need to accept the limitations that come with using these drag and drop bells and whistles.

Getting into using some proper non-wiz-bang db access code to return the total records and the actual records to implement custom paging and along with that code to return the overall totals for your footer is the way to go if you really need this.

HellFireClub

mewso wrote: »

I'm not sure I understand your complaint here to be honest. The code you posted is doing a rudimentary summing of the values while each row is iterated through so obviously that particular way of doing things is never going to stretch beyond the rows being looped for that particular page.

The SqlDatasource and the wizardry that comes with it will always have limitations and for any kind of serious development should be dumped as soon as possible. It may be that you just needed a quick grid of data for your own purposes and so on but if so you need to accept the limitations that come with using these drag and drop bells and whistles.

Getting into using some proper non-wiz-bang db access code to return the total records and the actual records to implement custom paging and along with that code to return the overall totals for your footer is the way to go if you really need this.

I hear ya, but my point is, what use is page by page totals to any person? If you run a query (say a date range query as I have done above), you don't know how many pages will come back, and you don't know (as a user), what the paging number has been set to in the Gridview declaration in the aspx page. It seems rediculous to me that you can get back totals for a page without too much issue (even though they are never going to be any use to anyone), but what you do need a total for (the full list of data returned), you have to go around the house to get that information in place. I just don't get that.