What’s New for the General Journal and Microsoft Excel Functionality in the new Microsoft Dynamics AX (V7)

The general journal is a powerful tool in Microsoft Dynamics AX.  Journals are used to create manual transactions that can affect anything from the ledger to customers, vendors, and more once posted.  Before posting though, setup can demand mandatory reviews, approvals, and more.  With such wide versatility, the general journal is used practically daily.  For larger journals though, it can be cumbersome to work solely within Microsoft Dynamics AX to create and process these transactions.

Enter Microsoft Excel, hereupon referred to as Excel.  Falling under the scope of Microsoft, an integration exists to allow users to work with journals in Excel and then upload them into Microsoft Dynamics AX.  From payroll to invoicing, these journals can be large and detail heavy.  Microsoft Dynamics AX does not allow for some of the shortcuts that Excel allows to make this easier, i.e. copy and paste.  Due to the popularity and learning curve of Excel, users frequently want to know how the functionality works as to execute it.

In the 2012 version, this Excel upload feature was cumbersome to set up and then execute for importing journals.  Developers first had to make sure the correct add-in was installed to allow Excel to connect with Microsoft Dynamics AX.  From the developer workspace, the ledger service had to be activated.  A port had to then be set up to allow Excel to read, write, and access the journals within Microsoft Dynamics AX 2012.  A data source had to be created next to allow Excel to find the correct tables—Microsoft Dynamics AX stores all of its data in a large number of tables.  Excel then had to be set up to connect with Microsoft Dynamics AX 2012.  From there, the fields would have to be pulled into Excel before end users could work with them.

It was a frustrating and cumbersome process for users to have to set up.  This had to do with users needing to know the exact nature of the following: services, inbound ports, document data sources, and table hierarchies.  Without the exact setup for each portion, the functionality for journal uploading from Excel would not work.  In fact, this existed for all services that Excel can interact with besides just the ledger service.  For the most recent version of Microsoft Dynamics AX- the new Microsoft Dynamics AX (V7), the functionality has been completely redone to where no technical set-up is required to be used.

The ability to export to Excel has existed in Microsoft Dynamics AX for a while.  However, a setup like the one explained above was usually required before end users could use the functionality.  The new Microsoft Dynamics AX (V7) now comes out-of-the-box with a large number of these setups completed.  End user access exists from day one.  It now just becomes a matter of knowing how these new changes have taken effect before taking advantage of them.  The following will go through the setup and how to create, upload and post a journal using Excel alongside the new Microsoft Dynamics AX (V7)  while also explaining the logic behind the process along the way.

The idea of the following is to show users how to access and use the Excel functionality.  With that, a simple journal will be created and posted.  As this is only the basic foundation, other options and design choices will be pointed out along the way to show how the functionality can be enhanced for more advanced operations.

The new Microsoft Dynamics AX (V7) has given general journals some navigational changes as well as a new workspace.  Microsoft Dynamics AX’s navigation has changed to focus on functionality over its focus on main groups—i.e. common, inquiries, periodic, journals, reports, and setup.  Notice that the journal entries group includes all journals and tasks related to them in this module—elimination journals are not here.  However, those that have another function appear again in the module—i.e. allocation journals also appear in the allocation group.

The general journal processing workspace was also added to supplement the journal entries group.  From here, users can keep up with all journals or just those he or she is responsible for.  Selecting an option from the tile grid will open up the page in Microsoft Dynamics AX.  Selecting an option from the list grid will open up the related journals in the table.  Either way, the user is able to work with journals either directly in the page or indirectly via the table.

In order to work with Excel, a new journal needs to be created and opened.  This can be done via the main page or the workspace.  For this example, the description will be changed to Excel Upload Test for easier tracking of progress.

Once inside the journal, it will look like a standard new journal.  The point of interest will be the Microsoft icon on the right side of the page in the action pane.  This icon will give two options: Open in Excel and Export to Excel. Microsoft Dynamics AX works with Excel via a concept called entity backing.  The data entity essentially is a table that exists for storing relevant data.  If the data entity exists, a read and write connection exists with Excel.  If the data entity does not exist, only a read connection will exist with Excel.

There are many data entities and more will come in later updates.  When working within the new Microsoft Dynamics AX (V7) overall, keep an eye out for this icon.  For some areas, working in Excel may be more efficient depending on the workload.  Open in Excel states that the data entity exists and users can work within Excel.  Export to Excel states that the data entity does not exist and users can only export the data to Excel as a report or statement.

