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

AxioWorks Newsletter September 2023: An exhilarating month.

30 Sep 2023

Hello Reader, September has been a bustling and exhilarating month for us at AxioWorks, filled with advancements, collaborations, and a whole lot of […]
[read article]

The Collaborative Pathway: Bridging Minds in the AI Ecosystem

19 Sep 2023

In recent days, I found myself immersed in an enlightening conversation with a friend, a fellow entrepreneur steering his ship in the vast sea of […]
[read article]

AxioWorks Newsletter August 2023: A month of milestones and innovation.

28 Aug 2023

Hello Reader, September seems to be a month of unexpected milestones and continued innovations at AxioWorks. As the summer wanes and the autumn […]
[read article]

Use Graph API in Power Automate to read large Excel files from SharePoint

28 Aug 2023

Power Automate, a part of Microsoft’s suite of tools, is increasingly becoming a preferred choice for automating workflows and business […]
[read article]

From Punch Cards to Python: Still Hitting the Keys in my 50s

16 Aug 2023

I spent the entire weekend writing code, not because of deadlines to meet or million-pound dreams, but for the kick that I still get from doing it. […]
[read article]

AxioWorks Newsletter July 2023: Unveiling AxioWorks’s latest successes and exciting innovations

27 Jul 2023

Hello Reader, We hope this monthly update finds you in good spirits. As always, we are eager to share the latest developments and exciting ventures […]
[read article]

AI and Data: The Symbiosis of Interaction and Accuracy

13 Jul 2023

Artificial Intelligence (AI) has rapidly emerged as a revolutionary technology that fundamentally alters the nature of human-machine interactions. […]
[read article]

AxioWorks Newsletter June 2023: SQList v8 Release, Webinars, and New Product Development

30 Jun 2023

Hello Reader, Greetings to all our valued customers and subscribers! We are thrilled to share some exciting updates and developments with you in this […]
[read article]

Safeguarding Secrets with Secure Strings and Environment Variables in the Power Platform

26 Jun 2023

In today’s interconnected digital landscape, safeguarding sensitive information and maintaining robust security measures is of paramount […]
[read article]

Case study: Streamlining Medical Trial Data Analysis in the Pharmaceutical industry

15 Jun 2023

In the ever-evolving landscape of the pharmaceutical industry, conducting medical trials and analysing the resulting data are critical for the […]
[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