Home accounting aged trial balance dynamics gp reporting

Mastering Aged Trial Balance Reporting: Print All Aging Periods in Dynamics GP 9.0

Updated: • 0 • min read
Table of Contents

In the world of financial management, a detailed understanding of accounts receivable is paramount for maintaining healthy cash flow and making informed business decisions. The Aged Trial Balance (ATB) report is a cornerstone for this analysis, providing a snapshot of outstanding invoices categorized by their age. While Microsoft Dynamics GP 9.0 offers robust reporting capabilities, the default RM Aged Trial Balance report typically displays only the first four aging periods, which can often be insufficient for comprehensive financial scrutiny. Organizations frequently require a more granular view to identify long-overdue accounts and implement effective collection strategies.

This guide provides a comprehensive, step-by-step approach to modify the RM Aged Trial Balance report in Microsoft Dynamics GP 9.0, or Microsoft Business Solutions - Great Plains 8.0, to display all seven available aging periods. By extending the report’s functionality, finance professionals can gain deeper insights into their customer balances, enhance their analytical capabilities, and improve overall accounts receivable management. This customization leverages the powerful Report Writer tool within Dynamics GP, allowing for tailored reports that meet specific business needs.

Mastering Aged Trial Balance Reporting: Print All Aging Periods in Dynamics GP 9.0

The Foundation: Understanding Dynamics GP Report Writer

Microsoft Dynamics GP’s Report Writer is an integrated tool designed to customize existing reports and create new ones. It allows users to modify the layout, fields, and calculations of reports to suit unique operational requirements without needing to write code from scratch. For this modification, we will be using Report Writer to adjust the visual layout and data presentation of the Aged Trial Balance report, specifically by adding fields that represent the additional aging buckets and their corresponding legends and totals. Understanding this tool is key to unlocking the full potential of your Dynamics GP reporting.

The process involves careful navigation through report sections, identifying relevant fields, and correctly placing them to ensure accurate data display. While it requires attention to detail, the Report Writer interface is designed to be intuitive for users familiar with Dynamics GP. This customization will demonstrate how to extend standard reporting functionalities, providing a powerful example of how businesses can adapt their systems to evolving analytical demands.

Step-by-Step Guide: Modifying the RM Aged Trial Balance Report

The following instructions will guide you through the precise steps required to modify your Aged Trial Balance report. It is crucial to follow each step carefully to ensure the successful implementation of all seven aging periods. This modification specifically targets either the RM Quick Detail Aged Trial Balance or RM Detail Aged Trial Balance report, depending on your preferred base report.

Step 1: Essential Preparation – Backing Up Your Reports Dictionary

Before undertaking any modifications within Report Writer, it is imperative to create a backup of your Reports.dic file. This file contains all modified Microsoft Dynamics GP reports, and a backup ensures that you can restore your previous report versions in case of any unforeseen issues during the modification process. Data integrity is a top priority, and a backup serves as a critical safety net for your customizations.

To locate your Reports.dic file, follow these steps:

  1. Navigate to the Tools menu within Dynamics GP.
  2. Point to Setup, then System, and click on Edit Launch File.
  3. If prompted for a password, enter the system password. This security measure prevents unauthorized changes to the system’s core configuration files.
  4. In the Edit Launch File window, select either Microsoft Dynamics GP (for Dynamics GP 9.0) or Great Plains (for Microsoft Business Solutions - Great Plains 8.0). This selection determines which application’s launch file settings you are viewing.
  5. Carefully note the path displayed in the Reports box. This path indicates the exact location of your Reports.dic file.
  6. Click OK to close the Edit Launch File window. After noting the path, proceed to manually back up the Reports.dic file from that location to a secure place.

Step 2: Accessing the Report for Modification

With your Reports.dic file safely backed up, the next step involves opening Report Writer and selecting the specific Aged Trial Balance report you intend to modify. This process brings the report into the Report Writer environment, allowing you to begin making changes to its layout and data fields.

To open the report within Report Writer:

  1. From the Tools menu in Dynamics GP, point to Customize, and then click Report Writer. This action launches the Report Writer application, providing the interface for report customization.
  2. In the Product list, select the appropriate product: Microsoft Dynamics GP (for Dynamics GP 9.0) or Great Plains (for Microsoft Business Solutions - Great Plains 8.0), and then click OK. This step ensures that you are working within the correct application’s report dictionary.
  3. Click Reports on the main Report Writer window. This opens the Report Definition window, where you can manage both original and modified reports.
  4. In the Original Reports area, locate and click on RM Quick Detail Aged Trial Balance. Then, click Insert to move a copy of this original report to the Modified Reports area. It is important to work with a modified copy to preserve the original report’s integrity.
    • Note: While this example uses the RM Quick Detail Aged Trial Balance report, you should select the report most relevant to your specific scenario if you typically use a different Aged Trial Balance report.
  5. In the Modified Reports area, click on the newly inserted RM Quick Detail Aged Trial Balance, and then click Open. This action opens the Report Definition window for your modified report, allowing you to access its layout.

