Steps to set up a GL Transaction Integration with Analytical Accounting in Microsoft Dynamics GP

Introduction

This document will walk you through the necessary steps to setup a GL Transaction integration that includes Analytical Accounting mappings using Integration Manager for Microsoft Dynamics GP. This article will cover two common setup scenarios for importing Analytical Accounting information with a GL Transaction.

Business Scenario

Many companies use the Analytical Accounting module to help track their financial information. Many users also need to import transaction information into Dynamics GP which will include the AA information. Previous to Dynamics GP and Integration Manager 10.0, this was not possible as Integration Manager did not support AA. Those users would have to manually add the AA information after the transactions were imported, a process which could be very time consuming (and outlined in KB 897742). 

897742 How to move from Multidimensional Analysis to Analytical Accounting
https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;EN-US;897742

Background Information

Starting with Microsoft Dynamics GP/Integration Manager 10.0, Integration Manager now utilizes eConnect for select destination adapters (formerly the 'SQL Optimized' destination adapters). When using the eConnect destination adapters, it is now possible to import the following transaction types with Analytical Accounting information:

  • GL Transactions

  • Sales Transactions

  • Inventory Transactions

  • POP Receivings Transactions

In this article, we will look at two common scenarios for integrating GL Transactions that include AA information.


Step By Step

Activating Analytical Accounting

  1. In Dynamics GP, click on Tools under Microsoft Dynamics GP, point to Setup, point to Company, point to Analytical Accounting and click Setup. This will open the Analytical Accounting Setup Wizard window.

  2. Ensure that Create Default Record is checked and click Next, then click Finish.

  3. Go back to the Analytical Accounting Setup Wizard window again. (See step 1.)  The Create Default Record option should now be grayed out.

  4. Now check Activate Analytical Accounting option and click Next, then Finish.

  5. Click Yes to proceed with the activation.

  6. Click OK when it is finished.

Setting up sample AA Dimensions and Dimension Codes

  1. First, create three new test accounts that we will use for AA:

    1. In Microsoft Dynamcis GP, click on Cards point to Financial and click Account.

    2. Create the following accounts:

      Account

      Description

      Category

      999-9999-01

      AA Test Account 1

      Cash

      999-9999-02

      AA Test Account 2

      Cash

      999-9999-03

      AA Test Account 3

      Cash

    3. Accept all other defaults.

  2. Create the AA Transactions Dimension:

    1. In Microsoft Dynamcis GP, click on Cards, point to Financial, point to Analytical Accounting and click Transaction Dimension.

    2. Create the Trx Dimension by entering ELECTRONICS for the Trx Dimension.

    3. Select for the Data Type Alphanumeric.

    4. Enter Electronics for the Description 1 field.
      alternate text

    5. Click Save and close the Transaction Dimension Maintenance window.

  3. Create the AA Transaction Dimension Codes:

    1. In Microsoft Dynamics GP, click on Cards, point to Financial, point to Analytical Accounting, and click Transaction Dimension.

    2. Choose the Dimension ELECTRONICS.

    3. Enter XBOX for the Trx Dimension Code.

    4. Enter XBOX Hardware/Accessories for the Description.

    5. Select the Lookup and choose ELECTRONICS for the Linked to Node.
      alternate text

    6. Click Save.

    7. Keeping the window open, now enter TV for the Trx Dimension Code.

    8. Enter Televisions for Description 1.

    9. Select the Lookup and choose ELECTRONICS for the Linked to Node.
      alternate text

    10. Click Save.

    11. Enter STEREO for the Trx Dimension Code.

    12. Enter Stereo Equipment for Description 1.

    13. Select the Lookup and choose ELECTRONICS for the Linked to Node.
      alternate text

  4. Now grant access to the Dimension Codes:

    1. In Microsoft Dynamics GP, click on Tools, point to Setup, point to Company, point to Analytical Accounting and click User Access.

    2. For Trx Dimension, choose the Dimension ELECTRONICS.

    3. Select the Trx Dimension Code XBOX, then click the Distribute and Adjust check boxes at the bottom of the window to enable the code for all users.
      alternate text

    4. Click Save.

    5. Repeat steps 'B' and 'C' for the STEREO and TV" Dimension Codes.

  5. Create the Accounting Class to link our test accounts we created to the AA Dimensions:

    1. In Microsoft Dynamics GP, click on Cards, point to Financial, point to Analytical Accounting and click Accounting Class.

    2. Enter the Class ID ELECTR_CLASS

    3. Enter Electronics Account Class for the Description 1 field.

    4. In the Grid you should see the ELECTRONICS Trx Dimension. Change the Analysis Type to Optional.
      alternate text

    5. Click the Accounts in the bottom right corner.

    6. You may be prompted to Save changes to the Accounting class. Click Save.

    7. Scroll down in the list of accounts and find the three accounts you created in step 1.

    8. Mark the Link checkbox next to each account 999-9999-01, 999-9999-02 and 999-9999-03
      alternate text

      • You may be prompted with a message saying "This change will affect un-posted transactions and adjustments, if any. Do you want to continue?"

      • Click Continue.

    9. Close the Account Class Link window to return back to the Accounting Class Maintenance window.

    10. Click Save and close the window.


Building the Integrations

GL Transaction (with Sequence required) – Example Scenario:
One of the Analytical Accounting enabled accounts appears more than once in the distributions. Also, the distribution amount is being split out to multiple Dimension Codes. Because the same distribution account appears more than once, we must map the Line Item Sequence value on the GL Distributions so the AA information can be linked to the correct distribution line/amount.  

Furthermore, since the distribution amount for a single line is being split out into multiple Dimension Codes, we must also map the "Assign ID" field for the Analytics Dimension.

  1. To support these requirements, create a Tab Delimited text file for the header and distributions source queries with the following data (include the column headings as shown):  

    JournalNum

    TrxDate

    Account

    Amount

    Sequence

    10000

    4/12/2017

    000-1100-00

    500

    500

    10000

    4/12/2017

    000-1200-00

    -500

    1000

    10001

    4/12/2017

    999-9999-01

    16200

    500

    10001

    4/12/2017

    000-1100-00

    -16200

    1000

    10001

    4/12/2017

    999-9999-01

    2000

    1500

    10001

    4/12/2017

    000-1200-00

    -2000

    2000

  2. Save the file as a Tab Delimited text file (*.TXT). For this example, name the file "AA GLTrx-with Seq.txt".

  3. Create a second Tab Delimited source file that contains the AA Distribution information (include the column headings as shown):  

    JournalNum

    Account

    Amount

    Dimension

    DimensionCode

    CodeAmount

    AAAsignID

    Sequence

    10001

    999-9999-01

    16200

    ELECTRONICS

    XBOX

    8000

    1

    500

    10001

    999-9999-01

    16200

    ELECTRONICS

    TV

    7000

    2

    500

    10001

    999-9999-01

    16200

    ELECTRONICS

    STEREO

    1200

    3

    500

    10001

    999-9999-01

    2000

    ELECTRONICS

    XBOX

    1100

    1

    1500

    10001

    999-9999-01

    2000

    ELECTRONICS

    TV

    600

    2

    1500

    10001

    999-9999-01

    2000

    ELECTRONICS

    STEREO

    300

    3

    1500



    *NOTE: The AAssignID value will be used since we are splitting the distribution amount across multiple Dimension codes. The Sequence value is required because the distribution account 999-9999-01 appears on more than one distribution line, therefore the Sequence value is required to link the AA information to the correct distribution line.

  4. Save the file as a Tab Delimited text file (*.TXT). For this example, name the file AA GLTrx-with Seq AA Dimension.txt.

  5. Open Integration Manager and click on New Integration. Give it the name GL Transaction - AA with Sequence".




    Create the Header Source Query

  6. Click on Add Source, click on Text, then click on Define New Text.

  7. Give the source query the name GL Header and browse out to the first file you created named AA GLTrx-with Seq.txt.

  8. Choose Tab as the delimiter and check the box First Row Contains Column Names.
    alternate text

  9. Since we are using a single source file for the Header and Distribution lines, we need to setup the Grouping on the header fields. Click on the Sorting tab.

  10. Add JournalNum and TrxDate to the Group By fields list, then click OK to save the query. Click OK again on Warning stating there are fields named as Show that are not in the Group By list. This is expected.

  11. Right-click on the GL Header query and make sure you can preview the data as follows:
    alternate text


    Create the Distribution Source Query

  12. Click on Add Source, click on Text, then click on Define New Text.

  13. Give the source query the name GL Distributions and again browse out to the first file you created named AA GLTrx-with Seq.txt.

  14. Choose Tab as the delimiter and check the box First Row Contains Column Names.

    alternate text

  15. Click OK to save the query. You will receive a message stating that you must setup Query Relationship. Click OK to dismiss the dialog. We will set that up later once all the queries are created.

  16. Right-click on the GL Distributions query and make sure you can preview the data as follows:
    alternate text


    Create the AA Dimension Source Query

  17. Click on Add Source, click on Text, then click on Define New Text.

  18. Give the source query the name GL AA Dimension" and browse out to the second file you created for the AA information named AA GLTrx-with Seq AA Dimension.txt.

  19. Choose Tab as the delimiter and check the box First Row Contains Column Names.

    alternate text

  20. Click OK to save the query.

  21. Right-click on the GL AA Dimension query and make sure you can preview the data as follows:
    alternate text


    Create the AA Dimension Code Source Query

  22. Click on Add Source, click on Text, then click on Define New Text.

  23. Give the source query the name GL AA Dimension Code and browse out to the second file you created for the AA information named AA GLTrx-with Seq AA Dimension.txt.

  24. Choose Tab as the delimiter and check the box First Row Contains Column Names.

    alternate text

  25. Click OK to save the query.

  26. Right-click on the GL AA Dimension Code query and make sure you can preview the data as follows:
    alternate text


    Setting up the Query Relationships

  27. Double-click on Query Relationships.

    1. Since the Journal Number is the key field that differentiates one transaction from the next, this key field should go all the way through the Query relationship chain.

      alternate text

    2. To link the AA information, since the AA Dimension information is linked per distribution line, next we need to establish a link between the GL Distributions query and the "L AA Dimension query. As stated in the overview, a given GL account may appear more than once in a single transaction, there we must us the Sequence field to link the AA Dimension to the correct distribution line.
      alternate text

    3. Continuing down the relationship, the AA Dimension Codes are linked back to a given AA Dimension. In this example, we need to continue linking with the Sequence field and also add the DimensionCode field. At this point, all the appropriate relationships have been established so your Query Relationship window should look like the following:
      alternate text




    Add the Destination Adapter

  28. Click on Add Dest., expand Microsoft Dynamics GP eConnect, click on Financial and select GL Transaction.

  29. In the Server Name field, enter in the name of your SQL Server instance (not a DSN name) and the company database name.
    alternate text


    Define the field mappings

  30. Double-click on Destination Mapping to open the field mapping window.

  31. For GL Transactions with AA information, the Journal Entry # is REQUIRED. This field cannot be defaulted in! Some advanced users will use a VBScript on the Journal Entry# field to get the next number, but it does have to be supplied in the mapping otherwise the AA information cannot be linked to the transaction correctly.

  32. Provide a constant for the Batch ID and Reference fields.

    alternate text

  33. Click on the Entries folder. Map the Account Number, Debit/Credit Amount and Sequence Line to the fields from the GL Distributions query.

    alternate text

    *NOTE: The Sequence Line field is required in this case since the GL Account may appear more than once within the same GL Transacion. Without this field mapped, the AA information cannot be linked to the correct distribution.

  34. Click on the Analytics folder. Map the Assign ID and Amount field from the GL AA Dimension source query as follows:

    alternate text
    *NOTE: The “Assign ID” field is required in this case since we are splitting out the distribution amount to more than one AA Dimension Code.

    *Optional: If the percentages are known instead of the Amounts, you can map the “Assigned Percent” field instead of the Amount field. You do not need to map both!

  35. Click on the Dimensions folder. In this example, we are only using Alphanumeric Dimension Codes, so we will map the Transaction Dimension and Transaction Dimension AlphaNumeric" from the GL AA Dimension Code source query.

  36. Map the "Transaction Dimension" to the Dimension field from the source query and "Transaction Dimension AlphaNumeric" to the DimensionCode field from the source query:

    alternate text

  37. The field mappings are now complete! Save the Integration.

  38. Go ahead and Run the integration now to bring in the two Journal Entries.




    Verify the Transaction Integrated as expected

  39. The first Journal Entry does not have any AA information, so we will look at the second transaction, Journal Number 10001.

    alternate text

  40. To inspect the AA distributions, click on the first distribution line, then click the Analytical Accounting button. We should see the distribution amount of $16,200 has been split across 3 different AA Dimension Codes as follows:

    alternate text

    alternate text

    alternate text

  41. You can use the VCR buttons at the bottom of the window to navigate to the second Analytics distribution where you will see that the distribution amount of $2000 has also been split out across the 3 AA Dimension Codes.



