Our use of cookies

We use cookies to tailor your experience, gather analytics, and provide you with live assitance. By clicking "Accept" or continuing to browse our site you agree to the use of cookies. For more details please read our Cookie Policy.

Using Tableau to report on SharePoint lists and libraries: A step by step guide

Tableau is one of the most renowned and accepted Business Intelligence tool for visually analysing the data by organisations all over the world. Tableau allows its users to create and distribute an interactive dashboard depicting the trends, variations, and density of the underlying data either in the form of graphs or charts. Tableau can connect to almost all types of data sources for fetching data. Data sources can be files, relational and Big Data; once the data is fetched it can be processed to meet the needs. Moreover, it allows data blending and real-time collaboration, which makes it very unique.

In this blog, we will cover:

  • How to generate a Tableau report using data from a SharePoint List
  • How we can integrate this Tableau report into a Modern SharePoint Site
  • How to create a replication from the SharePoint list to SQL Server using SQList. This will create all the schema and keep the SQL data and schema in sync with the SharePoint list.

Once the data is in SQL Server, we will use Tableau to create reports using the exported data. The data export process from SharePoint lists to SQL Server can be configured to be either a continuous process or an On-demand one.

By the way, you can download a fully working version of SQList here

Here are the 7 simple steps to create a working Tableau report on a SharePoint list or library.

Step 1 – Connect to your SharePoint lists inside SQList

Launch the SQList Manager. If you haven’t set up a replication yet, your default screen would look like the diagram below.

Click on the ‘New replication’ button in the manager. We will be setting up a replication from scratch. As soon as you select to create a new replication, SQList Manager will take you through a step by step wizard.

The first step will be to specify the details of the SharePoint Connection. The main details of a SharePoint connection include:

  • Site Name: An arbitrary name of the connection
  • Site URL: The URL of source SharePoint Site. It is important to note that it is the URL of the main site not a specific list or library.
  • Authentication Mode: The type of authentication the SharePoint Site is using.
  • Credentials: Username and Password for the user

Once you have provided the connection details, you can test the connection and save it for further use and click on Next button.

Step 2: Choose the SQList replication type

Select the type of replication you would like to perform, either specific lists and libraries or all lists of the same name across a site hierarchy.

From the perspective of our blog, we will opt for the first option and export a specific SharePoint list which we will then generate a report on in Tableau.

Step 3: Select the SharePoint list(s) to replicate to SQL Server for reporting in Tableau

Next, you are presented with a view where you can select which lists or libraries to export. We have selected the “Employee Salary” list for the purpose of the blog.

Step 4: Set up the SQL Server connection

Now as you have set up the SharePoint Connection, the next step for you will be to set up the SQL Connection. You will need to provide the basic details related to the SQL connection:

  • Connection Name: An arbitrary name for the connection.
  • Server Name: You need to specify the SQL server instance name.
  • Database Name: You need to specify the name of the database where the exported data will be saved.
  • Database Engine: You need to specify the database engine version
  • Authentication Mode and credentials: Lastly you need to specify the authentication mode to be used to connect to SQL server and the appropriate credentials (User Name and Password).

Step 5: Start the SQList windows service

The replication configuration is almost complete we are left to check “Start the SQList service after saving the profile” so that the SQList windows service starts replicating the data to SQL server immediately

As soon as you click “Finish”, the replication configuration is complete and the SQList starts the replication of the SharePoint list. You can always check the status or logs of the replication in the “service status” tab.

For reference purposes, let’s look at the structure and data of the list that we have exported. The Employee Salary list contains compensation data for employees in a company. (The data is random and names are chosen on random basis J )

Step 7: Create Report using Tableau

Now as we have imported the SharePoint list data into SQL Server, we will now use it to generate a sample report (Chart).

Let’s launch the Tableau Desktop app. For the purpose of this blog, I am using version 10.5 this will work with previous versions as well.

As soon as you launch Tableau, we need to connect to a server which will serve as a data source. In our case, we will be using SQL Server as the server. Click on ‘Microsoft SQL Server’.

