Logo
banner

Blogs

Power BI > Embed Dashboard/Report in D365 Sales with RLS – 4

, June 5, 2020 114 Views

As there are various approaches to embed Power BI Dashboard/Report in D365 Sales, we might often use a different approach to achieve it only to realize that it doesn’t suit our cost or requirements. We have posted blogs describing the other 3 approaches but they have their own limitations regarding licenses or restriction of data for the users.

I recommend you read through the previous blog first, to get the context.

Let’s go through another approach to achieve it without many drawbacks and mostly used to embed the Power BI Report.

Approach 4. Embedding Power BI Report using REST API.

Prerequisites: Azure Power BI Embedded capacity subscription and 1 Power BI Pro License.

Here, we will create a Web API and an HTML Web Resource.

You can create the API by referring to this link.

Please note that the report embedded here will need to have RLS implemented.
Hence, the API created should also have the following amendment:

The below statement should be changed from

var generateTokenRequestParameters = new GenerateTokenRequest(accessLevel: "view");

to

var generateTokenRequestParameters = new GenerateTokenRequest("View", null, identities: new List < EffectiveIdentity > {
  new EffectiveIdentity(username: "username", roles: new List < string > {
    "roleA",
    "roleB"
  }, datasets: new List < string > {
    "datasetId"
  })
});

This Web API needs to be hosted in whichever way is suitable for you.

Here, we have the role which filters the userId column of a specific entity.
Please see below:

After hosting the API, an HTML Web Resource needs to be created, which will have the following code:

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <script src="https://code.jquery.com/jquery-3.2.1.min.js"
            integrity="sha256-hwg4gsxgFZhOsEEamdOYGBf13FyQuiTwlAQgxVSNgt4="
            crossorigin="anonymous"></script>
    <script src="/WebResources/new_PowerBI_Script"></script>
    <script src="./ClientGlobalContext.js.aspx" type="text/javascript"></script>

    <style>
        html {
            height: 100%;
            width: 100%;
            margin: 0;
            padding: 0;
            overflow: hidden;
        }

        .reportContainer {
            height: 100%;
            width: 100%;
            margin: 0;
            padding: 0;
        }

        body {
            font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
            height: 100%;
            width: 100%;
            margin: 0;
            padding: 0;
        }
    </style>
</head>
<body>
    <div id="report1Container" class="reportContainer"></div>
    <script>
        function RemoveCurlyBraceFromGuid(guid) {
            var returnGuid = guid;
            try {
                if (guid != null &&
                  guid.toString().length == 38) {
                    returnGuid = returnGuid.replace("{", "");
                    returnGuid = returnGuid.replace("}", "");
                }
            }
            catch (error)
            { }  //Supress error.
            return returnGuid;
        }

        function EmbedReport(jsonData, container) {

            $.ajax({
                url: '<Web-API-URL>',
                type: 'POST',
                dataType: 'json',
                data: jsonData,
                headers: {
                    "content-type": "application/json"
                },
                success: function (data) {
                    // Read embed application token from data
                    var accessToken = data.EmbedToken.token;

                    // Read embed URL from data
                    var embedUrl = data.EmbedUrl;

                    // Read report Id from data
                    var embedReportId = data.Id;

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

                    var newLayout = models.LayoutType.Custom;

                    if (window.innerWidth < 768) {
                        newLayout = models.LayoutType.MobilePortrait;
                    }
                    else {
                        newLayout = models.LayoutType.MobileLandscape;
                    }

                    // Embed configuration used to describe 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: false,
                            navContentPaneEnabled: true,
                            layoutType: newLayout

                        }

                    };

                    // Get a reference to the embedded report HTML element
                    var reportContainer = $(container)[0];

                    // Embed the report and display it within the div container.
                    var report = powerbi.embed(reportContainer, config);

                    // Displays the report in full screen mode.
                    // report.fullscreen();

                }
            });
        }

        var context = GetGlobalContext();
        var userId = context.userSettings.userId;;
        userId = RemoveCurlyBraceFromGuid(userId);

        var userURL = context.getClientUrl() + "/api/data/v9.0/systemusers(" + userId + ")?$select=domainname";

        $.ajax({
            url: userURL,
            type: 'GET',
            dataType: 'json',
            headers: {
                "OData-MaxVersion": "4.0",
                "OData-Version": "4.0",
                "Accept": "application/json",
                "Content-Type": "application/json; charset=utf-8",
                "Prefer": "odata.include-annotations=\"*\""
            },
            success: function (data) {
                var jsonData = JSON.stringify({
                    "GroupId": "<Workspace-Id>",
                    "ReportId": "<Report-Id>",
                        "Identity": {
                        "Username": data.domainname,
                        "Roles": ["BusinessDeveloper"],
                        "Datasets": ["<Dataset-Id>"]
                    }
                });

                jQuery.support.cors = true;
                var report1_container = '#report1Container';
                EmbedReport(jsonData, report1_container);
            }
        });

    </script>
</body>
</html>

Please note that here, we are passing the name of the role, username, and datasetId from Web Resource to Web API. So that way, we can filter the report as per the logged-in user.

After creating the HTML Web Resource, CRM dashboard is to be created using the Web Resource.
Select the newly created Web Resource. Do not forget to uncheck the following check-box because it may block the contents.

Note that, you need not add any users for RLS in Power BI Service.

Thus, the report will be filtered according to the logged-in user.

Pros:

  • Reduces the cost for Power BI Pro licenses for all the users.
  • Unlike the first two approaches, which supported embedding of only Dashboards, we can also embed Reports using this approach.

Cons:

  • Coding for Web API
  • An additional cost of Azure Power BI Embedded capacity.

To find the approach that suits your requirement, please visit the following blogs:
Embedding Power BI Report in Dynamics 365 CRM with RLS – 1
Embedding Power BI Report in Dynamics 365 CRM with RLS – 2
Embedding Power BI Report in Dynamics 365 CRM with RLS – 3

In case of any queries, comment down below and we shall help you through your queries.

Happy Embedding!

mm

Inkey

INKEY is your solution partner.
Our focus is to deliver you in-time intelligent innovative solutions ("key") for the problems in hand. Maintaining a quality standard right from the inception of a project is our top most priority.

Our team of talented professionals will execute your projects with dedication and excellence. We take ownership and accountability for the effort that goes into meeting our client’s needs.

Years of experience and proven success of delivering innovative custom solutions.

More posts by

Leave a Reply

Your email address will not be published. Required fields are marked *