UA-44032151-3 page contents

Embedded Power BI: Interactive integration with Dynamics 365 for Finance and Operations


Introduction

In this blog post we’ll look at the integration between Dynamics 365 for Finance and Operations and its embedded Power BI reports, specifically regarding drill-through and callbacks from Power BI to AX.

Note

This post will not cover authoring, embedding and securing the reports.

This is not intended as full documentation of this feature, but rather a pointer to get developers on the right track. All content is subject to change as Dynamics 365 evolves.

Prerequisites

For the sake of saving time, it is recommended reading through the whole post first, before starting to do any practical experiments, as some design considerations for the Power BI report will be clarified down the road.

Target

Information embedded in this post should help to achieve the following result - clicking on a visual which represents the customer and opening the appropriate customer form. Note that the screenshot is a mock-up, normally the customer form opens in an own window:

 

Hooking in

After we’ve fulfilled the prerequisites by securing a Dynamics 365 instance with enabled embedded Power BI, creating a simple Power BI report, adding it to metadata and ensuring it is visible on a form, we might develop a keen urge to somehow interact with the newly embedded PBIX file .

The starting point for this interaction will be an event handler which subscribes to the delegate exposed on the PowerBIReportControl class – buildReportDrillThru().

This event handler will be triggered each time the users select any meaningful data on a report. All the necessary context information is encapsulated within the PBIReportSelectedData object passed as a parameter to the even handler.

Next is the tricky part… We need to make sense of the data provided by Power BI as context.

Where am I?

The first thing we need to do is ensure that our logic is only executed for our report. Unfortunately, so far there is no strongly typed way to do this, so we will have to use the report name for this purpose. PBIReportSelectedDataReport.report().displayName() will return the name of the current report. The following sample shows how to subscribe to the delegate and how to check which report the user is interacting with:

    [SubscribesTo(classStr(PowerBIReportControl), delegateStr(PowerBIReportControl, buildReportDrillThru))]
    public static void PowerBIReportControl_buildReportDrillThru(PBIReportSelectedData _data)
    {
        if (!_data)
        {
            return;
        }
        
        switch (_data.report().displayName())
        {
            case 'PfePowerSampleReport':                                
                PfeEmbeddedPowerBIHander handler = PfeEmbeddedPowerBIHander::construct();
                handler.handleClick(_data);
                break;
 
            default:
                break;            
        }        
    }

After we have successfully identified the report, it would be useful to check which page and visual the user is interacting with. Methods PBIReportSelectedData.page().name() and PBIReportSelectedData.visual().title() can be utilized for these purposes:

    void handleClick(PBIReportSelectedData _data)
    {
        switch(_data.visual().title())
        {
            case 'CustomerCreditLimitList':
                this.handleCustCreditLimitListClick(_data);
                break;

            default:
                break;              
        }
    }

Who are these people?

Now that we’ve figured out in which report the action happens and what the user is clicking on, we need to understand the data context of the click. There are several classes which help us with this task:

Class Description 
PBIReportSelectedData Contains all the context of the drill-through.
PBIReportSelectedDataPoint A class representing one selected data point. Each PBIReportSelectedData instance can contain multiple instances of PBIReportSelectedDataPoint; they are stored as a List and can be accessed via PBIReportSelectedData.dataPoints(). If the user has selected multiple elements, such as several slices of a pie chart, there will be several data points in the list.
PBIReportSelectedDataPointValue Data point values store the report measures selected by the user. For example, in a Power BI table with multiple columns one of which is a credit limit measure and the rest denote customer information – the credit limit will be returned as a value and the other fields as data point identities.

Consider that, depending on the visualization, the value might differ, for example, in a pie chart of currencies, it can return the sum of all the related transactions.

Data point values are stored in a list and are accessible via PBIReportSelectedDataPoint.values()

PBIReportSelectedDataPointIdentity Data point identity provides additional context on the value. In a case of a table with multiple columns described above, all the columns, which are not measures will be stored in the in the data point identity, such as, customer number and location.

Data point identities are stored in a list and are accessible via PBIReportSelectedDataPoint.identities()

PBIReportSelectedDataPointValueTarget The value target contains the name of the measure and the data source from the Power BI report.
PBIReportSelectedDataPointIdentityTarget The identity target contains the name of the data source and the field from the Power BI report.

 

