1

I have a site with several hundred of lists and over 100,000 items in total. I need a content roll up of the data to track the amount of items created per month/year.

I already took a look into:

  • Content Query Web Part: Apparently the threshold of 5,000 can't easily be exceeded. With regard to my amount of data (>100,000 items), this approach doesn't seem promising.

  • Content Search Web Part: As this web part is building upon the search functionality, it should work with more than 5,000 items. However, I can only return 50 items as a result, which is too little.

  • Linked Data Source and Data View Web Part: This was the promising approach. I created a linked data source and implemented it with a data view web part in SharePoint Designer. With right click -> 'Export to Excel' you could filter and count the data. However, it only exports the first page of results and ignores the other pages. Also the export ignores the filters (see picture) and thus I'd have to return all 100,000 items and filter them within Excel, which wouldn't work, because a limit of 50,000 items applies to data views. filtering in data view web part

Are there any ways to export a filtered data view web part? Is it even possible to roll up >100,000 items and export them to Excel?

Environment: SharePoint 2013 (+ Designer), only site owner permissions

  • 2
    I don't think you'll get very far doing it on the SharePoint side, you will most likely have to do it from the client side, multiple data connections in Excel pulling in the data or something like Power BI or SSRS to aggregate it into a cube which you can use in Excel. – Eric Alexander Sep 6 '16 at 12:56
  • we had a lot of threshold related issues in my team, so I want to know if do you have administrator rights? – Marcelo Espinosa Sep 6 '16 at 13:16
  • @MarceloEspinosa Nope, no administrator rights. – wheeler Sep 6 '16 at 13:29
  • Would you consider using CSOM or REST api to get all the data? You can create a html/txt file in SPD and write your code there. Then use CEWP point to the file. – YogaPanda Sep 7 '16 at 15:10
1

Short answer: No.

Long answer: Maybe.

One way could be to "split" the list using multiple views. This needs a filter-column which has an index and this in turn can be a new problem because you can not add a new index to a list which has already exceeded the threshold. A nice article on threshold (including some ways to overcome the limitations) can be found here. (The article is for SP2010 - but nothing has changed for that topic...)

Another possibility would be to use Excel/PowerBI or some other tool and "export" using search - as you have noticed the search results are paged, so you would need to query the search for all existing pages of the list. A good intro on using the REST-api for search can be found here. (Esp. have a look for the query-parameters &startrow=and &rowlimit=)

1

"Divide and Conquer"

If the performance is not a problem, I think the way to achieve this is using code: you can loop through all lists, and in every list run a query to get the items created by day, and get a sum by month.

I propose to loop though the web lists (and you can define which lists do you want to take in to account with the string listindex = "list1, list2, list3" and add all the results to a DataSet in a DataTable by Year basis (you can manipulate/export this dataset to your convenience --check the link at the bottom).

It has to run with elevated privileges so it can count all items without exception, and it will take it's time to run, as it doesn't have the best performance.

Dataset

Feel free to tune it up and modify the following code:

SPSecurity.RunWithElevatedPrivileges(delegate()
    {
    using (SPSite site = new SPSite("http://YOURSITEHERE"))
    {
        using (SPWeb web = site.RootWeb)
        {
            string listindex = "list1, list2, list3";
            foreach (SPList lista in web.Lists)
            {                            
                if (listindex.Contains(lista.Title))
                {

                    if (lista != null)
                    {
                        DataSet ds = new DataSet();

                        for (int a = 2016; a >= 2014; a--)
                        {
                            DataTable dt = ds.Tables.Add(a.ToString());                                                                                                                      
                            DataColumn colMonth = new DataColumn();
                            colMonth.DataType = Type.GetType("System.String");
                            colMonth.ColumnName = "Month";
                            dt.Columns.Add(colMonth);

                            DataColumn colCount = new DataColumn();
                            colCount.DataType = Type.GetType("System.String");
                            colCount.ColumnName = "Count";
                            dt.Columns.Add(colCount);

                            int countMes = 0;
                            for (int m = 1; m <= 12; m++)
                            {
                                DataRow dr = dt.NewRow();


                                string mes = "";
                                if (m < 10)
                                {
                                    mes = "0" + m.ToString();
                                }
                                else
                                {
                                    mes = m.ToString();
                                }

                                int diasMes = DateTime.DaysInMonth(a, m);
                                for (int d = 1; d <= diasMes; d++)
                                {
                                    string dia = "";
                                    if (d < 10)
                                    {
                                        dia = "0" + d.ToString();
                                    }
                                    else
                                    {
                                        dia = d.ToString();
                                    }

                                    string ano = a.ToString();
                                    string fecha = "";
                                    SPQuery query = new SPQuery();
                                    fecha =  ano + "-" + mes + "-" + dia;
                                        query.Query = "<Where><Eq><FieldRef Name='Created' /><Value Type='DateTime'>" + fecha + "</Value></Eq></Where>";

                                    SPListItemCollection listItems = lista.GetItems(query);
                                    if (listItems != null)
                                    {
                                        countMes = countMes + listItems.Count;
                                    }                                                                                              
                                }

                                dr["Month"] = mes.ToString() ;
                                dr["Count"] = countMes.ToString();
                                dt.Rows.Add(dr);

                                countMes = 0;
                            }

                        }



                    }
                }
            }

        }
    }
    });

DataSet to Excel

  • How would I run the code on the site? And what kind of permissions do I need for it? – wheeler Sep 7 '16 at 6:12
  • You can run that in a web, or in an event receiver, which kind of SharePoint project do you have? – Marcelo Espinosa Sep 7 '16 at 11:21
  • Unfortunately I don't have access to Visual Studio. – wheeler Sep 7 '16 at 11:42
  • Do you have access to SharePoint Designer? – Marcelo Espinosa Sep 7 '16 at 13:17
  • Yes. Can I add an event receiver with SPD? I have SP 2013, but only site owner permissions. – wheeler Sep 7 '16 at 13:35

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.