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.

POWER BI: SHAREPOINT REAL TIME REPORTS AND DASHBOARDS

Microsoft Power BI provides a powerful way to visualise SharePoint data and build reports and dashboards on SharePoint lists and libraries. With the introduction of a Power BI web part (and the embed code) we can now also integrate these Power BI assets in our SharePoint sites.

In this blog post we will cover how to:

  • Create a Power BI report on  a SharePoint list
  • Configure the Power BI report to use direct query on the SharePoint data to keep the report up to date
  • Embed the Power BI report in SharePoint online

Power BI direct query with SharePoint lists

You may be wondering how we can use direct query with SharePoint data because according to this article from the Power BI team states that only the following data sources are currently supported:

Well we have a clever workaround to make your SharePoint data available as an additional direct query data source

CONFIGURING SHAREPOINT DIRECT QUERY FOR POWER BI

Pre-Requisites

To set up direct query for SharePoint lists and libraries and to embed the report in SharePoint Onlie you will need the following:

  1. AxioWorks SQList to synch your SharePoint lists to normalised SQL Azure tables. Download a trial here
  2. A SQL Azure database
  3. Power BI Pro
  4. A SharePoint Online tenant

Step 1: Synch SharePoint data to SQL Azure

First of all we’ll configure a replication using the SQList service to synch our SharePoint list to SQL Azure.

 

Open the SQList Manager and click new replication

image

 

Enter the details of the SharePoint Online site (could also be onprem), username and password and test the connection

image

Click Next
Next enter the details of the SQL Azure database and test the connection (ensure that your SQL Azure firewall rules allow access from the machine that SQList is running on)

image

Click next

Select “Export the lists and libraries from this site only” since we are only exporting one list

image

You will now see all the lists in the site we have selected. We are going to select the “Axioworks Sales” list which contains 8414 rows

image

 

Click Next and click finish

The SQList service will now start and will begin to synchronise the SharePoint list data with SQL Azure. You will see the SQList Service is running and data is being synchronised:

image

At this point you could close the SQList manager and leave the windows service to keep the data in synch.

If we open this lists in the SharePoint site we will see the following columns in this list:

image
And the data looks something like this:

image

 

Once the replication is complete we can open the database in SQL Server Management studio:

 

Step 2: Configure Power BI to use SharePoint data synched to SQL Azure

First of all, let’s open our Power BI desktop application and Get the data from our SQL Azure database

clip_image001

And enter our SQL Azure database details and click ok:

clip_image002

And now enter our credentials to access the database and click connect:

clip_image003

Open the navigator and select the table of interest.

clip_image004

In our case, SQList is keeping the AxioWorksSales:AxioWorksSales table in synch with our SharePoint list

clip_image005

Click “Load” and we now see the Power BI canvas to allow us to create our first visualistaion

clip_image006

But first I’m goign to create a column for Month that I’m going to use in my visualisation:

clip_image007

clip_image008

And I’ll do the same for year:

clip_image009

Now I’m goign to create a visualisation using Treemap and use a slicer based on year.

I’m also going to filter where sales > 30K

clip_image010

Export to Power Bi service

File -> Publish -> Publish to Power BI Service

Sign In to Power BI

clip_image011

And I now have a visualisation based on my SharePoint list

clip_image012

clip_image013

 

Step 3: Embed in SharePoint Online

 

There are a few requirements in order for Embed in SharePoint Online reports to work.

  • The Power BI (Preview) web part for SharePoint Online requires new SharePoint features to be enabled. Your tenant needs to be enrolled in the Set up the Standard or First Release options in Office 365 program to use this feature.
  • The First Release for everyone option must be selected.
  • The Power BI (Preview) web part for SharePoint Online requires Modern Pages.

Step 3: Embed your report in SharePoint Online

In order to embed your report into SharePoint Online, you will first need to get the URL for the report and then use that URL with the new Power BI web part within SharePoint Online.

Get a URL to your report
  1. View the report within the Power BI service.
  2. Select the File menu item.
  3. Select Embed in SharePoint Online (Preview).
  4. Copy URL from dialog.
    Note:

    You can also use the URL that is displayed in your web browser’s address bar when viewing a report. That URL will contain the report page you are currently viewing. You will need to remove the report section, from the URL, if you want to use a different page.

Add the Power BI report to a SharePoint Online page
  1. Open the desired page in SharePoint Online and select Edit.Or, create a new modern site page by selecting + New within SharePoint Online.
  2. Select + and select the Power BI (Preview) web part.
  3. Select Add report.
  4. Past the report URL into the property pane. This is the URL you copied from the steps above. The report will load automatically.
  5. Select Publish to make the change visible to your SharePoint Online users.

Granting access to reports

Embedding a report in SharePoint Online does not automatically give users permission to view the report. The permissions to view the report are set within the Power BI service.

Important:

Make sure to review who can see the report within the Power BI service and grant access to those not listed.

There are two ways to provide access to the report within the Power BI service. If you are using an Office 365 Group to build your SharePoint Online team site, you list the user as a member of the app workspace within the Power BI service. This will make sure that users can view the contents of that group. For more information, see Create and distribute an app in Power BI.

Alternatively, you can grant users access to your report by doing the following.

  1. Add a tile from the report to a dashboard.
  2. Share the dashboard with the users that need access to the report. For more information, see Share a dashboard with colleagues and others.

Web part settings

Below is a description of the settings that can be adjusted for the Power BI web part for SharePoint Online.

Property
Description

Page name
Sets the default page that is shown by the web part. Select a value from the drop down. If no pages are displayed, either your report has one page, or the URL you pasted contains a page name. Remove the report section from the URL to select a specific page.

Display
Option to adjust how the report is fit within the SharePoint Online page.

Show Navigation Pane
Shows or hides the page navigation pane.

Show Filter Pane
Shows or hides the filter pane.

Multi-factor authentication

If your Power BI environment requires you to sign-in using multi-factor authentication, you may be asked to sign-in with a security device to verify your identity. This will occur if you did not sign-in to SharePoint Online using multi-factor authentication but your Power BI environment requires an account validated by a security device.

Note:

Multi-factor authentication is not yet supported with Azure Active Directory 2.0. Users will receive a message saying error. If the user signs-in again to SharePoint Online using their security device, they may be able to view the report.

Reports that do not load

Your report may not load within the Power BI (Preview) web part and may show the following message.

This content isn’t available.

There are two common reasons for this message.

  1. You do not have access to the report.
  2. The report was deleted.

You should contact the owner of the SharePoint Online page to help you resolve the issue.

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

*