• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 598
  • Last Modified:

How to get summary from Gridview Output.

I have the Gridview where data is populated like this.

Source      State      Count
CI                 A                5
CI                 B                5
CI                 C                5
CI                 D                5
CI                 E                5
CM                 F                3
CM                G                3
CM                H                3

How to get the summary Below as
CI Total - 25, CM Total - 9
0
searchsanjaysharma
Asked:
searchsanjaysharma
2 Solutions
 
Rajar AhmedConsultantCommented:
Try this,
Add this on markup of your gridview
ShowFooter="true" OnRowDataBound="gvData_RowDataBound" 

Open in new window

//have these variables on your codebehind
Int32 count = 0;
    StringBuilder footerText = new StringBuilder();
    string compSource = "";
protected void gvData_RowDataBound(object sender, GridViewRowEventArgs e)
    {
     
        if (e.Row.RowType == DataControlRowType.DataRow)
        {

            if (compSource != e.Row.Cells[0].Text)
            {
                
                if (count > 0)
                {
                    footerText.Append(count);
                    footerText.Append(" , " + e.Row.Cells[0].Text + " Total - ");
                    count = 0;
                }
                else if (string.IsNullOrEmpty(compSource))
                {
                    footerText.Append(e.Row.Cells[0].Text + " Total - ");
                }
                compSource = e.Row.Cells[0].Text;
            }
            compSource = e.Row.Cells[0].Text;
            count += Int32.Parse(e.Row.Cells[2].Text);            
        }
        else if (e.Row.RowType == DataControlRowType.Footer){
            footerText.Append(count.ToString());
            e.Row.Cells[0].Text = footerText.ToString();
        }

    }

Open in new window

0
 
Alan WarrenCommented:
Hi searchsanjaysharma
Could do it with a datalist populated by a stored procedure, totalling COUNT values can't be done with inline SQL, but it can be done.
Totalling SQL COUNT results with a Datalist
Codeless databinding Gridview and Datalist to SqlDataSources
<%@ Page Language="VB" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
      <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
        DataSourceID="CountsSqlDataSource">
        <Columns>
          <asp:BoundField DataField="Source" HeaderText="Source" 
            SortExpression="Source" />
          <asp:BoundField DataField="State" HeaderText="State" SortExpression="State" />
          <asp:BoundField DataField="Count" HeaderText="Count" ReadOnly="True" 
            SortExpression="Count" />
        </Columns>
      </asp:GridView>
      <asp:SqlDataSource ID="CountsSqlDataSource" runat="server" 
        ConnectionString="<%$ ConnectionStrings:cnSessionTest %>" 
        SelectCommand="Table_2_Counts_Sel" SelectCommandType="StoredProcedure"></asp:SqlDataSource>  

      <asp:DataList ID="TotalsDataList" runat="server" 
        DataSourceID="TotalsSqlDataSource" RepeatDirection="Horizontal" 
        RepeatLayout="Flow" >
        <ItemTemplate>
          <asp:Label ID="SourceLabel" runat="server" Text='<%# Eval("Source") %>' /> Total - <asp:Label ID="TotalsLabel" runat="server" Text='<%# Eval("Totals") %>' />
        </ItemTemplate>
          <ItemStyle Wrap="False" />
        </asp:DataList>

      <asp:SqlDataSource ID="TotalsSqlDataSource" runat="server" 
        ConnectionString="<%$ ConnectionStrings:cnSessionTest %>" 
        SelectCommand="Table_2_Totals_Sel" SelectCommandType="StoredProcedure"></asp:SqlDataSource>  
    
    </div>
    </form>
</body>
</html>

Open in new window

And the Stored procedures, which you would need to modify for correct table names.
ALTER PROCEDURE [dbo].[Table_2_Counts_Sel]
AS
BEGIN

SELECT	 t1.Source
		,t1.[State]
		,(	SELECT     COUNT(Source) AS 'Count'
			FROM          dbo.Table_2 AS t2
			WHERE      (Source = t1.Source)) AS 'Count' 
                                                     
FROM         sessiontest.dbo.Table_2 AS t1
GROUP BY t1.Source, t1.[State]
END

-- Testing
-- exec Table_2_Counts_Sel

-- Output
-- CI	A	5
-- CI	B	5
-- CI	C	5
-- CI	D	5
-- CI	E	5
-- CM	F	3
-- CM	G	3
-- CM	H	3

Open in new window

ALTER PROCEDURE [dbo].[Table_2_Totals_Sel]
AS
BEGIN

-- Create a temp table TO store the select results
CREATE TABLE #SumCountSource
(
    [Source] [varchar](50) NULL,
    [State] [varchar](50) NULL,
    [CountOfSource] INT NULL
)

INSERT INTO #SumCountSource

SELECT	 t1.Source
		,t1.[State]
		,(	SELECT     COUNT(Source) AS 'Count'
			FROM          dbo.Table_2 AS t2
			WHERE      (Source = t1.Source)) AS 'CountOfSource' 
                                                     
FROM         sessiontest.dbo.Table_2 AS t1
GROUP BY t1.Source, t1.[State]


SELECT Source, SUM([CountOfSource]) AS Totals
FROM   #SumCountSource
GROUP BY [Source]

DROP TABLE #SumCountSource
END

-- Testing
-- exec Table_2_Totals_Sel

-- Output
-- CI	25
-- CM	9

Open in new window

Alan ";0)
0
 
searchsanjaysharmaAuthor Commented:
good
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now