Enhance Dynamics GP Payables: Introducing Bucket Functionality for Improved Management

Table of Contents

Managing payables efficiently is crucial for any business, and the Aged Trial Balance report in Microsoft Dynamics GP is a fundamental tool for tracking outstanding vendor invoices. By default, this report provides four aging buckets, offering a basic overview of your payables. However, for organizations requiring more granular control and deeper insights into their aged payables, extending the number of aging buckets becomes essential. This comprehensive guide outlines the process of adding additional aging buckets to the Payables Management Aged Trial Balance report, specifically for the detail print option, using Report Writer in Microsoft Dynamics GP. This enhancement allows for more refined categorization of outstanding liabilities, providing a clearer picture of your financial obligations and aiding in better cash flow management and strategic payment planning.

The standard Aged Trial Balance report typically displays aging periods such as Current, 1-30 days, 31-60 days, and 61+ days. For businesses with complex payment terms, longer aging cycles, or specific reporting requirements, these four buckets may not be sufficient. Expanding the number of buckets provides the flexibility to define more specific aging periods, such as 61-90 days, 91-120 days, or even beyond, offering a more precise breakdown of when invoices become due. This level of detail empowers finance teams to identify long-outstanding debts, prioritize payments, and proactively manage vendor relationships, ultimately leading to more robust financial health and improved operational efficiency.

Step 1: Back Up the Reports.dic File and Then Start Report Writer¶

Before making any modifications within Microsoft Dynamics GP, it is paramount to create a backup of your existing Reports.dic file. This precautionary measure safeguards your current custom reports and ensures that you can easily revert to a previous state should any unforeseen issues arise during the modification process. The Reports.dic file contains all customized reports, so its integrity is vital for maintaining your reporting structure. Failing to back up this file could lead to irretrievable loss of custom report layouts.

To locate your Reports.dic file, follow these precise steps, which may vary slightly depending on your Microsoft Dynamics GP version:

1. Navigate to the launch file editor:
   • For Microsoft Dynamics GP 10.0 and later: From the Microsoft Dynamics GP menu, point to Tools, then Setup, then System, and finally click Edit Launch File.
   • For Microsoft Dynamics GP 9.0 and Microsoft Business Solutions - Great Plains 8.0: From the Tools menu, point to Setup, then System, and then click Edit Launch File.
2. If prompted, enter your system password to proceed. This ensures only authorized users can modify system settings.
3. In the Edit Launch File window, identify your product:
   • For Microsoft Dynamics GP 10.0 and Microsoft Dynamics GP 9.0: Click Microsoft Dynamics GP.
   • For Microsoft Business Solutions - Great Plains 8.0: Click Great Plains.
4. Observe the path displayed in the Reports box. This path indicates the location of your Reports.dic file. Make a note of this location, then navigate to it using Windows Explorer and copy the Reports.dic file to a safe backup directory.
5. After noting the path and backing up the file, click OK to close the Edit Launch File window.

Once your Reports.dic file is securely backed up, you can proceed to open Report Writer, the tool used for customizing reports in Dynamics GP.

1. To launch Report Writer:
   • For Microsoft Dynamics GP 10.0 and later: From the Microsoft Dynamics GP menu, point to Tools, then Customize, and click Report Writer.
   • For Microsoft Dynamics GP 9.0 and Microsoft Business Solutions - Great Plains 8.0: From the Tools menu, point to Customize, and then click Report Writer.
2. In the Product list within the Report Writer launch window, select the appropriate product:
   • For Microsoft Dynamics GP 10.0 and Microsoft Dynamics GP 9.0: Click Microsoft Dynamics GP.
   • For Microsoft Business Solutions - Great Plains 8.0: Click Great Plains.
3. Click OK to open the Report Writer application. This action launches the primary interface where you will modify the Payables Management Aged Trial Balance report.

Step 2: Open the Report¶

With Report Writer open, the next step involves locating and opening the specific report that requires modification. Our focus is the “PM Aged TB - Detail” report, which provides a detailed breakdown of aged payables.

1. In the Report Writer main window, click on the Reports button. This action will open the Report Writer Reports window, which lists all available reports within your Dynamics GP installation.
2. Within the Report Writer Reports window, you will see two panes: Original Reports and Modified Reports. First, check the Modified Reports pane to see if “PM Aged TB - Detail” is already listed. If it is, this means a modified version of the report already exists.
3. If the “PM Aged TB - Detail” report is not listed in the Modified Reports pane, locate it in the Original Reports pane. Select “PM Aged TB - Detail” from the list of original reports.
4. Click the Insert button. This action moves the “PM Aged TB - Detail” report from the Original Reports pane to the Modified Reports pane, creating a copy that you can now customize without altering the original system report.
5. Once “PM Aged TB - Detail” appears in the Modified Reports pane, select it.
6. Click the Open button. This action opens the Report Definition window for the selected report.
7. In the Report Definition window, click the Layout button. This will open the Report Layout window, which is a graphical design interface where you can add, remove, and reposition fields on the report. This is where the core modifications will take place.

