Join Multiple SharePoint Lists to build SQL Server Reporting Services Report

By: Svetlana Golovko   |   Updated: 2019-03-13   |   Comments (1)   |   Related: > SharePoint

Problem

We have a couple of SharePoint lists that have SQL Server inventory related information. We would like to join these SharePoint lists to visualize memory distribution on our SQL Server hosts in a SQL Server Reporting Services (SSRS) report.  The task seems to be tricky as there are limitations in SSRS and it looks that we can't directly join two SharePoint lists. How can we achieve the results?

Solution

The task I needed to accomplish was to pull data together from multiple SharePoint lists to help us with managing some information about our SQL Server environment.  In particular, I was trying to determine the memory allocation on each server where there are multiple instances of SQL Server running. 

It's pretty common to have multiple SQL Server instances installed on a single host. SQL Server supports up to 50 instances on a stand-alone server. If you don't configure memory properly on your SQL Server instances the Operating System (OS) may experience memory pressure and SQL Servers may become unresponsive. In order to avoid the OS memory pressure, you need to set a maximum server memory configuration option on each instance. This Microsoft guideline provides the following calculation for the "max server memory":

Reserve 1GB-4GB to the OS. Then subtract the equivalent of potential SQL Server memory allocations outside the max server memory control, which is comprised of stack size* calculated max worker threads+ -g startup parameter (or 256MB by default if -g is not set). What remains should be the max_server_memory setting for a single instance setup."

You can also read this tip to learn how to set up a fixed amount of memory for SQL Server for more information.

We need to create the report that will show how much free memory is left on the host to the Operating System (e.g. total host memory minus the sum of maximum memory of all instances on this host). We will use a 4 GB threshold (memory reserved for the OS) for the demonstration in our report.

To create our SSRS report we will need to use the "Microsoft SharePoint List" connection type in the report's Data Source. This tip has an example of the report that uses a SharePoint list as a Data Source.

Below are the steps to setup the SharePoint lists for our demo report and the steps to create the report.  At the end we will show a workaround on the SharePoint side as one of the solutions.

Create SharePoint Lists

Create the SharePoint lists with Servers (Hosts) and SQL Server instances information as follows:

List #1 (Servers/Hosts list): "_Demo: SQL Servers". Add the following columns to the list:

• "Server Name" (Single line of text)
• "Total Memory" (Number)

List #2 (Instances list): "_Demo: SQL Server Instances". Add the following columns to the list:

• "Instance Name" (Single line of text)
• "Max Instance Memory (MB) (Number)
• "Host Name" (lookup to the "Server Name" column in the List #1)

Populate the Servers list:

Populate the Instances list:

Create the SSRS Report Using the SharePoint List Connection Type

We will use Report Builder to develop our report, but the steps using SQL Server Data Tools or Visual Studio will be almost the same. The output will be an *.rdl file that we can deploy to the Reporting Services Server.

We will start creating the report by defining a Data Source.

Select "Microsoft SharePoint List" as a connection type.

Enter a connection string for the SharePoint site that has your lists (that were created above).

Select "Use current Windows user" under the "Credentials" settings.

Now we will create a Dataset.

Use the Data Source that we have created earlier ("SharePointSite").

Click "Query Designer".

Select the "_Demo: SQL Server Instances" list. Now we need to add total server memory from another list. Our Instances list has a lookup column to the Servers list. Let’s see what we can do.

Click on the "_Demo: SQL Servers" list now:

You will get an error as on the screenshot above.

This action will remove the current query. Do you want to continue?

This is the expected error as SharePoint query can reference only one list.

Click "No" and save the Dataset as is.

In the following steps we will show you a workaround that you can use to bring the data to the report from another list.

Modify SharePoint List and Complete the Report’s Creation

This solution can be used if you have permissions to make changes to the SharePoint lists or if you can ask someone in your organization to make a change for you.

The workaround is to modify the instances list ("Host Name" lookup column). We will need to add a column from the servers list to show an additional field (total server memory):

Here is how the "_Demo: SQL Server Instances" list looks now:

Note, that we didn’t have to enter the data for the new column (highlighted), it comes with the updated lookup column.

Now we can continue our report’s development.

Open the Dataset created before. Click on the "Query Designer". Add the "Host Name:Total Memory (MB)" and the rest of the columns we need and click "OK":

In the Report Builder insert the new Chart and select the "Stacked Bar" chart type:

Click on the Chart and set the chart’s data like the following:

Category Groups – "Host Name".

Values (Series).

Sum of maximum memory on all instances (per host):

=Sum(Fields!Max_Instance_Memory__MB_.Value) 

Add another value (series) as an expression:

=Max(Fields!Host_Name_Total_Memory__MB_.Value)-Sum(Fields!Max_Instance_Memory__MB_.Value)

Click on the second series we have created (with the expression) and select "Series Properties":

Set the Custom legend text: "Free MB left for OS (number displayed)":

Set conditional background fill (to display red bars when there is no memory left for the OS):

=iif(Max(Fields!Host_Name_Total_Memory__MB_.Value)-Sum(Fields!Max_Instance_Memory__MB_.Value) >0 , "Automatic","Red")

Click "OK" and set the Custom legend text for the second series as "Total MB used by all instances".

Click "OK" and make the following changes to the Chart:

Remove Vertical and Horizontal Axis Titles

Set the following Horizontal Axis Options:

Set the "Maximum" value (60,000 in our example) close to the maximum memory you have on your largest host. You can also add a parameter to the report and use an expression here.

Read these tips about SSRS parameters.

Hide Vertical Axis labels.

Right click on a series bar on the chart and enable the "Show Data Labels" option.

Click on the label on the chart’s bar and set the following expression as the label’s data:

="#VALY"&"  -  "&max(Fields!Host_Name.Value)

We used extra spaces in the expression above to align the labels a little bit better.

Click "Yes" to confirm:

Set conditional font color for the label (to have the label in red font if the minimum free memory for OS threshold is lower than a specified value):

=iif (Max(Fields!Host_Name_Total_Memory__MB_.Value)-Sum(Fields!Max_Instance_Memory__MB_.Value) <4000,"Red", "Automatic") 

"4000" is the minimum amount of memory in MB we want to leave for the OS. You can add a parameter to the report or use another calculated value. We used 4 GB here for the demonstration purposes.

Move the Legend to the top.

The final report is going to look like this:

This chart shows us that:

• The DEMOSRV4 server doesn’t have any memory left for OS
• The DEMOSRV3 server doesn’t have any memory left for OS and total maximum memory for all instances on this host exceeds total OS memory by 2 GB
• The DEMOSRV1 server has only 2 GB of memory left for OS which may not be enough
• The DEMOSRV2 and DEMOSRV5 servers have acceptable memory allocation. DEMOSRV5 runs SSAS and SSRS in addition to the Database Engine services, so we left more than 4 GB of memory to the OS.

You can write a PowerShell script to find memory left for the OS. In this tip we wanted to provide a visual representation of the memory allocation as well as give a tip on how to join two SharePoint lists.

In our next tip we will provide another solution that could be used for joining multiple SharePoint lists.

Next Steps

• Read other SharePoint tips here.
• Read Report Builder tips here.
• This tip has another solution on joining the SharePoint lists.

About the author

Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips