none
Intercompany report RRS feed

  • Question

  • Hello community,

    I am trying to create an inter company report in report builder which relies on data from Dynamic's NAV accounting system. 

    The purpose of the report is to tie up the amounts in the Company Balance and Affiliate Balance. For the company Balance, we look at the Company and Affiliate to get the value. For the Affiliate Balance, we look up the respective company (in reverse ordeR) for the Affiliate company and look up the Affiliate (prev. company) to get the affiliate amount. hope this make sense. 

    Challenge is to line up the row amounts on the same lines as per below for 90 rocker co.

    Company Affiliate GL Account Company Balance Affiliate Balance
    90 Rocker Co. Affiliate Co. 1 11111                                 -                                 -  
    90 Rocker Co. Affiliate Co. 2 11111                                 -                                 -  
    90 Rocker Co. Affiliate Co. 3 11111                                 -                                 -  
    90 Rocker Co. Affiliate Co. 4 11111                (58,870.74)                58,870.74
                   (58,870.74)                58,870.74
    Affiliate Co. 1 90 Rocker Co. 11111                               -  
    Affiliate Co. 2 90 Rocker Co. 11111                               -  
    Affiliate Co. 3 90 Rocker Co. 11111                               -  
    Affiliate Co. 4 90 Rocker Co. 11111                  58,870.74
                   58,870.74


    Saturday, July 11, 2020 3:35 PM

All replies

  • Hi myranthiru,

    Could you please share us some sample data?

    And sorry for my poor understanding, could you please use excel to show what you want the report to look like?

    Best Regards,

    Amelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 13, 2020 6:02 AM
  • Hi Amelia,

    Thank you for the response and I appreciate the help.

    The objective of the report is to report the Company Balance and the Affiliate Balance on the same row for tie/reconciliation.

    The report looks at the Company for example, 90 Rocker Co. and if there is a balance for the Affiliate (James) in the GL account 11111 then report that amount on the column under Company Balance.

    Second, is to find the same entry or the equivalent entry in the Affiliate Company(James). For example, I will have to look up James (Company) and find the affiliate (90 Rocker Co.) to report the Affiliate Balance.

    Company Affiliate GL Account Company Balance Affiliate Balance
    90 Rocker Co. James 11111                                 -                                 -  
    90 Rocker Co. Sandwich 11111                                 -                                 -  
    90 Rocker Co. Scrivener 11111                                 -                                 -  
    90 Rocker Co. WDL 11111                (58,870.74)                58,870.74

    Monday, July 13, 2020 9:47 PM
  • Amelia, I was thinking maybe i could write an custom vb code to associate the Company + Affiliate for Company Balance and Affiliate + Company for Affiliate Balance, just not sure how to. 

    Any thoughts.

    Monday, July 13, 2020 9:48 PM
  • Hi myranthiru,

    Sorry for my poor understanding, is 58870.74 the sum of company balance?

    Please refer to the following threads which might help:

    Reporting Services calculating sub totals using custom code issue

    SQL Reporting services - custom code sum

    Best Regards,

    Amelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 15, 2020 6:39 AM
  • Hi Amelia,

    Yes - the company balance is (58,870.74) which maybe a credit balance but that is besides the point. And we will have an offsetting entry in the Affiliate Company for 58,870.74.

    I will take a look at the threads you have provided.

    Thanks,

    Thursday, July 16, 2020 2:32 PM