Step 3: Create Calculated Fields¶

To introduce new aging buckets beyond the default four, we must define new calculated fields within Report Writer. These calculated fields will serve as placeholders for the legends (headers) that define the new aging periods on the report. By creating distinct calculated fields for each new legend, we ensure that they can be individually placed and managed within the report layout. We will create three new calculated fields: LEGEND26, LEGEND27, and LEGEND28, corresponding to our new aging buckets (e.g., Bucket 5, Bucket 6, and Bucket 7).

1. In the Toolbox window, which is usually located on the left side of the Report Layout window, click on Calculated Fields.
2. Click the New button to open the Calculated Field Definition window.
3. In the Name field, type LEGEND26. This will be the legend for our first new aging bucket.
4. In the Result Type area, select String. Legends are text-based descriptions.
5. In the Expression Type area, select Calculated. This indicates that the field’s value will be derived from a calculation or a reference.
6. Click the Fields tab to define the expression.
7. In the Resources list, select RW Legends. The RW Legends table contains predefined legend values used throughout Dynamics GP reports.
8. In the Field list, select Legend.
9. Click the Add button.
10. When prompted for an array, type 26, and then click OK. This specifically links LEGEND26 to the 26^th legend in the RW Legends array, which can be configured in Dynamics GP’s aging periods setup.
11. Click OK to save the LEGEND26 calculated field.

Now, repeat the process for LEGEND27:

1. In the Toolbox list, click Calculated Fields, and then click New.
2. In the Name field, type LEGEND27.
3. Click String in the Result Type area, and then click Calculated in the Expression Type area.
4. Click Fields.
5. Click RW Legends in the Resources list, click Legend in the Field list, and then click Add.
6. When prompted for any array, type 27, and then click OK.
7. Click OK to save the LEGEND27 calculated field.

Finally, repeat the process for LEGEND28:

1. In the Toolbox list, click Calculated Fields, and then click New.
2. In the Name field, type LEGEND28.
3. Click String in the Result Type area, and then click Calculated in the Expression Type area.
4. Click Fields.
5. Click RW Legends in the Resources list, click Legend in the Field list, and then click Add.
6. When prompted for any array, type 28, and then click OK.
7. Click OK to save the LEGEND28 calculated field.

You have now successfully created the three calculated fields that will serve as the headers for your new aging buckets on the report. These fields will dynamically display the aging period descriptions configured in your Dynamics GP setup.

Step 4: Add Calculated Fields to the Report¶

After creating the calculated fields, the next critical step is to place them onto the report layout. This involves dragging the new fields from the Toolbox to specific sections of the report (Header, Body, Footer) and configuring their visibility and display properties. Each aging bucket typically requires three types of fields to be placed: the legend (header), the period’s voucher amount, and the period’s payment amount, along with a total for that period.

We will add fields for Bucket 5, Bucket 6, and Bucket 7.

Adding Fields for Bucket 5 (using LEGEND26):

1. In the Toolbox window, ensure Calculated Fields is selected. Drag the LEGEND26 field to the H1 (Report Header 1) section of the Report Layout. Position it to the right of the existing LEGEND25 field. This places the header for your fifth aging bucket.
2. Still in the Toolbox, ensure Fields (or relevant table for transaction data like Payables Management) is selected. Drag the Current TRX5 field to the H2 (Report Header 2) section of the Report Layout. Position this field to the right of the Period4 - Vouch field. This field will temporarily hold the total transactions for the fifth period.
3. On the Report Layout, double-click on the Current TRX5 field that you just placed in the H2 section. In the Field Options window, select Invisible in the Visibility field, and then click OK. This makes the raw Current TRX5 field itself hidden in the header, as it’s primarily used for calculations.
4. From the Toolbox, drag the Period5-Voucher field (from the Payables Master or relevant table) to the H2 section of the Report Layout. Carefully position the Period5-Voucher field directly over the Current TRX5 field you just made invisible. This field will display the total voucher amount for the fifth aging period.
5. On the Report Layout, double-click on the Period5 - Vouch field. In the Field Options window, select Hide When Empty in the Visibility field, and then click OK. This ensures that the field only appears if there is data for that period, preventing empty spaces on the report.
6. From the Toolbox, drag the Period5 - Pay field to the B (Body) section of the Report Layout. Position the Period5 - Pay field in line with the other PeriodX - Pay fields. This field will display the payment amount for individual transactions within the fifth aging period.
7. On the Report Layout, double-click on the Period5-Pay field. In the Field Options window, select Hide When Empty in the Visibility field, and then click OK. This ensures the field is only shown when relevant data is present.
8. From the Toolbox, drag the Current TRX5 field to the F1 (Report Footer 1) section of the Report Layout. Position this Current TRX5 field to the right of the CurrentTRX4 field. This instance of Current TRX5 will be used to sum the total for the fifth aging period at the report level.
9. On the Report Layout, double-click on the Current TRX5 field in the F1 section. In the Field Options window, select Sum in the Display Type box, and then click OK. This configures the field to display the cumulative sum of all transactions within the fifth aging period.
10. From the Toolbox, drag the LEGEND26 field to the RF (Report Footer) section of the Report Layout. Position this LEGEND26 field to the right of the LEGEND25 field in the footer. This provides a clear legend for the summed total.
11. On the Report Layout, double-click on the LEGEND26 field in the RF section. In the Field Options window, select Data in the Display Type field, and then click OK. This ensures the legend text is displayed as entered.
12. From the Toolbox, drag the Current TRX5 field to the RF (Report Footer) section of the Report Layout. Position this Current TRX5 field to the right of the Current TRX4 field in the footer. This instance will display the grand total for the fifth aging bucket at the report footer level.
13. On the Report Layout, double-click on the Current TRX5 field in the RF section. In the Field Options window, select Sum in the Display Type field, and then click OK. This ensures the grand total of the fifth aging bucket is correctly summed and displayed.