GL Transaction (No Sequence required) – Example Scenario:
The Analytical Accounting enabled accounts do NOT appear more than once in the distributions. The AA information represents a one-to-one relationship in the source data. This means that a single distribution line links to only one AA Dimension which has only one AA Dimension Code. Because it’s one-to-one, we do not need a Sequence Number or the Assign ID fields. For this example scenario, we can use a single source file.

  1. To support these requirements, create a Tab Delimited text file (*.TXT) with the following data:

    JournalNum

    TrxDate

    Account

    Amount

    Dimension

    DimensionCode

    10003

    4/12/2017

    000-1100-00

    500

    10003

    4/12/2017

    000-1200-00

    -500

    10004

    4/12/2017

    999-9999-01

    8000

    ELECTRONICS

    XBOX

    10004

    4/12/2017

    999-9999-02

    7000

    ELECTRONICS

    TV

    10004

    4/12/2017

    999-9999-03

    7000

    ELECTRONICS

    STEREO

    10004

    4/12/2017

    000-1100-00

    -16200

    10005

    4/12/2017

    999-9999-01

    1100

    ELECTRONICS

    XBOX

    10005

    4/12/2017

    999-9999-02

    600

    ELECTRONICS

    TV

    10005

    4/12/2017

    999-9999-03

    300

    ELECTRONICS

    STEREO

    10005

    4/12/2017

    000-1100-00

    -2000

  2. Save the file as a Tab Delimited text file (*.TXT). For this example, name the file "AA GLTrx-No Seq.txt"

  3. Open Integration Manager and click on New Integration. Give it the name "GL Transaction - AA without Sequence".




    Create the Header Source Query

  4. Click on Add Source, click on Text, then click on Define New Text.

  5. Give the source query the name GL Header – No Seq and browse out to the source file you created named AA GLTrx-No Seq.txt.

  6. Choose Tab as the delimiter and check the box First Row Contains Column Names.

    alternate text

  7. Since we are using a single source file, we need to setup the Grouping on the header fields. Click on the Sorting tab.

  8. Add JournalNum and TrxDate to the Group By fields list, then click OK to save the query. Click OK again on Warning stating there are fields named as Show that are not in the Group By list. This is expected.

  9. Right-click on the GL Header – No Seq query and make sure you can preview the data as follows:
    alternate text


    Create the Distribution Source Query

  10. Click on Add Source, click on Text, then click on Define New Text.

  11. Give the source query the name GL Distributions - No Seq and again browse out to the source file you created named AA GLTrx-No Seq.txt.

  12. Choose Tab as the delimiter and check the box First Row Contains Column Names.

    alternate text

  13. Click OK to save the query. You will receive a message stating that you must setup Query Relationship. Click OK to dismiss the dialog. We will set that up later once all the queries are created.

  14. Right-click on the GL Distributions – No Seq query and make sure you can preview the data as follows:
    alternate text


    Create the AA Dimension Source Query

  15. Click on Add Source, click on Text, then click on Define New Text.

  16. Give the source query the name GL AA Dimension – No Seq and browse out to the source file you created named AA GLTrx-No Seq.txt.

  17. Choose Tab as the delimiter and check the box First Row Contains Column Names.

    alternate text

    1. Click on the Filter tab and add a restriction with the following settings:
      Column: Dimension
      Operator: <>
      Value: 0

    2. Click "And Into Criteria"

      alternate text

  18. Click OK to save the query.

  19. Right-click on the GL AA Dimension – No Seq query and make sure you can preview the data as follows:
    alternate text


    Create the AA Dimension Code Source Query

  20. This query will be setup identical to the Dimension query we just created since this example is a one-to-one sample for the distribution/dimension/dimension code relationship. Click on Add Source, click on Text, then click on Define New Text.

  21. Give the source query the name GL AA Dimension Code – No Seqand browse out to the source file you created named AA GLTrx-No Seq.txt.

  22. Choose Tab as the delimiter and check the box First Row Contains Column Names.  
    alternate text

    1. Click on the Filter tab and add a restriction with the following settings:
      Column: Dimension
      Operator: <>
      Value: 0

    2. Click "And Into Criteria"

      alternate text

  23. Click OK to save the query.

  24. Right-click on the GL AA Dimension Code - No Seq query and make sure you can preview the data as follows:
    alternate text


    Setting up the Query Relationships

  25. Double-click on Query Relationships.

    1. Since the Journal Number is the key field that differentiates one transaction from the next, this key field should go all the way through the Query relationship chain.

      alternate text

    2. To link the AA information, since this example demonstrates a one-to-one relationship between the Distribution/Dimension/Dimension Code, the relationship setup is fairly straightforward. All we need to add is a link to the Account field since the AA information pertains to a particular account number.
      alternate text

    3. Continuing down the relationship, the AA Dimension Codes are linked back to a given AA Dimension. In this example, we need to continue linking with the Sequence field and also add the DimensionCode field. At this point, all the appropriate relationships have been established so your Query Relationship window should look like the following:
      alternate text




    Add the Destination Adapter

  26. Click on Add Dest., expand Microsoft Dynamics GP eConnect, click on Financial and select GL Transaction.

  27. In the Server Name field, enter in the name of your SQL Server instance (not a DSN name) and the company database name.  
    alternate text


    Define the field mappings

  28. Double-click on Destination Mappings to open the field mapping window.

  29. For GL Transactions with AA information, the Journal Entry # is REQUIRED. This field cannot be defaulted in! Some advanced users with use a VBScript on the Journal Entry# field to get the next number, but it does have to be supplied in the mapping otherwise the AA information cannot be linked to the transaction correctly.

  30. Provide a constant for the Batch ID and Reference fields.
    alternate text

  31. Click on the Entries folder. Map the Account Number and Debit/Credit Amount to the fields from the GL Distributions – No Seq query.
    alternate text

  32. Click on the Analytics folder. Map the Amount field from the GL AA Dimension – No Seq source query as follows:

    alternate text

    *Optional: If the percentages are known instead of the Amounts, you can map the “Assigned Percent” field instead of the Amount field. You do not need to map both!

  33. Click on the Dimensions folder. In this example, we are only using Alphanumeric Dimension Codes, so we will map the Transaction Dimension and Transaction Dimension AlphaNumeric from the GL AA Dimension Code – No Seq source query.

  34. Map the "Transaction Dimension" to the Dimension field from the source query and "Transaction Dimension AlphaNumeric" to the DimensionCode field from the source query:

    alternate text

  35. The field mappings are now complete! Save the Integration.

  36. Go ahead and Run the integration now to bring in the two Journal Entries.




    Define the field mappings

  37. The first Journal Entry does not have any AA information, so we will look at the second transaction, Journal Number 10004.
    alternate text

  38. To inspect the AA distributions, click on the first distribution line, then click the Analytical Accounting button. We should see the distribution amount of $8000 assigned to the XBOX dimension code as follows:
    alternate text

  39. You can use the VCR buttons at the bottom of the window to navigate to the second Analytics distribution where you will see that the distribution amount of $7000 has been assigned to the TV Dimension Code.
    alternate text

  40. Lastly, the 3rd AA distribution has the amount of $1200 assigned to the STEREO dimension code.
    alternate text

  41. You can also view Journal Entry 10005 in the same way to verify that the distributions have been assigned to the AA Dimension Codes as expected.

More Information

n/a

Query Words

AA, IM, import, integrate, AA data, General Ledger

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×