group dataset data and get totals

Discussion in 'ASP .Net' started by =?Utf-8?B?aWdvdHlvdXJkb3RuZXQ=?=, Oct 11, 2006.

  1. I'm getting my dataset like this:

    SalesMan Sales Make
    Smith 25,000 1
    Smith 9500 10
    Smith 72,252 1
    Smith 125,000 5
    Smith 25,252 10
    Jones 65,000 5
    Jones 12,500 10

    I want to show this data like this in my data grid
    SalesMan TotalSales TotalSalesAmount Chevy(10)
    TotalSales BMW(1) TotalSales Bentley(5) TotalSales
    Smith 5 257,004 2 34,752
    2 97,252 1 125,000
    Jones 2 77,500 1 12,500
    1 65,000

    Total Sales 7 334,504 3 47252
    2 97,252 2 190,000


    if there are more then one salesman, I want to get the total for all, so
    total sales, total sales amount, then total for each car make sold and number
    sold.
    I want to show this in a datagrid so I can then export it to excel if
    possible. Is this possible to do?
     
    =?Utf-8?B?aWdvdHlvdXJkb3RuZXQ=?=, Oct 11, 2006
    #1
    1. Advertising

  2. =?Utf-8?B?aWdvdHlvdXJkb3RuZXQ=?=

    sloan Guest

    See this KB:

    http://support.microsoft.com/default.aspx?scid=kb;en-us;326145


    /*
    * See KB http://support.microsoft.com/default.aspx?scid=kb;en-us;326145
    * There were 2 bugs in the code as retrieved from the KB
    *
    * private object Add ... and this line // return (Convert.ToDecimal(a) +
    Convert.ToDecimal(b));
    *
    *
    * private bool ColumnCompare .. and this line // bool returnValue =
    (Convert.ToString(a) == Convert.ToString(b)); return returnValue;
    *
    *
    * and 1 enhancement in the InsertGroupByInto method
    *
    * case "last":
    if (GroupBy.Length > 0)
    {
    //they specified a non aggr column .. but since there was no
    GroupBy, it won't reflect accurate data, thus only set it when there is a
    valid GroupBY
    DestRow[Field.FieldAlias]=SourceRow[Field.FieldName];
    }
    *
    * */






    "igotyourdotnet" <> wrote in message
    news:...
    > I'm getting my dataset like this:
    >
    > SalesMan Sales Make
    > Smith 25,000 1
    > Smith 9500 10
    > Smith 72,252 1
    > Smith 125,000 5
    > Smith 25,252 10
    > Jones 65,000 5
    > Jones 12,500 10
    >
    > I want to show this data like this in my data grid
    > SalesMan TotalSales TotalSalesAmount Chevy(10)
    > TotalSales BMW(1) TotalSales Bentley(5) TotalSales
    > Smith 5 257,004 2

    34,752
    > 2 97,252 1 125,000
    > Jones 2 77,500 1

    12,500
    > 1 65,000
    >
    > Total Sales 7 334,504 3 47252
    > 2 97,252 2 190,000
    >
    >
    > if there are more then one salesman, I want to get the total for all, so
    > total sales, total sales amount, then total for each car make sold and

    number
    > sold.
    > I want to show this in a datagrid so I can then export it to excel if
    > possible. Is this possible to do?
     
    sloan, Oct 11, 2006
    #2
    1. Advertising

  3. Thanks for Sloan's input.

    Hello Igotyourdotnet,

    As for the knowlege base article Sloan has mentioned, it provide a helper
    class which can query Rows in DataTable, also there is another one
    describing on write a helper to query distinct rows from DataTable:

    #HOW TO: Implement a DataSet SELECT DISTINCT Helper Class in Visual C# .NET
    http://support.microsoft.com/kb/326176/en-us

    However, I think if we put all the calculation with the given DataTable at
    ADO.NET layer(.net code), it will be quite complex and require large number
    of code event involve those two existing helper classes.

    As for the raw table structure you mentioned below:

    SalesMan Sales Make
    Smith 25,000 1
    Smith 9500 10
    Smith 72,252 1
    Smith 125,000 5
    Smith 25,252 10
    Jones 65,000 5
    Jones 12,500 10

    Is it an physical table or view in the database ? if so, I suggest you
    first structure the result set at database layer through T-SQL. e.g.

    we use the following sql query to return distinct salesman and make list.
    =========
    select distinct salesman from salerecords

    select distinct make from salerecords
    ===========

    and use the following SQL query to return the sales and salesamount group
    upon salesman and make:
    =======
    select salesman, count(sales) as totalsales_count_make, sum(sales) as
    totalsales_amount_make from salerecords group by salesman, make order by
    salesman desc
    =========

    the result set will look like below:
    ==================


    salesman totalsales_count_make totalsales_amount_make

    Smith 2 97252
    Smith 1 125000
    Smith 2 34752
    Jones 1 65000
    Jones 1 12500

    =======================

    Given the above records, you can loop through the rows and manually
    calculate the value of columns in each row of the target table you want (as
    below);


    ====================
    SalesMan TotalSales TotalSalesAmount Chevy(10)
    TotalSales BMW(1) TotalSales Bentley(5) TotalSales

    ==============

    You first loop through all the distinct salesman and for each salesman,
    calculate the column value according to above resultset(return by group sql
    query).

    In a word, what we need to do is put all the data into single DataTable so
    that the GridView can simply bind to it. And put all the group and query
    code in .NET layer will be much more expensive.

    How do you think?

    Please feel free to post here if you have any other ideas or concerns on
    this.

    Sincerely,

    Steven Cheng

    Microsoft MSDN Online Support Lead



    ==================================================

    Get notification to my posts through email? Please refer to
    http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
    ications.



    Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
    where an initial response from the community or a Microsoft Support
    Engineer within 1 business day is acceptable. Please note that each follow
    up response may take approximately 2 business days as the support
    professional working with you may need further investigation to reach the
    most efficient resolution. The offering is not appropriate for situations
    that require urgent, real-time or phone-based interactions or complex
    project analysis and dump analysis issues. Issues of this nature are best
    handled working with a dedicated Microsoft Support Engineer by contacting
    Microsoft Customer Support Services (CSS) at
    http://msdn.microsoft.com/subscriptions/support/default.aspx.

    ==================================================



    This posting is provided "AS IS" with no warranties, and confers no rights.
     
    Steven Cheng[MSFT], Oct 12, 2006
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest
    Replies:
    2
    Views:
    443
    Guest
    Jul 2, 2004
  2. =?Utf-8?B?Um9iZXJ0IENoYXBtYW4=?=

    Running totals in gridview (when you have many totals required)

    =?Utf-8?B?Um9iZXJ0IENoYXBtYW4=?=, Feb 15, 2007, in forum: ASP .Net
    Replies:
    3
    Views:
    858
    Alexey Smirnov
    Feb 16, 2007
  3. Ralph Hartman
    Replies:
    1
    Views:
    207
    Raghavendra T V
    Aug 25, 2004
  4. D
    Replies:
    0
    Views:
    217
  5. Jay
    Replies:
    2
    Views:
    86
    Stephen Chalmers
    Sep 26, 2005
Loading...

Share This Page