Enhance Dynamics GP Reporting: Adding Custom Buckets to Your Balance-Detail Report

Table of Contents

Microsoft Dynamics GP offers robust reporting capabilities, allowing businesses to gain valuable insights from their financial data. Standard reports often provide a good starting point, but sometimes, businesses require more granular control over how data is presented. One common need is to customize reports by adding extra “buckets” to categorize information in a more meaningful way. This article will guide you through the process of enhancing your Balance-Detail report in Dynamics GP by incorporating custom buckets, specifically focusing on adding an additional aging period column. This customization will empower you to analyze your financial data with greater precision and clarity.

Adding a Custom Bucket to Your Balance-Detail Report¶

This guide focuses on adding a new bucket to your Balance-Detail report, which is particularly useful for aging analysis. By adding an extra column, you can further break down your data to gain a deeper understanding of outstanding balances across different timeframes. The steps outlined below detail how to modify the report layout to include this additional bucket.

Step 1: Incorporating the Legend 19 Field into the H1 Section¶

The Legend fields in Dynamics GP reports are crucial for defining the labels or descriptions for your custom buckets. In this step, we will add the Legend 19 field to the H1 section of your Report Layout. The H1 section typically represents the report header that appears at the top of each page.

1. Access the Report Layout Window: Begin by opening the Report Writer within Dynamics GP and navigating to the Report Layout for your Balance-Detail report. This usually involves going to Reports > Report Writer. Once in Report Writer, locate and open the specific Balance-Detail report you wish to modify.
2. Open the Toolbox Window: Within the Report Layout window, ensure the Toolbox window is visible. If it’s not, you can usually open it from the Tools menu or a similar option within the Report Writer interface. The Toolbox is your central resource for adding and manipulating fields in your report layout.
3. Locate the Calculated Fields List: In the Toolbox window, you will find various lists of fields. Look for the Calculated Fields list. This list contains fields that perform calculations or hold specific values derived from your data.
4. Find the Legend 19 Field: Scroll through the Calculated Fields list until you locate the Legend 19 field. These Legend fields are often listed numerically.
5. Drag and Drop to the H1 Section: Once you’ve found Legend 19, click on it and drag it from the Toolbox window directly into the H1 section of your Report Layout.
6. Position the Field: Position the Legend 19 field to the right of the Legend 18 field within the H1 section. This placement ensures a logical flow in your report header, with the new custom bucket label appearing alongside existing labels.

By adding the Legend 19 field to the H1 section, you are setting the stage for displaying a custom label for your newly added bucket in the report header. This label will help users understand the meaning of the additional data column.

Step 2: Adding the End Amount-Aging Period Field to the H2 Section¶

The H2 section in the Report Layout typically represents the column headers that appear under the main header (H1). In this step, we will add the End Amount-Aging Period field to the H2 section to display the actual aging amounts for our new custom bucket.

1. Select the Data Source in the Toolbox: In the Toolbox window, navigate to the Layout tab. Here, you will see a list of tables and temporary files that are available as data sources for your report. Select PM Aged Trial Balance Document Temporary File from this list. This temporary file contains the data related to aged balances for Payables Management.
2. Locate the End Amount-Aging Period Field: After selecting the data source, the Toolbox window will display the fields available within the PM Aged Trial Balance Document Temporary File. Find the field named End Amount-Aging Period. This field holds the ending balance amounts for different aging periods.
3. Drag and Drop to the H2 Section: Drag the End Amount-Aging Period field from the Toolbox window into the H2 section of the Report Layout. This will place the field as a column header in your report.
4. Access the Report Field Options: In the Report Layout window, double-click on the newly added End Amount-Aging Period field in the H2 section. This action will open the Report Field Option window, allowing you to configure the field’s properties.
5. Set the Array Index to 5: In the Report Field Option window, locate the Array Index field. Set the value of the Array Index to 5. The Array Index specifies which aging period bucket the field should represent. An index of 5 typically corresponds to an additional, custom aging bucket beyond the standard aging periods.
6. Set Visibility to Hide When Empty: In the Report Field Option window, find the Visibility field. Select Hide When Empty from the dropdown options. This setting ensures that the column for this custom bucket will only appear in the report if there is data to display for that aging period, preventing empty columns in your report output.
7. Configure the Format Field: Click on the Format Field field repeatedly until Calculated Fields appears. This setting determines how the field will be formatted. By selecting “Calculated Fields,” we are instructing the field to use a calculated formatting function.
8. Select Func/Rept Index for Format Field: In the box under the Format Field field, select Func/Rept Index. This function is likely a pre-defined calculated field that handles the formatting for report indices, ensuring the amounts are displayed correctly based on the aging period index.
9. Confirm Changes: Click OK in the Report Field Option window to save the changes you have made to the End Amount-Aging Period field.

By completing these steps, you have successfully added a column header in the H2 section that will display the aging amounts for your custom bucket. The Array Index of 5 is crucial as it links this column to the specific custom aging period you are defining.

Step 3: Adding the Applied Amount - Aging Periods Field to the B Section¶

