Roll up large amount of data from multiple lists

Question

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.

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

Answer 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=)

Answer 2

"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.

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