Adding Fields for Bucket 6 (using LEGEND27):

Now, repeat the same detailed process for LEGEND27 and its corresponding transaction fields (Period6-Voucher, Period6-Pay, Current TRX6).

1. In the Toolbox window, drag the LEGEND27 field to the H1 section of the Report Layout. Position it to the right of the newly placed LEGEND26 field.
2. Drag the Current TRX6 field to the H2 section of the Report Layout. Position the Current TRX6 field to the right side of the Period5 - Vouch field.
3. In the Report Layout, double-click Current TRX6, click Invisible in the Visibility field, and then click OK.
4. In the Toolbox window, drag the Period6-Voucher field to the H2 section of the Report Layout. Position the Period6-Voucher field over the Current TRX6 field.
5. In the Report Layout, double-click Period6 - Vouch, click Hide When Empty in the Visibility field, and then click OK.
6. In the Toolbox window, drag the Period6 - Pay field to the B section of the Report Layout. Position the Period6 - Pay field to the right of the Period5 - Pay field.
7. In the Report Layout, double-click Period6 - Pay, click Hide When Empty in the Visibility field, and then click OK.
8. In the Toolbox window, drag the Current TRX6 field to the F1 section of the Report Layout. Position the Current TRX6 field to the right side of the Current TRX5 field.
9. In the Report Layout, double-click Current TRX6, click Sum in the Display Type box, and then click OK.
10. In the Toolbox window, drag the LEGEND27 field to the RF section of the Report Layout. Position the LEGEND27 field to the right side of the LEGEND26 field.
11. In the Report Layout, double-click LEGEND27, click Data in the Display Type field, and then click OK.
12. In the Toolbox window, drag the Current TRX6 field to the RF section of the Report Layout. Position the Current TRX6 field to the right side of the Current TRX5 field.
13. In the Report Layout, double-click Current TRX6, click Sum in the Display Type field, and then click OK.

Adding Fields for Bucket 7 (using LEGEND28):

Lastly, repeat the detailed steps for LEGEND28 and its corresponding transaction fields (Period7-Voucher, Period7-Pay, Current TRX7).

