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

We are looking for a Sales and Marketing Manager!

18 Jun 2021

We are looking for someone with experience in the SharePoint and BI arenas to take on the role of Sales and Marketing Manager. You will be able to […]
[read article]

Mover : Only Migration tool you will ever need to move your cloud content to SharePoint

11 Jun 2021

One of the biggest conundrum of any organization planning to move to SharePoint is how to move all their documents stored in various different […]
[read article]

New version of AxioWorks SQList released (7.0.0.0)

04 Jun 2021

A new version of AxioWorks SQList is out today! We have been really busy for the past few months, but here we are with a brand new release of […]
[read article]

AxioWorks Newsletter June 2021: May has been a month of new releases

01 Jun 2021

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]

Form Configuration Editor: Lightweight and Built-in SharePoint List Form editor

16 May 2021

SharePoint Lists provides an easy and robust way of storing and manipulating data in SharePoint.  When you set up a new custom list, you also get OOTB […]
[read article]

AxioWorks Newsletter May 2021: April flew by faster than SpaceX

01 May 2021

Hello Reader, Welcome to another issue of our monthly Newsletter! Thank you for the great feedback on the past newsletters, our team has been working […]
[read article]

Bring Clarity to your SharePoint with MS Clarity

26 Apr 2021

Introduction There has been an exponential growth in SharePoint usage over the past few decades due to its vast set of capabilities to fulfill the […]
[read article]

AxioWorks Newsletter April 2021: A busy month!

01 Apr 2021

Welcome to our second Newsletter! Did you enjoy the first one? Or maybe you didn’t? Either way, do give us some feedback using the links at the […]
[read article]

MS Lists has finally landed in MS Teams

29 Mar 2021

During this Pandemic era, working remotely has significantly increased throughout the world and so is the usage of tools to facilitate collaboration […]
[read article]

AxioWorks Newsletter March 2021: Our first!

01 Mar 2021

We have just sent out our first newsletter and, being it our first one, we thought we dedicate a post to it. All future ones will go out just by […]
[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

*