The B section in the Report Layout is typically the body section where the detailed data rows of the report are displayed. In this step, we will add the Applied Amount - Aging Periods field to the B section to show the transaction amounts that fall into our custom aging bucket for each vendor or customer.

1. Select the Data Source in the Toolbox: In the Toolbox window, on the Layout tab, select the PM Aged Trial Balance Apply To TEMP table from the list of data sources. This temporary table holds the detail-level data related to applied amounts and aging periods for Payables Management transactions.
2. Locate the Applied Amount - Aging Periods Field: Once you’ve selected the data source, find the Applied Amount - Aging Periods field in the list of fields. This field contains the applied amounts broken down by aging period.
3. Drag and Drop to the B Section: Drag the Applied Amount - Aging Periods field from the Toolbox window into the B section of the Report Layout. This will add the field as a data column in the report body.
4. Access the Report Field Options: In the Report Layout window, double-click on the Applied Amount - Aging Periods field you just added in the B section. The Report Field Option window will appear.
5. Set the Array Index to 5: In the Report Field Option window, set the Array Index value to 5. Similar to Step 2, this links this data column to the custom aging bucket (index 5).
6. Configure Visibility: In the Report Field Option window, select Hide When Empty from the dropdown for the Visibility field. This ensures that detail rows will not display empty values in the custom bucket column if there are no transactions for that aging period.
7. Configure the Format Field: Click on the Format Field field repeatedly until Calculated Fields is displayed.
8. Select Func/Rept Index for Format Field: In the box under the Format Field field, select Func/Rept Index. This applies the appropriate formatting to the amount values based on the aging period index.
9. Confirm Changes: Click OK to close the Report Field Option window and save the settings for the Applied Amount - Aging Periods field.

By adding the Applied Amount - Aging Periods field to the B section with the correct Array Index, you are now displaying the transaction-level data for your custom aging bucket in the report body. This allows you to see which transactions contribute to the balances in this new bucket.

Step 4: Adding the Vendor Totals5 Field to the F1 Section¶

The F1 section in the Report Layout is typically used for displaying group totals, such as vendor totals in a Payables report. In this step, we will add the Vendor Totals5 field to the F1 section to display the total balance for each vendor within our custom aging bucket.

1. Locate the Vendor Totals5 Field in Calculated Fields: In the Toolbox window, navigate back to the Calculated Fields list. Find the Vendor Totals5 field. These “Totals” calculated fields are designed to aggregate data at different levels in the report.
2. Drag and Drop to the F1 Section: Drag the Vendor Totals5 field from the Toolbox window into the F1 section of the Report Layout.
3. Access the Report Field Options: Double-click on the Vendor Totals5 field in the F1 section to open the Report Field Option window.
4. Set Display Type to Data: In the Report Field Option window, locate the Display Type field. Select Data from the dropdown. This ensures that the field displays the actual total amount as data, rather than as a label or other type of information.
5. Configure the Format Field: Click on the Format Field field repeatedly until Calculated Fields appears.
6. Select Func/Rept Index for Format Field: In the box under the Format Field field, select Func/Rept Index. This applies the correct formatting to the total amount value, ensuring it is displayed appropriately.
7. Confirm Changes: Click OK to close the Report Field Option window and save the settings for the Vendor Totals5 field.

By adding Vendor Totals5 to the F1 section, you are now displaying the total amount for each vendor that falls within your custom aging bucket. This provides a summary view of vendor balances in this new aging category.

Step 5: Adding the Legend 19 Field to the RF Section¶

The RF section in the Report Layout usually represents the report footer, appearing at the very bottom of the report. In this final step, we will add the Legend 19 field to the RF section. This is likely for consistency and to ensure the custom bucket label is displayed even in the report footer, if needed, or for summary pages.

1. Locate the Legend 19 Field in Calculated Fields: In the Toolbox window, go back to the Calculated Fields list and find the Legend 19 field, just as you did in Step 1.
2. Drag and Drop to the RF Section: Drag the Legend 19 field from the Toolbox window into the RF section of the Report Layout.
3. Access the Report Field Options: Double-click on the Legend 19 field in the RF section to open the Report Field Option window.
4. Set Display Type to Data: In the Report Field Option window, set the Display Type to Data. This ensures the field displays the actual legend text.
5. Configure the Format Field: Click on the Format Field field repeatedly until Calculated Fields appears.
6. Select Func/Rept Index for Format Field: In the box under the Format Field field, select Func/Rept Index. This ensures consistent formatting for the legend label.
7. Confirm Changes: Click OK to close the Report Field Option window and save the settings for the Legend 19 field in the RF section.

By adding Legend 19 to the RF section, you have completed the process of incorporating the custom bucket label throughout the report layout, including the footer.

Conclusion¶

By following these five steps, you have successfully enhanced your Dynamics GP Balance-Detail report to include a custom aging bucket. This customization provides you with a more detailed and insightful view of your financial data, allowing for better analysis and decision-making. Remember to save your changes to the report layout and test the modified report to ensure it meets your specific requirements. Customizing reports in Dynamics GP empowers you to tailor your reporting to the unique needs of your business, unlocking the full potential of your financial data.

Do you have any questions about customizing Dynamics GP reports or want to share your own reporting tips? Leave a comment below!