Embedded Power BI reports with ASP.NET Core

Last year I had some projects where I had to embed Power BI reports to ASP.NET Core applications. There were easy cases that solved practically with copy-paste but I also had more complex situation where server-side code was needed because application uses custom authentication instead of Azure AD. This blog post covers both scenarios for embedding Power BI reports to ASP.NET Core applications.

Source code available! Source code and sample project for this blog posts are available in my GitHub repository gpeipman/AspNetCorePowerBI.

Integration with PowerBI is (always not so) easy to set up. We need reference to Microsoft.PowerBI.Api NuGet package and some coding to get reports integrated to our application. But first we need report. I once built simple report showing data about my craft beer ice distilling (check out my beer IoT journey if you are into complex beers). For embedding I’m using another one with just a chart from main report.

Now I want this report to be available in my ASP.NET Core web application.

There are two ways how to do it:

• Use embedded Power BI report in iframe – use this option if users are authenticated using Azure AD. It’s the fastest way to get work done.
• Use C# and JavaScript to embed Power BI report – use this option if users are authenticated using Azure AD or if you have service account for Power BI that all users must use. This option is also good if you need more server-side control over Power BI service.

Let’s start with iframe and then let’s see how C# and JavaScript approach works.

Need simple ad-hoc reporting for ASP.NET Core? Check out my blog post DataSet and DataTable based ad-hoc reporting with ASP.NET Core. I worked out this primitive solution few years ago and it still works well in production.

Using Power BI report in iframe

It’s the simplest option to go with. Just open your report in Power BI portal, move to File menu and select Embed like shown on following screenshot.

Power BI will show you a dialog with embed report URL and iframe definition. Copy iframe definition, paste it to your view and you are done.

NB! This solution expects user to have Azure AD account. If user is already authenticated then report is shown immediately. If user is not authenticated then authentication form is shown and user is sent back to ASP.NET Core application after authentication.

Using C# and JavaScript to embed Power BI report

This is harder approach but it supports also using custom service account to display report. We have more work to do and in this point be ready for all kind of complexities in settings between Power BI and Azure AD if you are not Power BI professional.

NB! This approach works only with reports in workspaces. I wasn’t able to get it work with reports in My workspace.

To make Power BI integration work we need to register our application at Azure AD and get some ID-s.

First we need to register our web application in Power BI. You can do it at address https://dev.powerbi.com/apps. Log in, fill registration form and click Register. Make sure you select same application type as shown on following screenshot.

Application is actually registered to Azure AD behind your Office 365 tenant. If application got registered successfully then Power BI shows the following dialog.

Copy these ID-s to your application settings now! We need these ID-s in web application to communicate with Power BI.

In Power BI open the report you want to show to users and grab report ID from browser’s address bar.

Copy report ID to your application settings! We have to tell Power BI components later what report we want to display. Same way you must take workspace ID where report belongs.

Configuring ASP.NET Core application

Application must know few settings to show report from Power BI. Add the following JSON to your appsettings.json file and fill in appropriate values.

"PowerBI": {
  "ApplicationId": "bfca1230-3d9a-4a31-de94-d0abfff13e8d",
  "ApplicationSecret": "tYA0i/dXsauHlkX1LXHQ194/EaH6crrrSoF1q55basc=",
  "ReportId": "5eae2c14-fd87-471e-191a-7fba3e8f918e",
  "WorkspaceId": "041148db-09c1-4e2d-b550-3f49164e868a",
  "AuthorityUrl": "https://login.windows.net/common/oauth2/authorize/",
  "ResourceUrl": "https://analysis.windows.net/powerbi/api",
  "ApiUrl": "https://api.powerbi.com/",
  "EmbedUrlBase": "https://app.powerbi.com/",
  "UserName": "gunnar@example.com",
  "Password": "admin123LikeAlways"
}

To have these settings as object we need class for Power BI settings block.

public class PowerBISettings
{
    public Guid ApplicationId { get; set; }
    public string ApplicationSecret { get; set; }
    public Guid ReportId { get; set; } 
    public Guid? WorkspaceId { get; set; }
    public string AuthorityUrl { get; set; }
    public string ResourceUrl { get; set; }
    public string ApiUrl { get; set; }
    public string EmbedUrlBase { get; set; }
    public string UserName { get; set; }
    public string Password { get; set; }
}

To make settings available in ASP.NET Core application we load settings when application starts and register them as singleton in framework-level dependency injection. This is done in ConfigureServices() method of Startup class.

var powerBISettings = Configuration.GetSection("PowerBI").Get<PowerBISettings>();
services.AddSingleton(powerBISettings);

We need also some NuGet packages:

• Microsoft.IdentityModel.Clients.ActiveDirectory
• Microsoft.PowerBI.Api

And Power BI JavaScript library:

