I recently had the pleasure of attending SharePoint Saturday: The Conference (thanks again to Dux Raymond Sy and Innovative-e for putting it together!), and one of the most common requests that I heard was for the ability to aggregate data not across just sites, but across Site Collections as well. Did you know that Bamboo’s List Rollup Web Part allows you to achieve this today? It sure can, and I’m here today to show you how.

The Problem

Ability to aggregate information across sites into a single view. SharePoint provides an aggregation Web Part called the Content Query Web Part, but it’s limited to a single Site Collection.

The Goal

Aggregate all Calendar items from all sites and site collections into a single view within the Top Site Collection.

The Tools

• SharePoint 2010 (SharePoint Foundation 2010 & SharePoint Server 2010). NOTE: this is also supported in SharePoint 2007 (WSS v3.0 & MOSS 2007)
• Bamboo List Rollup Web Part (Release 5.0)

In this demonstration, I will be using release 5.0 of List Rollup Web Part (your specific file version may vary as long as you are running release 5.0.0 or greater). The Bamboo List Rollup Web Part comes with two “flavors” of aggregation: Schema method and Classic method. The Schema method is best used to provide faster performance, but aggregation is limited to lists of the same List Type, and columns that are meshed together must be of the same column type. Classic method provides the ultimate flexibility, however, allowing users to aggregate lists of any type, and to mesh columns of any column type (matching only on column display name), but users will experience slower performance. In this article, I will cover both methods.

The Data

Within my SharePoint-80 Web Application, I have 3 Site Collections:

• Top Site Collection, which contains
  • Two Site Collections; each sub-Site Collection also contains two Sites.

The Steps

After successfully installing the fully-functional 30-day trial of List Rollup Web Part, we need to add the product to the page. As noted, the Bamboo List Rollup Web Part provides two types of aggregation: Schema and Classic. Each type of aggregation is performed slightly differently, as shown below.

Aggregation Type: Schema

When you add a new Web Part to the page, select Bamboo Solutions from the Categories list box, select Bamboo List Rollup Schema Designer, and then click Add. Finally, save the page you added Bamboo List Rollup Schema Designer to. NOTE: if you are unable to locate the Bamboo List Rollup Schema Designer, this may be due to the fact that the Site Collection Feature has not been activated. If this is the case, login as the Site Collection Administrator, go to Site Actions -> Site Settings and select Site collection features in the Site Collection Administration section, then locate the feature Bamboo List Rollup Schema Designer and click Activate.  While here, confirm that the Bamboo List Rollup Grid View is also Activated as we will be using this later. Now you should see the Web Part listed in the category Bamboo Solutions.

In the Web Part, select New Schema to define the rules of the aggregation. The first step is to Configure Discovery Mode, which will allow you to choose from Select Individual Lists, Automatically Find Lists in Subsites and Automatically Find Lists in Site Collection.  In this case, for our demonstration, we will select Automatically Find Lists in Site Collection and save the settings:

In Step 2, we want to select the site and then the list we want to automatically discover.  In our case, I am going to enter the URL (using relative URL) of Site Collection A, then select the list Calendar (which means that List Rollup Web Part will locate all lists of type Calendar in Site Collection A), and click Save.

Now we want to add this discovery rule to the Included Lists section of the schema creation screen clicking Include List in Rollup in Step 3.

To provide optimal performance, List Rollup Schema mode utilizes SharePoint aggregation services. Unfortunately, the aggregation services don’t have the ability to auto-discover lists ACROSS site collections. To include lists in my aggregated view from a different site collection, I will simply be repeating Steps 2 and 3.

In Step 2, I will change the selected URL to ./sites/b to select my second site collection, then select Calendar from the Select a List drop-down. Next I will click Include List in Rollup in Step 3.  Now I have two site collections listed in the Included Lists section of the screen:

If you have additional site collections, repeat steps 1-3 to include all of your site collections. The next step is to define which columns to include in the schema.  In Step 4, click Select Columns.  The first step is to define your Template List.  The Template list is the master list you want to use to select columns to be displayed in the aggregated views, along with your definition of the format for the columns based on the column settings in that list (for example, how many decimal places are displayed in the Number column, or for Date and Time columns if only Date is shown). I have configured my demonstration as follows:

In Step 5, you can define an optional filter so that only relevant data is returned.  In my demonstration, since my sample data is very small, I have selected, All Items.

Step 6 is only enabled if the aggregation is on lists of type: Calendar.  This defines that if items in the calendar list have recurrence rules, should the items be expanded. If this option is not enabled, the aggregated view will only display the Series item and not the individual occurrences. In my demonstration, I have this optioned turned off.

In Step 7, you can exclude lists from the aggregation.  Although List Rollup Web Part follows SharePoint item level security and does not display items users do not have access to, there are some cases where you do not want the list to be included in the aggregated view.  That is where this step comes in.

Last but not least, in Step 8, provide a name and description schema.

Finally, select Create/Update Schema. You have now successfully created an aggregated view of all Calendars in your site collections. Now we want to display the view in your site.  That is where the Bamboo List Rollup Grid View Web Part comes into play. The Bamboo List Rollup Grid View provides the means to display the aggregated data to users. You can also display the data through other Bamboo Web Parts which will be detailed later.

When adding a Web Part to the page, select Bamboo Solutions in the Categories list and then locate and add Bamboo List Rollup Grid View.

