Power BI reports in Microsoft Dynamics 365 for Finance and Operations will display no data, blank, or empty if default main account categories are not used

If you enabled Power BI integration in your Microsoft Dynamics 365 for Finance and Operations system as instructed in the article below, you may notice that parts of the reports do not display data while some do, even though there are transactions in the system and the Power BI reports were not modified by you.

Configure Power BI integration for workspaces

For example, Quick Ratio Analysis page in the Financial performance report:

The reason may be simply that the default, out-of-the-box main account categories are not used in the system, as new custom account categories were set up instead.

If this is the case, you may consider either moving to the default account categories or customizing the code behind the data entities to respect the new account categories created.

Microsoft published Power BI reports utilize the default, out-of-the-box default account categories. You are pointed to it in articles related to main accounts setup:

Create a main account
Set up main account categories

A set (not complete) of default account categories from a demo system:

For information on what data is consumed by Power BI reports refer to the following article and review each report from there:

Power BI content

For example, Financial performance Power BI content:

Financial performance Power BI content

“Understanding the data model and entities


The following entities were used as the basis of the Financial performance Power BI content:

Aggregate data entities

  • GeneralLedgerActivities – This entity aggregates general ledger balances by account category.
  • BudgetActivities – This entity aggregates budget balances by account category.

Data entities

  • FiscalCalendars
  • MainAccounts
  • LegalEntities
  • Ledgers
  • ChartofAccounts”


Let us take a look at the Quick Ratio card on the Quick Ratio Analysis page in the Financial performance.pbix report opened in Power BI Desktop.

Note: The report can be downloaded from LCS Shared asset library, the Power BI report model group.

The source for the data in the card is the query:

Query: General Ledger Activities

The expressions behind the fields:



The source for the data in Microsoft Dynamics 365 for Finance and Operations is the view:

View: LedgerActivityView

The values are calculated in the methods:

QuickRatioAssets: computeColumnQuickRatioAssets

CurrentLiabilities: computeColumnCurrentLiabilities

They invoke methods in the class:

Class: LedgerBIHelpers

    /// <summary>
    /// Gets the expression used for the quick ratio assets measure.
    /// </summary>
    /// <param name=”_viewName”>
    /// The name of the view that contains the field to use forcomparison.
    /// </param>
    /// <param name=”_dataSourceName”>
    /// The name of the data source on the view that contains the field to use for comparison.
    /// </param>
    /// <param name=”_amountFieldName”>
    /// The name of the amount field on the data source to use forcomparison.
    /// </param>
    /// <returns>
    /// A string expression formatted as a conditional sql expression.
    /// </returns>
    /// <remarks>
    /// Calculation: Cash + Cash Equivalents + Short Term Investments + Accounts Receivable + Notes Receivables
    /// </remarks>
    public static str computeColumnQuickRatioAssets(
        TableName _viewName,
        TableName _dataSourceName,
        FieldName _amountFieldName)
    {
        List categoryList = new List(Types::String);
        categoryList.addEnd(LedgerBIHelpers::addMainAccountCategoryRestriction(MainAccountCategorySystemValues::Cash, _viewName, _dataSourceName));
        categoryList.addEnd(LedgerBIHelpers::addMainAccountCategoryRestriction(MainAccountCategorySystemValues::CashEquivalents, _viewName, _dataSourceName));
        categoryList.addEnd(LedgerBIHelpers::addMainAccountCategoryRestriction(MainAccountCategorySystemValues::ShortTermInvestments, _viewName, _dataSourceName));
        categoryList.addEnd(LedgerBIHelpers::addMainAccountCategoryRestriction(MainAccountCategorySystemValues::AccountsReceivable, _viewName, _dataSourceName));
        categoryList.addEnd(LedgerBIHelpers::addMainAccountCategoryRestriction(MainAccountCategorySystemValues::NotesReceivable, _viewName, _dataSourceName));
 
        return LedgerBIHelpers::computeColumnFromMainAccountCategoryList(
            categoryList,
            _viewName,
            _dataSourceName,
            _amountFieldName);
    }

 
    /// <summary>
    /// Gets the expression used for the current liabilities measure.
    /// </summary>
    /// <param name=”_viewName”>
    /// The name of the view that contains the field to use for comparison.
    /// </param>
    /// <param name=”_dataSourceName”>
    /// The name of the data source on the view that contains the field to use for comparison.
    /// </param>
    /// <param name=”_amountFieldName”>
    /// The name of the amount field on the data source to use for comparison.
    /// </param>
    /// <returns>
    /// A string expression formatted as a conditional sql expression.
    /// </returns>
    /// <remarks>
    /// Calculation: Calculation: Accounts Payable + Notes Payable + Current Maturities on Long Term Debt + Taxes Payable + Interest Payable
    /// + Dividends Payable + Leases Payable (Current) + Sinking Fund Payable (Current) + Other Current Liabilities
    /// + Long term liabilities
    /// </remarks>
    public static str computeColumnCurrentLiabilities(
        TableName _viewName,
        TableName _dataSourceName,
        FieldName _amountFieldName)
    {
        List categoryList = new List(Types::String);
        categoryList.addEnd(LedgerBIHelpers::addMainAccountCategoryRestriction(MainAccountCategorySystemValues::AccountsPayable, _viewName, _dataSourceName));
        categoryList.addEnd(LedgerBIHelpers::addMainAccountCategoryRestriction(MainAccountCategorySystemValues::NotesPayable, _viewName, _dataSourceName));
        categoryList.addEnd(LedgerBIHelpers::addMainAccountCategoryRestriction(MainAccountCategorySystemValues::CurrentMaturitiesOnLongTermDebt, _viewName, _dataSourceName));
        categoryList.addEnd(LedgerBIHelpers::addMainAccountCategoryRestriction(MainAccountCategorySystemValues::TaxesPayable, _viewName, _dataSourceName));
        categoryList.addEnd(LedgerBIHelpers::addMainAccountCategoryRestriction(MainAccountCategorySystemValues::InterestPayable, _viewName, _dataSourceName));
        categoryList.addEnd(LedgerBIHelpers::addMainAccountCategoryRestriction(MainAccountCategorySystemValues::DividendsPayable, _viewName, _dataSourceName));
        categoryList.addEnd(LedgerBIHelpers::addMainAccountCategoryRestriction(MainAccountCategorySystemValues::LeasesPayable, _viewName, _dataSourceName));
        categoryList.addEnd(LedgerBIHelpers::addMainAccountCategoryRestriction(MainAccountCategorySystemValues::SinkingFundPayable, _viewName, _dataSourceName));
        categoryList.addEnd(LedgerBIHelpers::addMainAccountCategoryRestriction(MainAccountCategorySystemValues::OtherCurrentLiabilities, _viewName, _dataSourceName));
        categoryList.addEnd(LedgerBIHelpers::addMainAccountCategoryRestriction(MainAccountCategorySystemValues::LongTermDebt, _viewName, _dataSourceName));
 
        return LedgerBIHelpers::computeColumnFromMainAccountCategoryList(
            categoryList,
            _viewName,
            _dataSourceName,
            _amountFieldName);
    }