Step 3: Expanding the Report Layout – Introducing New Aging Periods

This is the most critical and detailed step, involving the addition of new fields to the report layout to accommodate aging periods five, six, and seven. The Report Writer’s layout view is where you visually arrange elements and define how data is presented. We will be working with “Legends,” “Buckets,” and “Total Buckets” – these elements represent the headers, individual transaction amounts, and summary totals for each aging period, respectively.

Here’s how to create and place the necessary fields:

  1. Within the Report Definition window, click Layout. This opens the graphical Report Layout window, where you will perform most of your modifications.
  2. In the Toolbox window on the left, select Legends from the resource list. Then, click on Legend 5. Drag Legend 5 from the Toolbox and place it into the H1 (Header 1) area of the report. This will serve as the column header for the fifth aging period.
  3. Still in the Toolbox, click Calculated Fields in the resource list, and then click Bucket 5. Drag Bucket 5 to the H2 (Header 2) area. This field will display the individual transaction amounts that fall into the fifth aging period.
  4. Locate Apply Bucket 5 in the Toolbox window and drag it into the report body section. This field, when placed in the body, will show the applied amount for the fifth aging period for each individual transaction line.
  5. For each of the fields you just added (Legend 5, Bucket 5, Apply Bucket 5), double-click the field on the report layout. In the Field Options window that appears, click Hide When Empty in the Visibility list, and then click OK. This ensures that if there is no data for a specific aging period, the corresponding field will not print, maintaining a clean report appearance.
  6. In the Toolbox window, select Total Bucket Five from the resource list. Drag Total Bucket Five to the F1 (Footer 1) section and also to the RF (Report Footer) section of the report. These fields will summarize the totals for the fifth aging period at both the group level (F1) and the overall report level (RF).
  7. Double-click Total Bucket Five in the F1 section of the report layout. In the Field Options window, click Sum in the Display Type list, and then click OK. This configures the field to calculate and display the sum of all transactions within the fifth aging period for that specific group.
  8. Similarly, double-click Total Bucket Five in the RF section of the report. Click Sum in the Display Type list, and then click OK. This ensures the field calculates and displays the grand total for the fifth aging period across the entire report.
  9. To introduce the sixth and seventh aging periods, repeat steps 2 through 8 for the following fields:
    • Legend 6, Bucket 6, Apply Bucket 6, and Total Bucket Six.
    • Legend 7, Bucket 7, Apply Bucket 7, and Total Bucket Seven.
    • Ensure each “Legend” field is placed in H1, “Bucket” fields in H2, “Apply Bucket” fields in the Body, and “Total Bucket” fields in both F1 and RF, with their Display Type set to Sum.

After adding the bucket and total fields, you must create calculated fields for the legends of the new aging periods. These calculated fields ensure that the descriptive text for “Legend 5,” “Legend 6,” and “Legend 7” appears correctly in the report footers.

  1. In the Toolbox window, click Calculated Fields in the resource list, and then click New.
  2. In the Name field, type Legend 5. In the Result Type list, click String. In the Expression Type area, click Calculated.
  3. In the Resources list, click RW Legends. In the Field list, click Legend, and then click Add. When prompted, type 5 for the array index, and then click OK. This formula retrieves the predefined legend text for the fifth aging period.
  4. Click OK to close the Calculated Field Definition window. Then, drag the newly created Legend 5 calculated field from the Toolbox to both the RF section and the F1 section of the report. These fields will provide the textual description of the aging period in the report summaries.
  5. In both the RF section and the F1 section, double-click the Legend 5 field. In the Display Type list, click Data, and then click OK. This ensures the calculated legend text is displayed as raw data.
  6. Repeat steps 12 through 16 for the Legend 6 calculated field and for the Legend 7 calculated field, ensuring you set the array index to 6 and 7 respectively.
  • Note: After placing all fields, you may need to adjust the formatting within the report layout. This could involve resizing fields, aligning them, or adding visual separators like dashes to headers and footers to ensure the report is readable and aesthetically pleasing. Proper alignment is crucial for a professional-looking report.

Step 4: Finalizing Your Changes – Saving the Modified Report

Once all the required fields have been added and appropriately configured in the report layout, it’s time to save your modifications and exit Report Writer. This action commits your changes to the Reports.dic file, making the customized report available within Dynamics GP.

  1. Close the report layout window. When prompted to save your changes, click Save. This action saves all the field additions and layout adjustments you’ve made.
  2. In the Report Definition window, click OK. This confirms that you want to finalize the report definition.
  3. To exit Report Writer, use one of the following steps:
    • In Microsoft Dynamics GP 9.0, click Microsoft Dynamics GP on the File menu.
    • In Microsoft Business Solutions - Great Plains 8.0, click Microsoft Business Solutions - Great Plains on the File menu.

Step 5: Granting User Access to the Enhanced Report

