SharePoint Lists as Data Sources

One of the newer methods used as a data source are SharePoint Lists. SharePoint lists as data sources one of the newer methods of data sources is the SharePoint Lists. Although this type of data source removes the need of an available database for you reports, conversely, it removes the available of useful database features as well.  Connecting to a SharePoint lists is very easy. That is one of the advantages of using this type of data source. It’s typically an HTTP URL such as the following...

https://intranet.acmetools.com/_layouts/15/viewlsts.aspx

Though SharePoint lists have one of the simplest methods of connections, it also provides one of the most costly drawbacks in the inability to develop useful queries against the SharePoint lists. This becomes a bigger issue when you are using multiple SharePoint list in the report and data needs to be joined across different lists. Unfortunately, there is no natural JOIN syntax that you can use in the Visual Studio/Report Builder query designer that will allow you to join two different SharePoint Lists into one query/dataset. Therefore you must create multiple datasets and join them using the combination of the SQL Expression Language commands.

For example, if we had three different SharePoint Lists as follows...

In this example if we had to display the Customer with the Sales total, we would have to join the Sales List to the Location List and eventually to the Customer List. This is easy to do in the typical relational database with a simple join command.  Again, since we can join SharePoint lists within the query builder, we have to rely on the SSRS expression language for make these connections. To accomplish this we using the following SSRS functions LookupSet , Join and ReportItem syntax (since we must join a to a third list).

If we simply joining the Sales List to the Location List, all we have to do is use the LookupSet function as follows.

=Join(LookupSet(Fields!LocationID.Value, Fields!LocationID.Value, Fields!CustomerID.Value, “Location”)

If we must connect to the third Sharepoint Customers to get the Customer Name, we have to use the ReportItem expression. This is because you cannot use nested LookupSets in the SSRS Expression language. There use are required to then using the above command to create the CustomerID value to display in a Tablix, Matrix, or List, and then using the ReportItem to use that one of the values in another LookupSet Expression. So if we have already created a ReportItem for the CustomerID using the above expression, then we are using the following to pull in Customer name.

=Join(Lookupset(ReportItems!CustlocID3.Value,Fields!CustomerID.Value,Fields!LastName.Value,"Customers"))

In this expression we use the CustlocID3 that is the ReportItem name in the Tablix (You can find the name of the textbox in the general sections of the Tablix (or Matrix) properties.

Microsoft ability to allow SharePoint Lists as a data source adds some real advantages for enterprise customers. This is particular true is you need some centralized data source but the IT department won’t give out another DB license or access. SharePoint Lists has some great advantages but to get around some of the major drawbacks it will require you to use more of the SSRS expression language .