How to choose the right BI and reporting strategy for Dynamics 365 Finance

Dynamics 365 for Finance and Supply Chain Management offers a broad range of reporting and business intelligence options. You can utilize the integrated Power BI dashboards, link the Power BI report gallery within Dynamics, use integrated SSRS reports or develop Power BI reports and dashboards that connect to Dynamics 365. Sometimes it can be hard to decided which one to choose. Here is a guideline which one to choose depending on the reporting requirements.

Dynamics 365 Power BI and Reporting strategy

Decision Tree for Power BI / Reporting in Dynamics 365 Finance & SCM

Report Format:

What is the purpose of the report? Is it an interactive report / dashboard or is it a static list or document like artifact? For example, sales analysis is typical an interactive report while a collection letter is a printed document. Power BI is great for interactive data analysis, SQL Server Reporting Services (SSRS) is the right tool for lists and page oriented printable documents.

Real Time:

Do you need to see transactional data as soon as it is generated in Dynamics? For example posting and invoice and immediately printing the document. If so, you need to access the transactional database (AxDB). There are two ways: Use integrated Reporting Services or query entities via OData. However, using entities allows you to access the AxDB but Power BI doesn’t support Direct Query mode for OData, i.e. you have to hit refresh in order to get the latest data.

Multiple data sources:

Is Dynamics 365 Finance the only data source for your report, or do you need to integrated external data sources as well? An example could be to develop a revenue analysis which includes actual sales data from Dynamics 365 as well as demographics and household income. Integrated Power BI dashboards in Dynamics 365 use direct query to access the AxDB and cannot integrate other data sources. It is also not recommended to load external data into Dynamics 365 AxDB because you have a limited cost free database size in your subscription. Additional SQL storage has to be paid.

Additional licenses:

Dynamics 365 Finance and Supply Chain Management includes the rights to view the integrated Power BI dashboards. No additional Power BI license is required. Reports developed using the integrated SQL Server Reporting Services technology are also covered by the Dynamics license. External Power BI reports, dashboards and paginated reports require additional Power BI licenses. At least Power BI Pro for reports and dashboards, Power BI Premium Capacity or Premium per User for paginated Reports.


An interactive custom Power BI Report viewed in Dynamics 365 Finance via the users Report Catalog option

Production Performance is part of Dynamics 365 Finance & SCM and directly connects to the entity store (aka. AxDW)

A paginated report in Power BI (Premium)

A static SSRS paginated report in Dynamics 365 Finance and SCM


Before you start working with a certain product, make sure to understand the requirements. Identify the data sources and how to access them. Then choose the right tool for the job. Don’t try to make a printable Power BI or fancy SSRS. By leveraging the full reporting and BI potential you can deliver a great user experience that adds value to Dynamics 365 Finance and SCM.

Add carry forward sum to OpenSalesOrders SSRS report

A common requirement is to add a running totals sum and carry foward which displays the actual sum on the next page. Following the tutorials like this by André it’s easy to implement also for Dynamics AX.

  1. Start Visual Studio as Admin and open CustSalesOpenOrders_NA report from the application explorer
  2. Open the report design
  3. Add 2 new columns on the right side and set Hidden property to TRUE
  4. Open the property dialog for the first new column and change the name to RunningTotal
  5. Set the following expression as value
  6. Open the property dialog for the second new column and change the name to RunningTotalPrev
  7. Set the following expression as value
    =RunningValue(Fields!amountRemainingMST.Value,SUM,"CustSalesOpenOrders_NADS") – Fields!amountRemainingMST.Value
  8. In the report design create a new text field in the report header above the amountRemainingMST
  9. Set the following expression as value
    ="Running Total: " +cstr(First(ReportItems!RunningTotalPrev.Value))
  10. In the report design add a new footer and add a text field in the footer under the amountRemainingMST
  11. Set the following expression as value
    ="Running Total: " +cstr(Last(ReportItems!RunningTotal.Value))
  12. Save and deploy the report
  13. Open Dynamics AX go to Sales and Marketing > Reports > Sales Orders and start the Open Sales Orders report

Your report should look like this, including a page sum and a carry forward on the next page


BI Service Error: SSASFrameworkService not generated, Instance of ‘SysSecRole’ could not be created

I recently faced another problem with SSRS reports. The BI Service (Administration > Setup > AIF > Incoming Ports) was down and could not be activated. Trying to re-activate the service resulted in an error telling me that the SSASFrameworkService could not be generated because an instance of service class “SysSecRole” could not be generated. AOS restart and Full IL Compilation didn’t solve the problem.

However, the solution was to re-deploy the AIF Service Group: AOT > Service Groups > BI Service > Context Menu > Deploy Service Group. Afterwards the BI Service could be activated and reports were working again.

