1

I have a list with "Created by(person-default), Period(single line of text), Team(single line of text)...." fields. More than an entry is allowed per user.

Now,

  1. I may have to find the list of unique/distinct users who submitted the entries against the given period
  2. Number of users(distinct/unique) against the given period & Team.

Would that be possible with Listdata.svc? or how to achieve this?

Update: 12-Sep-2014


The way I could do is:

    static void Main(string[] args)
    {
        using (SPWeb web = new SPSite("http://intranet-dev.local").OpenWeb())
        {
            // Create a List Object
            SPList myList = web.Lists["UniqueUsers"];

            // Create a Query Object
            SPQuery curQry = new SPQuery();
            //Write the query (I suggest using U2U Query Bulider Tool)
            curQry.Query = "<Where><Eq><FieldRef Name='Title' /><Value Type='Text'>TestB</Value></Eq></Where>";

            if (myList != null)
            {
                //var uker = myList.Items.Cast<SPListItem>().Select(itm => GetListItemAuthorEmail(itm)).Distinct();
                var uker = myList.GetItems(curQry).Cast<SPListItem>().Select(itm => GetListItemAuthorEmail(itm)).Distinct();

                foreach (string str in uker)
                {
                    Console.WriteLine(str);
                }
            }

            Console.ReadKey();
        }
    }
    static public string GetListItemAuthorEmail(SPListItem spListItem)
    {
        string Email = string.Empty;
        var spFieldUser = spListItem.Fields.GetFieldByInternalName("Author") as SPFieldUser;

        if (spFieldUser != null && spListItem["Author"] != null)
        {
            var fieldValue = spFieldUser.GetFieldValue(spListItem["Author"].ToString()) as SPFieldUserValue;
            if (fieldValue != null)
            {
                var spUser = fieldValue.User;//Get the SPUser
                Email = spUser.Email;//Get the login name from SPUser
            }
        }
        return Email;
    }

But not sure if it can be achieved without much of load on the server.

  • 1
    In order to answer your question you need to provide more details about your list fields, at least field types..? – Vadim Gremyachev Sep 9 '14 at 20:18
  • 1
    have updated the question.. – Karthikeyan Sep 9 '14 at 21:44
2

Yes you can achieve this , and you will need to do enhancement the same .

http:///_vti_bin/listdata.svc/Tasks?$filter=Team eq 'Team3'

http://www.c-sharpcorner.com/uploadfile/anavijai/rest-api-in-sharepoint-2010-for-listdata-svc-part-1/

2
+25

Both of these can be done with views using Group Bys.

  1. Create a view called ByPeriodPerson and in the Group By section of the view definition, select Period first group by the column and then select Person for the second group by the column. The resulting view will display a distinct set of Periods - when you expand each period you will have a distinct set of Persons - expand each person to see their rows for that Period. (see image for view definition) enter image description here

  2. Same as 1. Create a view called ByPeriodTeam and in the Group By section of the view definition, select Period first group by the column and then select Team for the second group by the column. The resulting view will display a distinct set of Periods - when you expand each period you will have a distinct set of Teams - expand each team to see the Team rows for that Period. (see image for list view)

enter image description here

1

ODATA Distinct is not possible unfortunately:

You may be able to filter distinct values out afterwards ?

0

I did this with XSLT in a DataViewWebPart, sorted by the key for which I wanted to find the unique values. (H/T: Marc Anderson)

You edit the default for-each select="$Rows" see line 3 (in my case the field was ClientName)

enter image description here

In this case, I connected THIS web part with only unique client names, to another web part showing more fields and records for the same list-- documents with a matching client name. [Hence the dvt.gencurselkey call].

Another way to go would be to export the list into an excel query and do a pivot table by those fields.

0

Another solution is to use linq distict. This link shows how to do it in sharepoint:

https://stackoverflow.com/questions/3660110/selecting-rows-with-distinct-column-values-using-linq

-1

You can get data into data table and do a distinct with group by clause.

We're looking for long answers that provide some explanation and context. Don't just give a one-line answer; explain why your answer is right, ideally with citations. Answers that don't include explanations may be removed.

  • In SharePoint you don't normally access the database directly. Can you elaborate on the answer further to explain how this would solve the OP question? Thank You! – Benny Skogberg Sep 16 '14 at 8:11
  • Hey Benny, first query list for particular date and time. you will get SplistitemCollection,Convert into C# data table.And then first do group by period.And for the second one,group by time and team and you will get distinct record. – user22236 Sep 16 '14 at 9:57
  • Great! Can you update your answer with this info, and possibly add the code snippet to do this? Thanks! – Benny Skogberg Sep 16 '14 at 13:01
  • Hey Benny :i have added post here as i am not allowed to post answer.sharepointundefind.wordpress.com – user22236 Sep 17 '14 at 7:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.