As soon as you click on SQL Server, it will ask you for credentials with the help of which it can connect to the server. You can specify the same details which we specified in our SQL connection in the previous step as that server will serve as our data source for the report.

Once you have provided the correct SQL Connection details and signed in, then you need to specify the database. Our Employee Salary list was exported to the Report database. So, I will select Report database.

As soon as I select the  Report database, the table pane gets updated with all the tables present in the database. We are concerned with the  EmployeeList_EmployeeSalary.

Drag the Employee Salary table to the table section and click on “Update Now” in the bottom section. Then the bottom pane will show the data present in the table.

Since we will be using a single table as the source we can go ahead with the creation of the chart. If your chart requires more than one table then you can drag all the required tables and join them on the appropriate columns.

The next step will be to create a Sheet where we will plot a chart. Click on the Sheet at the left bottom. It will open up a new sheet. Now you need to specify the type of chart. We will select a bar chart for our purpose and we will show the employee and salary against each player. Since, we will be showing Employee VS Salary in the graph, drag the Name column in the Columns section and salary in the rows section as highlighted below. You can see that a chart will be rendered now.

So, the chart creation is complete and you can save the report for further use. We have chosen a very basic use case for the purpose of the blog but it can be extended a lot further depending upon the requirement, whether you want to export huge chunks of data residing in different lists (supported by SQList). Even if you want to generate a complex report with data coming from different tables is easily feasible from tableau prospect.

Feel free to post questions and feedback. Thank You!

Latest articles

Our Favourite Picks from Power BI Global Summit 2023

17 Mar 2023

Power BI Summit is the biggest Power BI conference of the year which ran virtually from 6th March to 10th March. It brought speakers from the […]
[read article]

AxioWorks Newsletter February 2023: Development of SQList new release is in full swing

28 Feb 2023

Hello Reader, Welcome to another exciting issue of our monthly Newsletter! Each month, we try our best to bring you content that is relevant and […]
[read article]

MS List Templates bundled with Power Automate Flow

20 Feb 2023

Microsoft Lists previously known as SharePoint Lists is the go-to option for maintaining and storing data in the world of O365 and its usage has […]
[read article]

AxioWorks Newsletter January 2023: Kicking off 2023 with some great content

30 Jan 2023

Hello Reader, Welcome to another exciting issue of our monthly Newsletter! Each month, we try our best to bring to you content that is relevant and […]
[read article]

A pragmatic approach to obtaining value from your SharePoint data

20 Jan 2023

Organisations have been storing data in SharePoint for more than 20 years.  This data can be as simple as a List used by a small team or a complex […]
[read article]

Power BI Report Server : An On-Premises Report hosting Suite

16 Jan 2023

Power BI has become the most widely used tool for reporting due its user-friendly interface which allows business users to generate sleek and […]
[read article]

AxioWorks Newsletter December 2022: Good bye 2022, welcome 2023!

29 Dec 2022

Hello Reader, Welcome to the December issue of our monthly Newsletter! Each month, we try our best to bring to you content that is relevant and […]
[read article]

PowerApps Debugging and Troubleshooting made easy with Monitor

28 Dec 2022

Troubleshooting and debugging are an integral part of building and supporting a resilient app especially when it is built using a low-code […]
[read article]

AxioWorks Newsletter November 2022: Is it almost Xmas already?

29 Nov 2022

Hello Reader, Welcome to the November issue of our monthly Newsletter! Each month, we try our best to bring to you content that is relevant and […]
[read article]

Future of SharePoint in the world of Power Platform

29 Nov 2022

Microsoft SharePoint has been the driving force for enabling virtual team collaboration and the modern workspace revolution over the last three […]
[read article]

Get SharePoint and SQL news to your inbox

Stay up-to-date with industry news and trends, SQL and SharePoint innovations and all the latest from AxioWorks by subscribing to our monthly newsletter.

Subscribe to the AxioWorks newsletter

*