1. In the Toolbox window, drag the LEGEND28 field to the H1 section of the Report Layout. Position it to the right of the newly placed LEGEND27 field.
2. Drag the Current TRX7 field to the H2 section of the Report Layout. Position the Current TRX7 field to the right side of the Period6 - Vouch field.
3. In the Report Layout, double-click Current TRX7, click Invisible in the Visibility field, and then click OK.
4. In the Toolbox window, drag the Period7-Voucher field to the H2 section of the Report Layout. Position the Period7-Voucher field over the Current TRX7 field.
5. In the Report Layout, double-click Period7 - Vouch, click Hide When Empty in the Visibility field, and then click OK.
6. In the Toolbox window, drag the Period7 - Pay field to the B section of the Report Layout. Position the Period7 - Pay field to the right of the Period6 - Pay field.
7. In the Report Layout, double-click Period7 - Pay, click Hide When Empty in the Visibility field, and then click OK.
8. In the Toolbox window, drag the Current TRX7 field to the F1 section of the Report Layout. Position the Current TRX7 field to the right side of the Current TRX6 field.
9. In the Report Layout, double-click Current TRX7, click Sum in the Display Type box, and then click OK.
10. In the Toolbox window, drag the LEGEND28 field to the RF section of the Report Layout. Position the LEGEND28 field to the right side of the LEGEND27 field.
11. In the Report Layout, double-click LEGEND28, click Data in the Display Type field, and then click OK.
12. In the Toolbox window, drag the Current TRX7 field to the RF section of the Report Layout. Position the Current TRX7 field to the right side of the Current TRX6 field.
13. In the Report Layout, double-click Current TRX7, click Sum in the Display Type field, and then click **OK`.

By meticulously following these steps, you have now placed all the necessary fields for your three new aging buckets (Bucket 5, Bucket 6, and Bucket 7) into the appropriate sections of the Payables Management Aged Trial Balance report layout. Ensure that all fields are correctly aligned for optimal readability on the final report.

Step 5: Save the Changes to the Report, and Then Exit Report Writer¶

After completing all modifications to the report layout, it is crucial to save your changes to ensure they are retained. Neglecting to save will result in the loss of all your hard work. Saving the modified report effectively updates the Reports.dic file with your new design.

1. From the File menu within Report Writer, select the appropriate option: Microsoft Dynamics GP or Microsoft Business Solutions - Great Plains. This action typically initiates the saving process and prompts you to confirm.
2. When you are prompted to save the changes to the report layout, click Save. This action saves the visual layout changes you made in the Report Layout window.
3. You will likely be prompted a second time to save the changes to the modified report itself. Click Save again to confirm and finalize the modification to the “PM Aged TB - Detail” report within your Reports.dic file.

After saving, Report Writer will close, and you will return to the main Dynamics GP application. Your modified report is now stored and ready for use, but it still needs security permissions assigned before users can access it.

Step 6: Assign Security Permissions to the Modified Report¶

Even though the report has been modified and saved, it will not be accessible to users until appropriate security permissions are assigned. This critical step ensures that only authorized users or roles can view and print the customized version of the Aged Trial Balance report. Assigning security permissions involves directing Dynamics GP to use your modified report instead of the original system report.

To assign security permissions to the modified report, utilize the method appropriate for your version of Microsoft Dynamics GP. For modern versions, the Alternate/Modified Forms and Reports window is the primary method.

Method 1: By Using Security in Microsoft Dynamics GP 10.0 and Later¶

This method is the recommended approach for assigning security in Microsoft Dynamics GP 10.0 and subsequent versions, offering a flexible way to manage access to modified forms and reports.

1. From the Microsoft Dynamics GP menu, point to Tools, then Setup, then System, and then click Alternate/Modified Forms and Reports.
2. In the ID box, type or select the user ID or the alternate/modified forms and reports ID that will be granted access to print this modified report. This ID could represent a specific user or a security role.
3. In the Product list, select Microsoft Dynamics GP. This filters the list to show only items related to the core GP application.
4. In the Type list, select Reports. This further narrows down the options to display only report modifications.
5. Expand the Purchasing folder. The Payables Management Aged Trial Balance report falls under the Purchasing series.
6. Expand the folder for the specific report you modified, which will be Aged Trial Balance (Detail).
7. Within this expanded folder, you will see multiple options for the report. Click to select Microsoft Dynamics GP (Modified). This directs the system to use your customized report instead of the original.
8. Click Save to apply these alternate/modified forms and reports settings.

After configuring the alternate/modified forms and reports, you must assign this configuration to the relevant users through User Security.

1. On the Microsoft Dynamics GP menu, point to Tools, then Setup, then System, and then click User Security.
2. In the User list, select the specific user ID who needs access to this modified report.
3. In the Company list, select the company for which this user should have access to the modified report.
4. In the Alternate/Modified Forms and Reports ID list, select the ID that you configured in step 2 of this method. This links the user to the set of modified forms and reports you just defined.

By following these steps, the selected user will now have the necessary permissions to access and run the Payables Management Aged Trial Balance report with the newly added aging buckets.

Method 3: By Using the Standard Security Tool in a Version Prior to Microsoft Dynamics GP 10.0¶

While less commonly used for modern versions, this method was prevalent in earlier versions of Dynamics GP (prior to GP 10.0) for assigning direct security to modified reports.

1. On the Tools menu, point to Setup, then System, and then click Security.
2. If prompted, type the system password in the Please Enter Password box, and then click OK.
3. In the User ID list, click the ID of the user who you want to have access to the modified report.
4. In the Type list, click Modified Reports.
5. In the Series list, click Purchasing.
6. In the Access List box, locate the report you modified (e.g., “PM Aged TB - Detail”). Double-click the report name, and then click OK. An asterisk (*) appearing next to the report name indicates that access has been granted.

This concludes the process of modifying the Payables Management Aged Trial Balance report to include additional aging buckets and assigning the necessary security permissions. This enhanced report will provide more detailed insights into your outstanding payables, empowering better financial management decisions.

What other Dynamics GP report customizations would you like to explore? Share your thoughts and challenges in the comments below!