To sum everything up, the user can select one or multiple data points, each of which can consist of multiple measures and fields. For the Dynamics-minded people such as me, a data point would represent a row and the combination of identities and values all the columns in that row. If it’s a pie chart, think of the row as slightly bent.

And finally, the targets show which report elements map to identities and values.

Points, Identities, Values, Targets and a Browser

The best way to understand what visuals return it is to browse their contents. A method like this can help with that by pushing the information to the infolog:

    public static void dataPointBrowser(PBIReportSelectedData _data)
    {
        ListEnumerator dataPointEnumerator = _data.dataPoints().getEnumerator();

        while (dataPointEnumerator.moveNext())
        {
            PBIReportSelectedDataPoint dataPoint = dataPointEnumerator.current();

            ListEnumerator identityEnumerator = dataPoint.identities().getEnumerator();           
            while (identityEnumerator.moveNext())
            {
                PBIReportSelectedDataPointIdentity pointIdentity = identityEnumerator.current();
                PBIReportSelectedDataPointIdentityTarget identityTarget = pointIdentity.target();
                info(strFmt("FIELD: %1.%2 Value %3", 
                            identityTarget.table(), 
                            identityTarget.column(), 
                            pointIdentity.identityEquals()));
            }

            ListEnumerator valueEnumerator = dataPoint.values().getEnumerator();
            while (valueEnumerator.moveNext())
            {
                PBIReportSelectedDataPointValue pointValue = valueEnumerator.current();
                PBIReportSelectedDataPointValueTarget pointTarget = pointValue.target();
                info(strFmt("MEASURE: %1.%2 Value %3", 
                            pointTarget.table(), 
                            pointTarget.column(), 
                            pointValue.formattedValue()));
            }
        }
    }

The above example loops thought all the values and identities of all the data points and uses the formattedValue() and identityEquals() methods to get the values out of the respective data points.

Mission (almost) accomplished

By combining the visual, data source and value information it is possible to understand what the user has clicked on and enables us to react accordingly, such as opening a form.

The following code snippets read out the Customer Id from the Power BI Report and call a menu item based on that:

    void readData(PBIReportSelectedData _data) 
    {        
        ListEnumerator dataPointEnumerator = _data.dataPoints().getEnumerator();
        while (dataPointEnumerator.moveNext())
        {
            PBIReportSelectedDataPoint dataPoint = dataPointEnumerator.current();

            ListEnumerator identityEnumerator = dataPoint.identities().getEnumerator();
            
            while (identityEnumerator.moveNext())
            {
                PBIReportSelectedDataPointIdentity pointIdentity = identityEnumerator.current();
                PBIReportSelectedDataPointIdentityTarget identityTarget = pointIdentity.target();
                switch (identityTarget.column())
                {
                    case 'CustAccount':
                        custAccount = pointIdentity.identityEquals();
                        break;

                    default:
                        break;
                }
            }
        }        
    }

    void openCustTable()
    {
        Args args = new Args();

        CustTable custTable = CustTable::find(custAccount);

        args.record(custTable);

        MenuFunction menuFunction = new MenuFunction(menuItemDisplayStr(CustTable), MenuItemType::Display);
        menuFunction.run(args);
    }

 

Design considerations / Tips

If the Power BI Report data source, field and measure names would match the names of respective metadata elements of the business database, one could use DictTable class to instantiate records. That can be achieved by appropriately renaming the elements in the Power BI Report, which brings us to the next point:

Give meaningful names to the Power BI report elements to be able to properly track them in Dynamics.

I suggest using constants (const) and/or macros to track the abovementioned elements names. The code samples in this post deliberately use text constants for purposes of readability.

Use resourceStr() function to reference the resource.   

Further reading / other examples

For more intricate examples on how to utilize this functionality see the standard CustCollectionsBIReportsHandler class.

The well known Fleet Management module also has a embedded Power BI sample - FMClerkWorkspaceform.

DISCLAIMER

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability or fitness for a particular purpose.

This post assumes that you are familiar with the programming language that is being demonstrated and the tools that are used to create and debug procedures.

Comments (0)

Skip to main content