Build a SharePoint Chart on Multiple Lists from Across Multiple Site Collections

By

Using the Lightning Conductor Web Part, you can rollup SharePoint lists from many site collections (Nothing new there, we’ve been doing it for nearly 10 years). However, a neat little trick is to use our powerful SharePoint Chart web part to build charts on data that comes from other web parts. These ‘other web parts’ include our Data Viewer, Out of the Box Business Connectivity Services web parts, and of course our Lightning Conductor Web Part. In this example we will build a SharePoint Chart on Multiple Lists.

So what’s the benefit of that? Well, users like to see data visually! Rather than display a grid of task lists rolled up from multiple site collections, you could display a Pie Chart of tasks by Task Status or Priority. You could chart custom lists or content types, as well as Document libraries.

Let’s get started….

We’ll assume here that you have already configured the Lightning Conductor web part. If you haven’t, then check out the Tutorials on how to configure the Lightning Conductor here: https://lightningtools.com/lightning-tools-tutorials/lightning-conductor-tutorials/sharepoint-task-rollup/

Build a SharePoint Chart on Multiple Lists

The first task is to add the SharePoint Chart web part to the page that contains the Lightning Conductor. You can then Edit your page, and then choose Edit Web Part from the context menu of the Lightning Conductor. Click the Edit menu again of the Lightning Conductor, and choose connections. You can then provide a connection to the SharePoint Chart web part.

Connect the SharePoint Chart to the Lightning Conductor
Connect the SharePoint Chart to the Lightning Conductor

Note: As well as connecting to web parts, the SharePoint Chart can connect to data sources directly such as Microsoft SQL, Oracle, BCS, XML, Excel SharePoint Lists and more.

Once the web parts are connected, we can begin to build our SharePoint Chart.

Building the SharePoint Chart

To build the SharePoint Chart or SharePoint list items from many site collections, simply click Edit this Chart from the SharePoint Chart web parts’ context menu.

Edit the SharePoint Chart On Multiple Lists
Edit the SharePoint Chart

Set the Data Source to be ‘Web Parts in the Current Page’. Then click the button ‘Get Provider Parts’

Set the SharePoint Chart to the Connected Web Part
Set the SharePoint Chart to the Connected Web Part

Once you have selected the Lightning Conductor, you can click ‘Preview Data’ to see the data that is rolled up within the Lightning Conductor.

Preview your SharePoint Chart On Multiple Lists Data
Preview your SharePoint Chart On Multiple Lists Data

Now we have verified that this is the data we wish to see, we can get on with configuring the Chart. In this example, we will display the number of tasks that we have to start per qtr. We will then configure a drill down allowing us to see the number of tasks per month, and then per day if required. There are many scenarios that we could Chart using the Lightning Conductor when it is rolling up SharePoint lists. Consider a Pie Chart on the number of Tasks by Priority, or the Number of Tasks Assigned to each Team Member and so on. To see a Chart on the Tasks by Start Date, choose the Link Title and the Start Date column from the list of available columns. There are lots of other types of SharePoint Chart on Multiple Lists that you could build.

Select the Columns for Your SharePoint Chart
Select the Columns for Your SharePoint Chart

Next, Enable Grouping of the data and Group the data by Start Date.

Group the Columns in Your SharePoint Chart
Group the Columns in Your SharePoint Chart

You will need to set an aggregate function on the Link Title Column. So in this example, we will choose Count.

Set Aggregate Functions in the SharePoint Chart
Set Aggregate Functions in the SharePoint Chart

Now for the cool bit. Let’s choose our Chart! There are loads of Chart types to choose from and refine. You can choose from Single Series, Multi Series, Stacked, Scatter, and Mixed Charts all in 2D or 3D. The Chart Preview is on your data, so you can always see how your chart will appear.

Choose Your SharePoint Chart Type
Choose Your SharePoint Chart Type

We’ll set the Chart Title to ‘Tasks per Quarter’. We’ll then get this to change dynamically when drilling down into the month or day view.

Set the SharePoint Chart Title
Set the SharePoint Chart Title

Let’s fast forward through the other cool settings, and select the Drill Down tab. Under Drill Down, we will get the chart to change to month and day grouping.

Set the SharePoint Chart Drill Down
Set the SharePoint Chart Drill Down

Now let’s choose Finish and see the Chart on the page! You can see your aggregated tasks from many site collections within the Lightning Conductor with conditional formatting, and the chart displaying the number of tasks per qtr.

Finished SharePoint Chart
Finished SharePoint Chart

Wow, The last QTR looks busy! Let’s click it to drill down to the QTR…

Finally, we’ll add another Chart to show the priority of tasks!

SharePoint Charts on a Page
SharePoint Charts on a Page

And THAT is how you can build a SharePoint Chart on Multiple Lists! We hope you enjoy the post! To find out more, follow our tutorials: https://lightningtools.com/lightning-tools-tutorials/ or go ahead and download a trial!

<Brett/>