After modifying and saving the report, users will not automatically see the changes. You must explicitly grant access to the modified report through Dynamics GP’s security settings. There are two primary methods for doing this: using Advanced Security or using the standard Microsoft Dynamics GP security. You only need to apply one of these methods.

Method 1: By Using Advanced Security

Advanced Security offers a more granular way to manage user access to reports, allowing for control over alternate, modified, and custom forms and reports.

  1. On the Tools menu, point to Setup, then System, and click Advanced Security. Type the system password if you are prompted.
  2. Click View, and then click by Alternate, Modified and Custom. This changes the view to show all customizable elements.
  3. Expand the relevant product:
    • In Microsoft Dynamics GP 9.0, expand Microsoft Dynamics GP.
    • In Microsoft Business Solutions - Great Plains 8.0, expand Great Plains.
  4. Expand the following nodes in sequence:
    • Reports
    • Sales
    • RM Quick Detail Aged Trial Balance or RM Detail Aged Trial Balance (depending on which report you modified).
  5. Under the report name, click the modified version:
    • In Microsoft Dynamics GP 9.0, click Microsoft Dynamics GP (Modified).
    • In Microsoft Business Solutions - Great Plains 8.0, click Great Plains (Modified). This action selects the customized report for the currently active user and company.
  6. Click Apply, and then click OK. This saves the security changes and grants access.
    • Note: By default, when you launch the Advanced Security tool, the current user and company are selected. You can, however, select additional users in the User area and additional companies in the Company area of the Advanced Security window to apply these changes broadly.

Method 2: By Using Microsoft Dynamics GP Security

For simpler security management or environments not using Advanced Security, the standard Dynamics GP Security window can be used.

  1. On the Tools menu, point to Setup, then System, and click Security. If prompted to type the system password, enter it.
  2. In the User ID list, select the user ID of the individual who needs to access the modified report.
  3. In the Type list, click Reports. This filters the available options to show only reports.
  4. In the Series list, click Sales. This further narrows down the report list to those within the Sales series.
  5. In the Access List box, double-click RM Quick Detail Aged Trial Balance or RM Detail Aged Trial Balance. An asterisk (*) will appear next to the report name once selected, indicating that it has been granted access. Click OK. This grants the specified user access to the modified report.

Step 6: Verifying the Outcome – Printing the Extended Report

With the report modified and access granted, the final step is to print the report and verify that all seven aging periods are now correctly displayed.

  1. Navigate to the relevant reporting section in Dynamics GP (e.g., Sales > Reports > Trial Balance > Aged Trial Balance).
  2. Print the RM Quick Detail Aged Trial Balance or RM Detail Aged Trial Balance report.
  3. Visually inspect the printed report to confirm that all seven aging periods, complete with their legends, individual bucket amounts, and totals, are now present and accurately populated with data.
  • Note: It’s important to remember that the legends you’ve added (Legend 5, 6, 7) will only print successfully when the report is generated and viewed within the Microsoft Dynamics GP application. If you attempt to print or preview the report directly from Report Writer, these legends may not display correctly. Always verify your modifications by printing the report through the standard Dynamics GP interface.

Visualizing the Workflow

To summarize the entire process of modifying, saving, and granting access to the RM Aged Trial Balance report, here is a simplified workflow diagram:

mermaid graph TD A[Start: Identify Need for 7 Aging Periods] --> B(Backup Reports.dic); B --> C(Open Report Writer in Dynamics GP); C --> D{Select RM Quick Detail Aged Trial Balance}; D --> E(Open Report Layout); E --> F(Add Legend 5, Bucket 5, Apply Bucket 5 Fields); F --> G(Add Total Bucket Five to F1 & RF Sections); G --> H(Repeat for Periods 6 & 7: Add all corresponding fields); H --> I(Create Calculated Fields for Legend 5, 6, 7); I --> J(Save Modified Report); J --> K{Grant User Access to Modified Report}; K --> L1[Method 1: Advanced Security] K --> L2[Method 2: Standard GP Security] L1 --> M(Apply Access); L2 --> M(Apply Access); M --> N(Print Report in Dynamics GP); N --> O[End: Verify All 7 Aging Periods Displayed];

Conclusion: Empowering Financial Analysis with Enhanced Reporting

By diligently following these steps, you have successfully transformed your default Aged Trial Balance report in Microsoft Dynamics GP 9.0 to display all seven critical aging periods. This enhancement moves beyond the standard four periods, providing a richer and more detailed perspective on your outstanding receivables. The ability to view aged balances with this extended granularity significantly empowers financial analysis, enabling more precise cash flow forecasting, more targeted collection efforts, and ultimately, better financial health for your organization.

Customizing reports in Dynamics GP is a powerful way to tailor your accounting system to the specific needs of your business. This modification is a prime example of how a relatively straightforward change in Report Writer can yield substantial improvements in financial visibility and operational decision-making. We invite you to share your experiences or questions regarding this modification in the comments below. What other report customizations have you found most valuable in your Dynamics GP environment? Your insights are valuable to the community!

Post a Comment