For the journal itself, notice that there are two options to work with inside Excel as defaults: General journal line entity and Invoice journal line.  General journal line entity allows the user to create and post standard journal transactions.  Invoice journal line allows the user to create and post invoices via the general journal.  For the purposes of this blog, the general journal line entity will be the one to work with.

Once an option is selected, a pane will open on the right side of the page to give more options on which format to open in Excel with.  For this example, Download will be used as that will pull the entry into Excel to be worked with.  There are two options to save this data to: OneDrive for Business and SharePoint.  If there is no account information setup for one or the other, the options will be grayed out as there is no legal file path for Microsoft Dynamics AX to follow.

Selecting the Download option will also trigger a popup asking if the file should be opened in Excel, saved, or saved as.  Since this will be a simple upload with no meaning, the file will be opened for this example.  If the file needs to be saved, do so accordingly.

Excel will open up automatically and the default format from Microsoft Dynamics AX will appear.  Notice that although the title says General Ledger Journal Entry and the columns are correct, there are cells that have the value .  This is due to the fact that there is no active data connection.  These fields are tied directly to the new Microsoft Dynamics AX—the information concerns the journal information and the debit and credit amounts.  The connection must be established before using the template.  Notice that the file is currently in Protected View with the option for Enable Editing.  Click on it.

A pane will open on the right side that will act as the data connection between Microsoft Dynamics AX and Excel.  It will initially be blank with only a Sign in link since the connector will verify user identity before connecting to Microsoft Dynamics AX.  Sign in to establish the connection and to work within Excel and Microsoft Dynamics AX.

Once authenticated and signed in, the connection will be established.  Notice that the cells with no values initially are now populated with the information of the journal being worked with.  The data connector is now available as well with different options to assist with the functional abilities.

Notice the five options at the bottom of the data connector. New creates a new record for the file.  Refresh clears the data.  Publish will upload the information back to Microsoft Dynamics AX.  Filter will create a filter based on input criteria.  Design will allow for additions and changes to the worksheet.  Designing the worksheet will allow for additional and more advanced operations as needed.  This feature allows users to work with any tables and fields that exist within Microsoft Dynamics AX.  This capability allows for budget uploads, payroll uploads, and more.

Input the journal line information.  For this example, rent of $4,000.00 is being paid from the petty cash account.  While entering data, pay attention to the data connector.  As cells with values that are linked to other tables in Microsoft Dynamics AX are highlighted, a list of choices will appear—i.e. Offset company pulls up a list of companies.  Once the line is complete, it can be published back to Microsoft Dynamics AX.

Once Publish is selected, it will either succeed or fail.  At the bottom of the data connector is a status bar.  The results of the publish will be displayed here.  If the publish failed, click the flag in the bottom right corner to open up the messages.  All error messages will be displayed here. Each error message has a description that can help solve the error as to publish successfully.  Resolve errors as necessary.

Going back to Microsoft Dynamics AX, open up the same journal to verify that the publish was successful.

Once the verification is complete, post the journal.  That is the entire process from opening the journal in Excel, creating the manual transactions, uploading into Microsoft Dynamics AX and posting the journal.  The process is much simpler than previous versions as this functionality exists straight out of the box with no setups required for the basic functionality.

The original capability of exporting pages into Microsoft Dynamics AX as a report or statement still exists.  Above explores Open in Excel, and here is a quick summary of Export to Excel.

Notice that the Export feature sets up the worksheet differently.  This feature is essentially set up for reporting and informational purposes.  There is no change in this one, but it is important to see the difference between the Open in Excel and Export to Excel functions that Microsoft Dynamics AX introduces.

Microsoft Dynamics AX has improved this functionality to allow users to quickly jump in and use it but notice that there were no financial dimensions included here.  This function requires a more complex setup that requires a developer.  It involves the developer workspace—this version splits this from the main environment and places it in Visual Studio—and coding to allow for this capability.  It can be done, but it is more of a hassle for end users than the previous setup of this functionality in prior versions.  In future updates, this function should be integrated into the new Microsoft Dynamics AX to prevent the need for development.

In the meantime, the out-of-the-box functionality for this is operational from the start and allows user to create journals, small or large, in Microsoft Dynamics AX either through manual entry or copying and pasting.  It is a great step forward from the tedious setups in prior versions.  Once the update is released, this functionality will be perfect for many workloads.  Here’s to hoping that one of the upcoming updates includes financial dimension integration without developer setup!