Those are the methods where it is decided which main account categories are pulled into the calculations.

If moving to the default account categories is not an option, you may consider customizing the standard code in the corresponding areas to respect custom account categories as you would customize any other artifact of Microsoft Dynamics 365 for Finance and Operations system. It is not required to modify the Power BI reports themselves for this, as long as only the content that is supposed to be displayed by the standard reports is to be displayed. If the requirement is to display additional information, you may then principally need to customize the PBIX report (or create a new one), the metadata and the code.

For more information on the concepts and modeling options review the “Analytical tools and visualization” articles collection under the “Intelligence” group.

Intelligence

Other reports may be empty because not all data expected by the report were supplied in the application.

For example, Project overview page in the Practice Manager report:

This page will display Blank and zeros if projects created in the system were not associated with a customer account, i.e. the field Account in the project detail form is empty (and by this the column Customer name in the grid).

Let us take a look at the Created projects card on the Project overview page in the Practice Manager July 2017.pbix report opened in Power BI Desktop.

The card is supposed to show the number of Projects in the stage Created, for the selected company (Legal entity).

The source for the data in the card is the query:

Query: ProjectAccountingCube_Projects

The source for the page level filter Legal entity is the query:

Query: ProjectAccountingCube_Company

If we take a look at the relations, we can see that the link between projects and the company can be built only through the customer.

If the link between the project and the customer is missing, the project will not be linked to the selected Legal entity and will not be counted. To have the report display data each project should be associated with a customer.

You can export the Azure SQL data warehouse database (AxDW) from the UAT sandbox system and import it into the SQL Server on your development system for testing a Power BI report locally using the Power BI Desktop. In SQL Server you have the option to use SQL Profiler tool to capture the trace on queries executed when refreshing the report. Analyzing the queries you are in the position to figure out which data is missing in your system.

It is the Entity store that is transferred to the data warehouse database (AxDW) that Power BI reports access through a DirectQuery. This database is filled with data on demand only, either one-time or in a scheduled batch job, as you can read in the following article:

Overview of Power BI integration with Entity store

Another reason for Power BI reports displaying no data may be that the data warehouse database has not yet been populated with related data.

As described in the above article, the Aggregate measurements the Power BI reports build upon can be selected for Refresh and updated in the form:

System administration > Setup > Entity Store

Make sure that all items in the list were selected and that the batch job ended successfully if no reports show data in the environment.

Note: These troubleshooting suggestions apply as well to Power BI Embedded reports which were introduced in July 2017 Update (7.2). These reports are found behind the Analytics tab in workspaces, for example. They require the data warehouse database (AxDW) to be Azure SQL database in the cloud so Power BI Embedded service can access it. Hence, they cannot be run in Microsoft Dynamics 365 for Finance and Operations on one-box development or demo system because the local SQL server database on those machine cannot be accessed by the service in the cloud. The workaround is to move the local AxDW database from the local SQL Server to Azure SQL as described in the following blog post:

Accessing Analytical Workspaces and Reports on 1Box environment

They can still be tested locally using Power BI Desktop. Just be aware that he actual version of a Power BI report used with Power BI Embedded service comes as resource in July 2017 update and this version should be rather used for testing. You can find the location of each Power BI report that is used by the system out-of-the-box with Power BI Embedded service if you search for .pbix extension in the system’s AosService\PackagesLocalDirectory folder. For example, the PracticeManager.pbix report used behind the Analytics tab in the Project management workspace can be found in the ApplicationSuite\Foundation\AxResource\ResourceContent\PowerBIReport subfolder. If you create a copy of it in a folder of your choice you can use it for testing with Power BI Desktop.

Conclusion

Standard Power BI reports use default out-of-the-box settings in order to perform calculations and pull out the relevant data from the system. If default settings are not used or other expected data are missing in the system, it may cause the standard reports not to display data. If reports were not modified by developers, then usually the reason for the report to display empty lies in the customer data. Only if it is confirmed that the issue is not data related we may investigate if it is related to the design of the report.