Send SSRS Report as Email

The SSRS Report Viewer in AX 2012 does not have a built in Email function. However, there are some implementations available to add an email button in SharePoint or ASP.NET pages, e.g. Codeproject . The idea is simple, save the report as PDF, open outlook email and add the PDF file as attachment. Follow these steps to implement an Email button for Dynamics AX.

In the AOT add a reference to the Microsoft.Reporting.WinForms DLL. It contains the SRS Report Viewer class. The DLL can be found here: C:\Program Files (x86)\Microsoft Visual Studio 10.0\ReportViewer. Make sure you are using the corresponding assembly version as your SQL Server.

Open the SrsReportViewer Form in the AOT, and add a button to the ButtonGroup. Call it “SendReport”.


Add the following Code to the clicked method:

void clicked()
    Microsoft.Dynamics.AX.Frameworks.Controls.ReportViewer.AxReportViewer axviewer;
    Microsoft.Reporting.WinForms.ReportViewer nviewer;
    Microsoft.Reporting.WinForms.LocalReport report;
    System.Exception ex;
    System.Array bytear;
    System.Object no;
    System.IO.File file;
    str tmpPath;
    System.IO.FileStream fs;
    SmmOutlookEmail smmOutlookEmail = new SmmOutlookEmail();

    axviewer = AxReportViewer.control();
    nviewer = axviewer.get_ReportViewerControl();
        //render as PDF
        report = nviewer.get_ServerReport();
        bytear = report.Render("PDF");
        no = bytear;

        //path to temp. files
        tmpPath = System.IO.Path::GetTempPath();
        tmpPath = tmpPath + "report.pdf";

        //save to file
        fs = System.IO.File::Create(tmpPath);

        //open outlook email
        if (smmOutlookEmail.createMailItem())

        ex = CLRInterop::getLastException();

Save, Compile and open a report. Enlarge the “Options” and you’ll see the new Email button.


By clicking the Email button, the report is rendered as PDF, stored locally in your temp. files and attached to a new outlook email.


This is a very simple implementation. You may add more customizations, e.g. take the report parameters into account to initialize the TO and CC Email fields.

SSRS Error: Dynamic packer query not found

Once again I came across an SSRS error, telling me the dynamic packed query was not found.
\Classes\SRSQueryBuilder\initalize (21)

The environment has 3 AOS, all of them running on one windows server (2712/8101, 2713/8102, 2714/8103) and 3 SSRS instances all running on another windows server. Since default AX voodoo like restart and incremental IL did not resolve the problem, I check and corrected issues following this checklist

In Dynamics AX

  • All AX instances have the BC Proxy account configured as system service account under
    System Administration > Setup > System > System service account
  • Every AX instance has its own SSRS report server configured
    System Administration > Setup > Business Intelligence > Reporting Services > Report Server
  • There is only one default configuration
  • The name (of the windows server) is correct e.g. VM-SRV-SQL
  • The name of the SSRS instance (!) is correct and configured e.g. MSSQLSERER, SSRSLIVE, SSRSTEST,etc.
  • Check the URL (they are in reverse order in the SSRS configuration manager) *argh*
      The report manager url is correct. That’s where you can browse the folder and set permissions
      The report service url is corret. That’s the web service
  • The configuration is assigned to the correct AOS
    Check the online users form if you are not sure which AOS you are actually using

On the SSRS server

  • All SSRS instances are running and all SSRS instances run as BC Proxy account
  • The default configuration (that cannot be changed in the AX configuration utility) is valid
  • The SSRS instances have a valid Microsoft.Dynamics.AX.ReportConfiguration.axc.configuration file in the Report Server\bin directory. If you are not sure, create a new configuration using the configuration utility, test the configuration by starting AX, save the configuration as file and replace the old one in the Report Server\Bin directory
  • Make sure the Business Connector configuration has a default company name e.g. DAT
  • Restart the SSRS instances

SSRS: Error when opening design of duplicated report

When you duplicate an SSRS Dynamics AX 2012 report in Visual Studio, and you try to open the design node you may get an error message like “object instance not set to an object”. The reason is that the data set parameters contain the name of the report e.g. SalesQuotation. Now that you have duplicated the report its name is CopyOfSalesQuotation and the report is broken.

Broken parameter in duplicated SSRS report

Compiling the report project brings up a list of errors.

Errors when compiling a duplicated report

Navigate to each data set parameter and change the name of the report to match the new name e.g. CopyOfSalesQuotation instead fo SalesQuotation

Broken parameter in duplicated report

The fixed parameter should look like this

Dynamics AX 2012 duplicated report data set parameter

Finally the report designer is working

Dynamics AX 2012 duplicated report in report designer