• https://github.com/microsoft/PowerBI-JavaScript

From Power BI JavaScript library take minified version from dist folder and copy it to scripts folder of your web application.

Now we have basic work done and it’s time to get to real business.

Getting access token

.NET Core libraries for Azure AD doesn’t support username and password authentication like it was before. Instead we have to write our own hack to get access token for username and password authentication.

Add the following method to controller that hosts action for report.

private async Task<string> GetPowerBIAccessToken(PowerBISettings powerBISettings)
{
    using(var client = new HttpClient())
    {
        var form = new Dictionary<string, string>();
        form["grant_type"] = "password";
        form["resource"] = powerBISettings.ResourceUrl;
        form["username"] = powerBISettings.UserName;
        form["password"] = powerBISettings.Password;
        form["client_id"] = powerBISettings.ApplicationId.ToString();
        form["client_secret"] = powerBISettings.ApplicationSecret;
        form["scope"] = "openid";

        client.DefaultRequestHeaders.TryAddWithoutValidation("Content-Type", "application/x-www-form-urlencoded");

        using (var formContent = new FormUrlEncodedContent(form))
        using (var response = await client.PostAsync(powerBISettings.AuthorityUrl, formContent))
        {
            var body = await response.Content.ReadAsStringAsync();
            var jsonBody = JObject.Parse(body); 

            var errorToken = jsonBody.SelectToken("error");
            if(errorToken != null)
            {
                throw new Exception(errorToken.Value<string>());
            }

            return jsonBody.SelectToken("access_token").Value<string>();
        }
    }
}

Now it’s time to display report.

Displaying report

Here’s the code for controller action to display Power BI report. Notice how I use controller action injection to get Power BI settings to method.

public async Task<IActionResult> Report([FromServices]PowerBISettings powerBISettings)
{
    var result = new PowerBIEmbedConfig { Username = powerBISettings.UserName };
    var accessToken = await GetPowerBIAccessToken(powerBISettings);
    var tokenCredentials = new TokenCredentials(accessToken, "Bearer");

    using (var client = new PowerBIClient(new Uri(powerBISettings.ApiUrl), tokenCredentials))
    {
        var workspaceId = powerBISettings.WorkspaceId.ToString();
        var reportId = powerBISettings.ReportId.ToString();
        var report = await client.Reports.GetReportInGroupAsync(workspaceId, reportId);
        var generateTokenRequestParameters = new GenerateTokenRequest(accessLevel: "view");
        var tokenResponse = await client.Reports.GenerateTokenAsync(workspaceId, reportId, generateTokenRequestParameters);

        result.EmbedToken = tokenResponse;
        result.EmbedUrl = report.EmbedUrl;
        result.Id = report.Id;
    }

    return View(result);
}

Here is my view to show the report.

@model PowerBIEmbedConfig
<style>
    #reportContainer {
        height: 600px;
        width: 100%;
        max-width: 2000px;
    }
</style>
<script src="https://npmcdn.com/es6-promise@3.2.1"></script>
<script src="~/js/powerbi.min.js"></script>

<div id="reportContainer"></div>

@section scripts {
    <script>
    // Read embed application token from Model
    var accessToken = "@Model.EmbedToken.Token";

    // Read embed URL from Model
    var embedUrl = "@Html.Raw(Model.EmbedUrl)";

    // Read report Id from Model
    var embedReportId = "@Model.Id";

    // Get models. models contains enums that can be used.
    var models = window['powerbi-client'].models;

    // Embed configuration used to describe the what and how to embed.
    // This object is used when calling powerbi.embed.
    // This also includes settings and options such as filters.
    // You can find more information at https://github.com/Microsoft/PowerBI-JavaScript/wiki/Embed-Configuration-Details.
    var config = {
        type: 'report',
        tokenType: models.TokenType.Embed,
        accessToken: accessToken,
        embedUrl: embedUrl,
        id: embedReportId,
        permissions: models.Permissions.All,
        settings: {
            filterPaneEnabled: true,
            navContentPaneEnabled: true
        }
    };

    $(document).ready(function () {
        // Get a reference to the embedded report HTML element
        var reportContainer = $('#reportContainer')[0];

        // Embed the report and display it within the div container.
        powerbi.embed(reportContainer, config);
    });
    </script>
}

And here is my Power BI embedded report when I run my ASP.NET Core application.

Wrapping up

There are multiple options to embed Power BI reports using ASP.NET Core. IFrame solution was simple and straightforward – just copy markup from Power BI portal, paste it to ASP.NET Core view and you are done. With C# and JavaScript our solution was more complex and we had to write more code. Still we were able to make report available for all authenticated users in application that doesn’t use Azure AD.

The post Embedded Power BI reports with ASP.NET Core appeared first on Gunnar Peipman - Programming Blog.