mssqltips logo

Join Multiple SharePoint Lists to build SQL Server Reporting Services SSRS Report

By:   |   Updated: 2019-03-13   |   Comments   |   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)
"_Demo: SQL Server Instances" List  - lookup column

Populate the Servers list:

Servers List Content

Populate the Instances list:

Instances List Content

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

SSRS Report - Data Source

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

SSRS Report - Dataset

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:

SSRS Report - Data Source - Query Designer warning

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):

"_Demo: SQL Server Instances" – Update Server Name lookup column

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

Updated "_Demo: SQL Server Instances" list (with total memory column)

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

SSRS Report - Data Source - Add lookup columns in Query Designer

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

Add Chart

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

Chart Data

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)
Series Properties - expression as value field

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

Series Properties

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

"Max_Instance_Memory" Series Custom Legend

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

"Max_Instance_Memory" Series conditional Background
=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:

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.

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:

Use custom Data Labels
Series Data Label expression
="#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:

Labels warning

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):

Series Data Label conditional font color
=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.

Legend Location

The final report is going to look like this:

Final Report

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.


Last Updated: 2019-03-13


get scripts

next tip button



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

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools