none
Can I create gridview subtotals through data derived from stored proc?

    Question

  • Greetings,

    I wanted to know if it is possible to format subtotals based off of each employee in my gridview. I will post my C# and stored procedure. As of now I am pulling every record assoc with every employee. I would like to view a single employee name and subtotals for each category assoicated with the employee. What do you think?

    using System;
    using System.Data.SqlClient;
    using System.Collections;
    using System.Data;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    public partial class App_MetricsPages_IMMeetingReport : System.Web.UI.Page
    {
        private void Page_Load(object sender, System.EventArgs e)
        {
            // Only run this code the first time the page is loaded.
            // The code inside the IF statement is skipped when you resubmit the page.
            if (!IsPostBack)
            {
                //Create a connection to the SQL Server; modify the connection string for your environment
                //SqlConnection MyConnection = new SqlConnection("server=(local);database=pubs;Trusted_Connection=yes");
                SqlConnection MyConnection = new SqlConnection("server=dmzpbl-sql05;database=Test_MasterRpt;UID=medsql;PWD=medsql;");

                // Create a Command object, and then set the connection.
                // The following SQL statements check whether a GetAuthorsByLastName 
                // stored procedure already exists.
                SqlCommand MyCommand = new SqlCommand("select * from sysobjects where id = object_id(N'procProjectTrackingMeetings')" +
                "  and OBJECTPROPERTY(id, N'IsProcedure') = 1", MyConnection);

                // Set the command type that you will run.
                MyCommand.CommandType = CommandType.Text;

                // Open the connection.
                MyCommand.Connection.Open();

                // Run the SQL statement, and then get the returned rows to the DataReader.
                SqlDataReader MyDataReader = MyCommand.ExecuteReader();

                // If any rows are returned, the stored procedure that you are trying
                // to create already exists. Therefore, try to create the stored procedure
                // only if it does not exist.
                if (!MyDataReader.Read())
                {
                    MyCommand.CommandText = "create procedure procProjectTrackingMeetings" +
                        " (@au_lname varchar(40), select * from authors where" +
                        " au_lname like @au_lname; select @RowCount=@@ROWCOUNT";
                    MyDataReader.Close();
                    MyCommand.ExecuteNonQuery();
                }
                else
                {
                    MyDataReader.Close();
                }

                MyCommand.Dispose();  //Dispose of the Command object.
                MyConnection.Close(); //Close the connection.
            }

            // Add the event handler to the Button_Click event.
            this.SearchButton.Click += new System.EventHandler(this.SearchButton_Click);
        }
        public void SearchButton_Click(object sender, System.EventArgs e)
        {
            //Create a connection to the SQL Server; modify the connection string for your environment.
            //SqlConnection MyConnection = new SqlConnection("server=(local);database=pubs;Trusted_Connection=yes");
            SqlConnection MyConnection = new SqlConnection("server=dmzpbl-sql05;database=Test_MasterRpt;UID=medsql;PWD=medsql;");

            //Create a DataAdapter, and then provide the name of the stored procedure.
            SqlDataAdapter MyDataAdapter = new SqlDataAdapter("procProjectTrackingMeetings", MyConnection);

            //Set the command type as StoredProcedure.
            MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;

            //Create and add a parameter to Parameters collection for the stored procedure.
            MyDataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@StartDate", SqlDbType.DateTime));

            //Assign the search value to the parameter.
            MyDataAdapter.SelectCommand.Parameters["@StartDate"].Value = (StartDateTextBox.Text).Trim();

            //Create and add a parameter to Parameters collection for the stored procedure.
            MyDataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@EndDate", SqlDbType.DateTime));

            //Assign the search value to the parameter.
            MyDataAdapter.SelectCommand.Parameters["@EndDate"].Value = (EndDateTextBox.Text).Trim();

            //Create and add an output parameter to the Parameters collection.
            MyDataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@RowCount", SqlDbType.Int, 4));

            //Set the direction for the parameter. This parameter returns the Rows that are returned.
            MyDataAdapter.SelectCommand.Parameters["@RowCount"].Direction = ParameterDirection.Output;

            //Create a new DataSet to hold the records.
            DataSet DS = new DataSet();

            //Fill the DataSet with the rows that are returned.
            MyDataAdapter.Fill(DS, "ProjectTrackingMeetings");

            /**********************************************
             * Get the number of rows returned, and assign it to the Label control.
            RowCount.Text = DS.Tables(0).Rows.Count().ToString() & " Rows Found!";
            RowCount.Text = MyDataAdapter.SelectCommand.Parameters[1].Value + " Rows Found!";
             * ********************************************/

            //Set the data source for the DataGrid as the DataSet that holds the rows.
            GrdAccount.DataSource = DS.Tables["ProjectTrackingMeetings"].DefaultView;

            //NOTE: If you do not call this method, the DataGrid is not displayed!
            GrdAccount.DataBind();

            MyDataAdapter.Dispose(); //Dispose the DataAdapter.
            //
        }
    }   


    /*************************************************************************************************
    ======================================================================================
    ************************************************************************************************
    Here is the stored Procedure
    ***************************************************************************************************
    =========================================================================================
    USE [Test_MasterRpt]
    GO
    /****** Object:  StoredProcedure [dbo].[procProjectTrackingMeetings]    Script Date: 02/16/2009 08:05:37 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:        Aaron WIlliams
    -- Create date: 02/04/2009
    -- Description:    Create queries for Accountability - IM Reporting
    -- =============================================
    ALTER PROCEDURE [dbo].[procProjectTrackingMeetings]
        -- Add the parameters for the stored procedure here
    (@StartDate as datetime, @EndDate as datetime, @RowCount as int output)

    AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

        -- Insert statements for procedure here

    --drop table #TempEstVSAct

    SELECT Meetings.ID, [Personnel Hours].RoleID, 'Actual' AS Type,
    Sum([Personnel Hours].EntryHrs) AS ActEntryHrs, Sum([Personnel Hours].EntryHrs) as EstEntryHrs
    INTO #TempEstVSAct
    FROM ([Personnel Hours] INNER JOIN Meetings ON [Personnel Hours].MeetingID = Meetings.ID)
    INNER JOIN [Company Personnel Acty] ON [Personnel Hours].ActyID = [Company Personnel Acty].ActyID
    WHERE ((([Company Personnel Acty].PerActy) Not Like 'On%'
    And ([Company Personnel Acty].PerActy) Not Like 'sourcing' And
    ([Company Personnel Acty].PerActy) Not Like '%graphic%'))
    GROUP BY Meetings.ID, [Personnel Hours].RoleID

    update #TempEstVSAct
    set EstEntryHrs = 0


    INSERT INTO #TempEstVSAct( ID, RoleID, Type, ActEntryHrs, EstEntryHrs)
    SELECT Meetings.ID, [Meeting Estimated].RoleID, 'Est' AS Type, 0,
    Sum([Meeting Estimated].EntryHrs) AS EstEntryHrs
    FROM (Meetings INNER JOIN [Meeting Estimated] ON Meetings.ID = [Meeting Estimated].MeetingID)
    INNER JOIN [Company Personnel Acty] ON [Meeting Estimated].ActyID = [Company Personnel Acty].ActyID
    WHERE ((([Company Personnel Acty].PerActy) Not Like 'on%'
    And ([Company Personnel Acty].PerActy) Not Like 'sourcing'
    And ([Company Personnel Acty].PerActy) Not Like '%graphic%'))
    GROUP BY Meetings.ID, [Meeting Estimated].RoleID



    SELECT Meetings.[Event Coord] as ProgramCoordinator, Meetings.Company, Meetings.[VMS/Core] as VMSCore,
    convert(varchar(10),Meetings.BegDate,101) as BegDate, Datediff(d,BegDate,GetDate()) as DaysUntilStart,
    Meetings.[Est Mgt Fee] as EstMgtFee, 
    convert(varchar(10),Meetings.[Date Sent to Client], 101) as FinalBilled,
    [Company Personnel Role].PerRole,
    Sum(#TempEstVSAct.EstEntryHrs)  AS ThreshHrs,
    Round(Sum(#TempEstVSAct.ActEntryHrs),2)  AS ActHrs,
    case when Sum(#TempEstVSAct.EstEntryHrs) = 0 then 0 else
    Round((Sum(#TempEstVSAct.ActEntryHrs)/Sum(#TempEstVSAct.EstEntryHrs)),2) end as ActToGoalPercent,
    Sum(#TempEstVSAct.EstEntryHrs) - Sum(#TempEstVSAct.ActEntryHrs) as RemainingGoalHrs

    --#TempEstVSAct.RoleID,
    --#TempEstVSAct.ID, Meetings.[Activity Year] as ActivityYear,
    --  Meetings.[Meeting Name] as MeetingName,
    --Meetings.[Meeting Type] as MeetingType, 
    --Meetings.[Act Mgt Fee] as ActMgtFee, Meetings.[Approved Status] as ApprovedStatus,
    --Meetings.[Cancelled Status] as CancelledStatus, Meetings.[Medical Educ] as MedicalEduc,
    --Meetings.[Client Dept] as ClientDept, Meetings.[Client Contact] as ClientContact,
    --Meetings.[Account Manager] as AcctManager, Meetings.Planner

    FROM ([Company Personnel Role]
    INNER JOIN #TempEstVSAct ON [Company Personnel Role].RoleID = #TempEstVSAct.RoleID)
    INNER JOIN Meetings ON #TempEstVSAct.ID = Meetings.ID
    WHERE (((Meetings.[Medical Educ])= 0)) and Meetings.BegDate between @StartDate and @EndDate
    and Meetings.[Cancelled Status] = 'N'
    group by  --#TempEstVSAct.ID, Meetings.[Activity Year],
    Meetings.[VMS/Core], Meetings.Company, Meetings.[Meeting Name],
    Meetings.[Meeting Type], Meetings.BegDate,
    Meetings.[Est Mgt Fee],
    Meetings.[Act Mgt Fee], --Meetings.[Approved Status],
    --Meetings.[Cancelled Status], Meetings.[Medical Educ] ,
    convert(varchar(10),Meetings.[Date Sent to Client], 101),
    --#TempEstVSAct.RoleID,
    [Company Personnel Role].PerRole,
    --Meetings.[Client Dept] , Meetings.[Client Contact] ,
    --Meetings.[Account Manager] , Meetings.Planner,
    Meetings.[Event Coord]
    order by Meetings.[Event Coord], Meetings.Company, Meetings.[Meeting Name]





    END


    Monday, February 16, 2009 6:18 PM

Answers

  • Yes but this forum is for Windows Froms. Go to http://forums.asp.net for ASP.Net questions.
    MSMVP VC++
    • Marked as answer by Kira Qian Thursday, February 19, 2009 2:15 AM
    Tuesday, February 17, 2009 11:38 PM