It’s a well-known fact that accountants love Excel to analyse their data. Being able to integrate Excel to accounting source system is high up their requirements list. So let’s have a look at the Excel reporting options for Dynamics 365 for Finance and Operations.
Today we will look at three reporting options, Standard Dynamics Excel integration, Odata feed Excel integration and Analyse in Excel using Power BI.
Standard Dynamics Excel integration
The Dynamics for Finance and Operations offering comes with an out of the box Excel integration. The Excel Data connector app interacts with your Excel workbooks with Dynamics for Finance and Operations OData Services (for publicly exposed data entities) and this made it possible for Excel to become a seamless part of the user experience in Dynamics for Operations. Most people now this function as “Open in Excel”. The exposed data entities can either be normal entities or aggregated entities. The aggregated entities are based on views of data and can consolidate, summarize and simplify the complex table structure. The standard data entity offering is not suitable for large amounts of data and therefore reporting. The standard data entities can however publish data back into Dynamics for Finance and Operations.
To load the aggregated data entity in Excel sign in to the data connector from Excel and press select Design. The Excel add-in retrieves entity metadata. Select Add table. A list of entities appears. The entities are listed in "Name - Label" format. Select an entity in the list, such as GeneralLedgerActivity, and then select Next.
To add a field from the Available fields list to the Selected fields list, select the field, and then select Add. Alternatively, double-click the field in the Available fields list.
After you've finished adding fields to the Selected fields list, make sure that the cursor is in the correct place in the worksheet (for example, cell A1), and then select Done. Then select Done to exit the designer.
Select Refresh to pull in a set of data.
The GeneralLedgerActivity entity only exposes RecIds, the entity can be customized to meet company requirements. There are number of blogs which describe how custom aggregated entities can be created and deployed. This is a task only a developer can complete.
Odata feed integration via PowerQuery
From Excel you can also connect to Dynamics using the OData Feed data connection.
Use the D365 URL in combination with "/data" to connect.
Simply sign-in with your Organisation account and press Connect.
After the authentication progress the full list of data entities will load, this may take a while. Select the data entities you which to load and press Load. If you’re interested in analysing ledger transactions load the following data entities:
- GeneralLedgerActivities
- FiscalCalendar
- FinancialDimensionValues
- DimensionCombination
- MainAccount
Excel uses the PowerQuery query and Odata data services to connect to Dynamics for finance and Operations. Now you the relationships between the data entities needs to be setup:
Click on relationships and setup the following relationships. I have not connected the FinancialDimensionValues entity but this is possible.
After the relationships are created the magic can begin. You can create your own Pivot tables using the established connection.
Analyse data in Excel using Power BI
Another option is to analyse your data in Excel using Power BI. Before you can use this service, the data model needs to be created and published to the Power BI service. The data model can be build using aggregated and standard Odata entities or can use the data management entities via BYODB (bring your own data base). The data modelling in Power BI very similar as PowerQuery. This progress is made very by the user-friendly UI and guides from the Power BI community.
Once the data model has been published, the tables and measurements are available in Excel. Go to Power BI online, select the ellipses menu beside a report or dataset and from the menu, select Analyse in Excel.
When you first use Analyse in Excel, you need to install updates to the Excel libraries. You’ll be prompted to download and run Excel updates (this initiates installation of the SQL_AS_OLEDDB.msi Windows installer package). This package installs Microsoft AS OLE DB Provider for SQL Server 2016 RC0 (Preview). More information to get Analyse in Excel. I also have the Power BI add-in installed.
After installing the Power BI integration, you can connect to the data model and reports. You may need to sign in before you can connect to the data model. You also need a Power BI pro licence to use this service.
Select the workspace and dataset you want to load.
Once loaded the tables and measures are available to build a PivotTable. This technology is using PowerQuery, which is the same technology as Power BI. You can create a PivotTable by dragging and dropping the available PivotTable field in the filter, column, row and value areas.
You can only use Measures as values in the pivot table. This is something to keep in mind while building your Power BI data model.
The date model in Power BI is centrally controlled. This means all users will always use the same data and measurements. Power BI sets up access control through Active Directory (AD), the same control panel your organization uses for other Microsoft solutions. But what makes Power BI different is row level security, which allows your team to grant and restrict access on a very controlled level.
Not fond of PivotTables?
You can convert Pivot tables into formulas. This option is perfect if you want to retrieve the balance of eg. a specific vendor account or nominal account.
In the example below the pivot table will be converted into a formula, afterwards you can simple remove the value you aren’t interested in.
To convert the report filters into formulas as well, check the ‘convert reports filers’ tick box.
The result, a converted Pivot table.
You can still change the report filters within the formula bar.
Changing the filter will not impact the columns labels and connected data filters. Therefore, you should try to avoid using the same fields as column definition and filter. For example if the field Month is defined as filter and column definition and you were to change the filter: the column definition will not get updated. In the below example it would be best to create a column definition of Actuals in combination with the Month filter.
Refresh your data
You can refresh your data within Excel via Refresh All or simply press CTRL + ALT + F5. This will retrieve the latest data from Power BI. There may be a delay between the Power BI data model and the data source. If near real-time reporting is required, the Direct Query may be best solution for you. Find about more about refreshing data in Power BI. Direct Query is restricted to one data source and doesn’t allow users to drill through the data using either Power BI or Excel PivotTable.
Useful links
Comments