When the Bamboo List Rollup Grid View is added to the page, we define what schema to display and how it will be displayed.  First, provide a name and description for the view you are going to display.  Note that the Name and Description values are automatically copied to the Web Part’s Title and Description fields for your convenience. Now enter the URL where the Schema resides. In my case, the schema resides in the same site as the grid view, so I am entering the relative URL ./ and selecting Load Schemas to view all available schemas in that site.  You can connect to schemas across site collections also!

After selecting the Schema to display, now we define which columns from the schema to include in the grid view and their order.

Next, select the default sorting of the grid view.

Filtering allows you to narrow the results of the aggregated view further.  NOTE: This filter will be applied ON TOP of the filter defined in the original schema:

Similar to SharePoint views, you can also group based on column values:

Item Limits help improve performance even more with the following settings:

Finally, define which features are available to the user in the grid view:

After configuring all the settings, click Show Data and enjoy the fruits of your labor!

Aggregation Type: Classic

As mentioned earlier, if you are seeking to aggregate lists of different types (for example, a mix of Calendars, Tasks, and Custom lists), List Rollup classic is the option to choose.

When you add a new Web Part to the page, select Bamboo Solutions from the Categories list box, select Bamboo List Rollup Classic Edition, and then click Add. Finally, save the page you added Bamboo List Rollup Classic Edition to. NOTE: If you are unable to locate the Bamboo List Rollup Classic Edition, this may be due to the Site Collection Feature having not yet been activated. If this is the case, login as the Site Collection Administrator, go to Site Actions -> Site Settings and select Site collection features in the Site Collection Administration section, then locate the feature Bamboo List Rollup Classic Edition and click Activate.

The first step is to enter the Site URL of the location of the list you want to aggregate.  In this case, I am entering the site URL of Site Collection A and then clicking the green arrow. Notice the drop-down Lists now display all lists and libraries of that entered site:

Next, I will select the list Calendar in the Lists drop-down, thus populating the Views drop-down with all the views available in the selected Calendar list:

 Each list you include in the rollup will be filtered individually, either the selected view or the CAML entered. For example, if in the Views drop-down I select Current Events, only events that occur after the current time will be displayed for that list. If I enter CAML into the CAML Query field, the filters defined in the selected view will be ignored and List Rollup will instead use the CAML Query to filter the selected list.  In this demonstration, I will simply select All Events:

Now I want to auto-discover all lists in the site collection for aggregation.  Bamboo provides two types of auto-discovery:

• Match List name means it will aggregate all lists that contain the same display name; or
• Match List type means it will aggregate all lists that are of the same list type.

I will be selecting Match List name because I do not want ALL lists of type Calendar, only lists called Calendar. The next setting is Discovery Path.  Note that Discovery Path is limited to the site collection. In my case, I will be finding all matches in sub-sites since the selected list is from the top level site of the site collection. The final setting for Auto-Discovery is scope limits.  This improves the performance of the aggregation.  Modify the limits to fit your needs.  You can even turn off the limits unselecting the checkboxes, basically making the limits unlimited:

The last step in this process is to define any lists you want to exclude from he rollup.  Although List Rollup Web Part follows SharePoint item level security and does not display items users do not have access to, there are some cases where you do not want the list to be included in the aggregated view. That is where this step comes in.

After performing all the configuration steps described above, click Include List to add the defined list to the Included Lists section:

Similar to Schema aggregation, we are unable to discover lists ACROSS site collections.  As a result, we need to repeat the above steps for each site collection. Each time, the included list is added to the central Included Lists section of the Web Part. Now we have to select the “Master List.”  Notice to the left of each item listed in the Included Lists section there is a radio button.  Selecting one of the included lists means that the selected view of that list will define which columns are displayed in the aggregated view.  This selection also defines the format of the columns, such as how many decimal places the Number column will display:

After adding all the site collections and selecting the “Master List,” we then define the Name and Description of the rollup:

Since Classic aggregation allows the rollup of any list type into the same view, we do not use the SharePoint aggregation service but our own service.  This is slower than using SharePoint so, as a solution to provide faster performance, List Rollup Classic Edition includes a cache feature.  It is suggested that you set the limit to at least 10 minutes for optimal performance. Another method to improve performance is limiting the number of items displayed in each page of results:

 Finally, you can optionally select to expand recurring events selecting the checkbox Expand. If this is not enabled, recurrences are displayed as Series only:

After completing the configuration, click Create and then refresh the page.  Upon doing so, a new Data View Web Part will appear on the page, displaying the results of the aggregation.

Other Viewing Options

If a simple list of items do not satisfy your business requirements, Bamboo Solutions has a number of products that can consume the aggregated views (both Schema and Classic types):

• Alert Plus – Save time, minimize inbox traffic, and keep projects on track automatically sending customized, conditional email alerts and reminders to users or a group of users.
• Calendar Plus Web Part – Keep your company’s events and initiatives on track and up-to-date with the Calendar Plus Web Part.
• Chart Plus Web Part – Spice up your SharePoint sites with Bar, Line, Pie, and Bubble charts (to name just a few).
• Data-Viewer Web Part – Add conditional formatting, calculations and nesting rollups in a grid view.
• List Search Advanced – Search your aggregated view from a single location with List Search Advanced Web Part.
• Mini-Calendar Web Part – Display items in a miniature calendar.  Great for pages with only a small amount of real estate available.

---