locked
Count data columns from a list, how to? RRS feed

  • Question

  • Hi,

    Im Using MOSS 2007 in our organization, and i have a custom list that our employes used frequently.

    this list has few columns, one of the column contain the name of the user that add an item.

    i want to make a web-part that will show a list who count how much items every user add to this list.

    For example:

    This is the custom list that has 2 items:

    #1 item

    User: John

    Job: Agent

    #2 item

    User: Jake

    And this is the web part that i want to show:

    User        Number of items

    John                      1

    Jake                      1

    Can you help me with that?

    Thanks

    Sunday, June 24, 2012 1:09 PM

Answers

  • Hi Erez1988,

    You can try the following code in your webpart to generate desired results:

    // Open the SPWeb containing the SPList that is to be processed
                using (SPSite site = new SPSite("http://sharepoint:2222/site"))
                {
                    using (SPWeb web = site.OpenWeb())
                    {
                        // Get reference of the SPList
                        SPList list = web.Lists["Custom List Title"];
    
                        // Fill DataTable with items inside SPList
                        DataTable dt = list.Items.GetDataTable();
    
                        // Generate necessary results containing 2 columns 'User' & 'NumberOfItems' by grouping the DataTable based on 'User' column using LINQ.
                        var result = dt.AsEnumerable().GroupBy(r => r.Field<string>("User")).Select(grp => new {User = grp.Key, NumberOfItems = grp.Count()});
    
                        // TODO: Bind our results to GridView control.
                    }
                }

    The anonymous type 'result' contains the information that you want to display. It can be bound to any .net control (say GridView) to display its results.

    The above code using LINQ on DataTable. So make sure you add a reference of 'System.Data.DataSetExtensions.dll' to your project to avoid compilation error.

    Happy Coding!

    Regards,
    Devang Bhavsar

    Sunday, June 24, 2012 2:33 PM

All replies

  • Hi Erez1988,

    You can try the following code in your webpart to generate desired results:

    // Open the SPWeb containing the SPList that is to be processed
                using (SPSite site = new SPSite("http://sharepoint:2222/site"))
                {
                    using (SPWeb web = site.OpenWeb())
                    {
                        // Get reference of the SPList
                        SPList list = web.Lists["Custom List Title"];
    
                        // Fill DataTable with items inside SPList
                        DataTable dt = list.Items.GetDataTable();
    
                        // Generate necessary results containing 2 columns 'User' & 'NumberOfItems' by grouping the DataTable based on 'User' column using LINQ.
                        var result = dt.AsEnumerable().GroupBy(r => r.Field<string>("User")).Select(grp => new {User = grp.Key, NumberOfItems = grp.Count()});
    
                        // TODO: Bind our results to GridView control.
                    }
                }

    The anonymous type 'result' contains the information that you want to display. It can be bound to any .net control (say GridView) to display its results.

    The above code using LINQ on DataTable. So make sure you add a reference of 'System.Data.DataSetExtensions.dll' to your project to avoid compilation error.

    Happy Coding!

    Regards,
    Devang Bhavsar

    Sunday, June 24, 2012 2:33 PM
  • You can use "Group By" option for the view of your data. Just create/customize your existing view and select the User column in GroupBy section. It'll give you a count of what you're looking for.


    Pman
    http://www.pmansLab.com/

    Sunday, June 24, 2012 3:06 PM