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

My Impressions of Microsoft Flow: A Developer’s Perspective

13 May 2024

Recently, I had the opportunity to explore Microsoft Flow, a tool that promises to facilitate the rapid development of software applications through […]
[read article]

Power Pages Unleashed: Creating Dynamic Websites with Microsoft Copilot

28 Apr 2024

In our latest tutorial, we delve into the capabilities of Microsoft Copilot, with a focused exploration of Power Pages. Our aim? To showcase how […]
[read article]

Harnessing the Power of Microsoft CoPilot in Power Automate Flows: A Comprehensive Tutorial

25 Mar 2024

In the ever-evolving landscape of technology and business automation, Microsoft has introduced a game-changer that is set to revolutionize how we […]
[read article]

Embracing AI: Transforming Jobs and Unlocking New Opportunities (Claude 3 Opus version)

18 Mar 2024

The rapid advancements in Artificial Intelligence (AI) have sparked both excitement and fear among workers worldwide. Many people are concerned that […]
[read article]

The Evolution of Work in the Age of AI: A New Era of Collaboration (ChatGPT 4 version)

18 Mar 2024

In the annals of human progress, pivotal inventions such as the steam engine, the tractor, and the internet have revolutionarily altered our way of […]
[read article]

AxioWorks Newsletter February 2024: Partnerships, Upcoming SQList Update, and Summit Invitation

27 Feb 2024

Hello Reader, This February has been pivotal for AxioWorks as we’ve focused on laying strong foundations for the year. Here are the key […]
[read article]

How AxioWorks SQList Aligns With the Priorities of Software Buying Decision Makers

26 Feb 2024

As outlined in the “2024 Global Software Buying Trends” report by Gartner Digital Markets, the criteria that predominantly influence […]
[read article]

Maximising Power BI Reporting from SharePoint: The Strategic Advantage of AxioWorks SQList over Native Connectors

15 Feb 2024

In today’s data-driven environment, making informed decisions rapidly can offer a significant competitive edge. This is where Power BI comes […]
[read article]

AxioWorks Newsletter January 2024: A Promising Start to the New Year!

29 Jan 2024

Hello Reader, We hope this newsletter finds you well and thriving. At AxioWorks, we are embracing the new year with great enthusiasm and are […]
[read article]

Exciting New Partnership and Innovative SharePoint Tools from AxioWorks and Lightning Tools

17 Jan 2024

AxioWorks is excited to announce a strategic partnership with Lightning Tools, a leader in creating innovative SharePoint components. This […]
[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