Streamline Dynamics GP: Printing & Editing General Posting Lists with Distribution Accounts

Table of Contents

This guide provides detailed instructions on utilizing Report Writer within Microsoft Dynamics GP 9.0 and Microsoft Business Solutions - Great Plains 8.0. The objective is to modify the standard General Posting Edit List report. By following these steps, you can include crucial distribution accounts associated with variable and fixed allocation accounts, providing a more comprehensive view of your financial transactions and allocations. This modification enhances the report’s utility for financial analysis and auditing.

The process involves adding necessary tables to the report definition, establishing appropriate sorting criteria, and creating calculated and conditional fields within Report Writer. These new fields are designed to capture and display the specific distribution account information linked to allocation accounts. Finally, these elements are incorporated into the report layout, and security permissions are configured to allow users access to the modified report. This ensures that the enhanced reporting capabilities are available to the relevant personnel.

Step 1 - Back Up the Report¶

Before making any modifications to reports in Microsoft Dynamics GP, it is crucial to create a backup of your existing Reports.dic file. This file contains definitions for all modified reports. Backing up this file ensures that you can restore your reports to their previous state if any issues arise during the modification process or if the changes do not meet your requirements. It is a fundamental best practice for safeguarding your custom reporting assets.

To locate the Reports.dic file, begin by navigating within Microsoft Dynamics GP. Access the system setup options through the main menu. The path will guide you to the specific system configuration area where launch file settings are managed.

1. From the main menu, select the Tools menu.
2. Point to Setup, then point to System.
3. Finally, select Edit Launch File.
4. If prompted by the system, enter the system password to proceed into the Launch File configuration window.
5. Within the Edit Launch File window, identify your current Microsoft Dynamics GP installation. If you are operating Microsoft Dynamics GP 9.0, select Microsoft Dynamics GP. If you are using Microsoft Business Solutions - Great Plains 8.0, select Great Plains.
6. Observe the path displayed in the Reports box. This path indicates the location of your Reports.dic file.
7. Select OK to close the Edit Launch File window. Copy the Reports.dic file located at the noted path to a secure backup location.

Performing this backup ensures that your current report customizations are preserved before you begin the modification process outlined in the following steps.

Step 2 - Add Tables and Sort Definitions to the Report¶

The next step involves entering Report Writer and configuring the General Posting Edit List report definition. This requires adding the necessary tables that contain the allocation and account information and then defining the correct sorting order to present the data logically. Adding tables links the report to the underlying data structures in Dynamics GP.

1. On the Tools menu, point to Customize, and then select Report Writer.
2. In the Product list, select your specific version: Microsoft Dynamics GP for version 9.0 or Great Plains for version 8.0. Select OK to open the Report Writer main window.
3. Select the Reports button to open the Report Definition window.
4. In the Original Reports area, locate and select the General Posting Edit List. Select the Insert button to move it to the Modified Reports area. This action creates a modifiable copy of the standard report.
5. In the Modified Reports area, ensure General Posting Edit List is selected, and then select the Open button to open the Report Definition window for your modified report.
6. Within the Report Definition window, select the Tables button. This opens the Report Table Relationships window.
7. In the Report Table Relationships window, select the Allocation Amounts Temporary table. Select New to add a related table. Select Account Master from the list of tables, and then select OK. This links allocation data to account details.
8. Select the Account Master table you just added, and then select New. From the list of tables, select either Variable Allocation Master or Fixed Allocation Master, depending on which type of allocation accounts you need to report on (or add both if necessary for a comprehensive report). Select OK. This links the account details to the specific allocation method masters.
9. Select Close to exit the Report Table Relationships window.

After adding the tables, you need to define the sorting criteria for the report. Correct sorting ensures that the data is grouped and ordered in a meaningful way, such as by batch, journal entry, or sequence.

10. In the Report Definition window, select the Sort button to open the Sorting Definition window.

11. In the Report Table list, select Posting Definitions Master. In the Table Fields list, select Batch Number, and then select Insert. This sets the primary sort order by batch.

    Note: To ensure subsequent sort definitions are nested correctly, always select the most recently added sort definition in the Sort By list before inserting the next one.

12. In the Report Table list, select Transaction Work. In the Table Fields list, select Journal Entry, and then select Insert. This adds a secondary sort by journal entry within each batch.

13. In the Report Table list, select Allocation Amounts Temporary. In the Table Fields list, select Sequence Line, and then select Insert. This sorts by the sequence line within the allocation details for each journal entry.
14. In the Report Table list, select Account Master. In the Table Fields list, select Account Index, and then select Insert. This sorts by the main account index involved in the allocation.
15. In the Report Table list, select either Variable Allocation Master or Fixed Allocation Master (corresponding to the table(s) you added earlier). In the Table Fields list, select Distribution Account Index, and then select Insert. This crucial step ensures that the report sorts by the distribution account within each allocation detail line, allowing you to see the breakdown.
16. (Optional but Recommended): You might want to add the Batch Number sort again at the end if the report structure requires it, though typically sorting by Batch, Journal, and then allocation details is sufficient. The provided steps suggest adding Batch Number again; follow step 11 again if needed after adding all other sorts: In the Report Table list, select Posting Definitions Master, select Batch Number in the Table Fields list, and then select Insert. Confirm the order in the Sort By list appears as Batch, Journal Entry, Sequence Line, Account Index, Distribution Account Index, and potentially Batch again.
17. Select OK to close the Sorting Definition window and save the sort order definitions.

Successfully completing this step lays the foundation for your modified report by establishing the data sources and the order in which the data will be presented.

Step 3 - Create a Calculated Field and a Conditional Field¶

Report Writer allows the creation of calculated and conditional fields to manipulate data or control report behavior. In this step, you will create fields that help manage the display of account indices, specifically to control report section headers and visibility later on.

1. In the Report Definition window, select the Layout button to open the Report Layout window.
2. In the Toolbox window within the Report Layout, select Calculated Fields in the resource list. Then select New to open the Calculated Field Definition window.
3. In the Name field, type Account Index Blank. This field will be used to suppress headers when there is no relevant account index.
4. In the Result Type list, select Integer.
5. In the Expression Type area, select Calculated.
6. Select the Constants tab. In the Type list, select Integer. Ensure the Constant value is 0 (zero). Select Add, and then select OK to save the calculated field. This simple field will always return 0.

Now, create a conditional field that will help determine whether a distribution account should be displayed based on the main account type (fixed or variable allocation).

7. In the Toolbox window, with Calculated Fields still selected in the resource list, select New again.
8. In the Name field, type Distribution Account. This field will act as a flag.
9. In the Result Type list, select Integer.
10. In the Expression Type area, select Conditional. This means the field’s value will depend on a condition.
11. Select the Fields tab. In the Resources list, select Account Master.
12. In the Field list, select Fixed or Variable, and then select Add. This brings the account type field into the expression.
13. In the Operators area, select the equals sign (=).
14. Select the Constants tab. In the Result Type list, select Integer. Ensure the Constant value is 0 (zero). Select Add. This completes the condition: GL_Account_MSTR.Fixed or Variable = 0. Account Master records with Fixed or Variable = 0 typically represent non-allocation accounts.
15. Select the True Case box. Select the Constants tab, select Integer in the Type list, ensure the Constant value is 0 (zero), and then select Add. If the condition is true (the account is not a fixed or variable allocation account), the field Distribution Account will be 0.

16. Select the False Case box. Select the Constants tab, select Integer in the Type list. In the Constant field, type 1, and then select Add. If the condition is false (the account is a fixed or variable allocation account), the field Distribution Account will be 1. This 1 will be used later to trigger the display of distribution account details.

    The resulting expression should look like this:
    Conditional: GL_Account_MSTR.Fixed or Variable = 0
    True Case: 0
    False Case: 1

17. Select OK to close the Calculated Field Definition window and save the Distribution Account conditional field.

These calculated fields are essential building blocks used in later steps to control the visibility of sections on the report layout and ensure that distribution account details only appear for relevant allocation accounts.

Step 4 - Create a Calculated Field for the Distribution Account Number¶

To display the actual distribution account number on the report, you need to create a calculated field that calls a built-in Dexterity function (User-Defined Function) provided by the financial series in Dynamics GP. This function takes an account index and returns the formatted account number string.

1. In the Toolbox window, select Calculated Fields in the resource list, and then select New.
2. In the Name field, type Get Dist Account Num. This name clearly indicates the field’s purpose.
3. In the Result Type list, select String. Account numbers are typically treated as strings for display purposes in reports.
4. In the Expression Type area, select Calculated.
5. Select the Functions tab, and then select User-Defined in the Function Type area.
6. In the Core list, select Financial. This filters the available functions to those relevant to the financial modules.
7. In the Function list, select RW_AccountNumber. This is the specific function designed to format an account index into a readable account number string.
8. Select Add. This places the function template into the expression box.
9. Select the Fields tab.
10. In the Resources list, select either Variable Allocation Master or Fixed Allocation Master, depending on the allocation type you are reporting on (select the one containing the distribution account index you need).
11. In the Field list, select Distribution Account Index. This is the numerical identifier for the distribution account linked to the allocation.

12. Select Add. This inserts the field into the function’s arguments.

    The resulting expression will call the function with the distribution account index from the relevant allocation master table:
    Calculated: FUNCTION_SCRIPT(RW_AccountNumber GL_Allocation_Variable_MSTR.Distribution Account Index ) or
    Calculated: FUNCTION_SCRIPT(RW_AccountNumber GL_Allocation_Fixed_MSTR.Distribution Account Index )

13. Select OK to close the Calculated Field Definition window and save the Get Dist Account Num field.

This calculated field is essential for converting the internal account index stored in the database into a user-friendly account number format that can be displayed on the report layout.

Step 5 - Create Another Calculated Field for the Distribution Account Description¶

Similar to getting the account number, you’ll create another calculated field to retrieve the description for the distribution account. This also involves using a specific user-defined function from the financial series in Report Writer.

1. In the Toolbox window, select Calculated Fields in the resource list, and then select New.
2. In the Name field, type Get Dist Account Desc. This name describes the field’s function.
3. In the Result Type list, select String. Account descriptions are text-based.
4. In the Expression Type area, select Calculated.
5. Select the Functions tab, and then select User-Defined.
6. In the Core list, select Financial.
7. In the Function list, select RW_AccountDescription. This function takes an account index and returns the corresponding account description.
8. Select Add.
9. Select the Fields tab.
10. In the Resources list, select either Variable Allocation Master or Fixed Allocation Master, matching the table used in the previous step.
11. In the Field list, select Distribution Account Index.

12. Select Add.

    The resulting expression will call the function with the distribution account index:
    Calculated: FUNCTION_SCRIPT(RW_AccountDescription GL_Allocation_Variable_MSTR.Distribution Account Index ) or
    Calculated: FUNCTION_SCRIPT(RW_AccountDescription GL_Fixed_Variable_MSTR.Distribution Account Index )

13. Select OK to close the Calculated Field Definition window and save the Get Dist Account Desc field.

With these two calculated fields (Get Dist Account Num and Get Dist Account Desc), you now have the necessary data elements to display the distribution account information on your report layout.

Step 6 - Add Headers¶

Report Writer uses report sections, including headers, to organize and group data. You will create new headers linked to the account index and distribution account index. These headers, combined with the calculated fields from Step 3, will be used to control when the distribution account details are displayed, ensuring they only appear under the relevant allocation accounts.

1. In the Report Layout window, select Tools, and then select Section Options. This opens the Report Section Options window, where you manage report headers and footers.

2. You need to create a header for the main allocation account. Follow these steps:
   a. In the Report Section Options window, find an existing header that you can use as a starting point or simply select New if you prefer. The instruction mentions selecting “Currency ID” then “New”, which suggests placing a new header relative to Currency ID. Select Currency ID, and then select New.
   b. In the Header Name field, type Variable Account or Fixed Account. Choose a name that reflects the type of allocation accounts this header relates to.
   c. In the Report Table list, select Account Master.
   d. In the Field list, select Account Index. This links the header to the main account involved.
   e. Select the check box labeled Suppress When Field is Empty. This prevents the header from printing unnecessarily. Crucially, select Account Index Blank (the calculated field from Step 3) in the Calculated Field list associated with suppression. This uses the calculated field to control when this header (and potentially its associated section) is suppressed. Select OK.

3. Now, create a header specifically for the distribution account that will appear nested under the allocation account header. Follow these steps:
   a. In the Report Section Options window, select the header you just created (Variable Account or Fixed Account), and then select New. This will create a nested header.
   b. In the Header Name field, type Distribution Account.
   c. In the Report Table list, select either Variable Allocation Master or Fixed Allocation Master, depending on which table contains the distribution account index you are reporting on.
   d. In the Field list, select Distribution Account Index. This links this nested header to the specific distribution account index.
   e. Select the Suppress When Field is Empty check box. Select Distribution Account (the conditional field from Step 3) in the Calculated Field list for suppression. This ensures that the distribution account header and its details only appear when the main account is identified as a fixed or variable allocation account (where the Distribution Account conditional field returns 1). Select OK.
   f. Select OK again to close the Report Section Options window.

You should now see the new headers (e.g., H4 - Variable Account and H5 - Distribution Account) added to your report layout structure in the Report Layout window. These headers define new sections where you can place fields related to the allocation and distribution accounts.

Step 7 - Add the New Fields to the Report¶

With the calculated fields created and the new headers added, the next step is to place the calculated fields onto the appropriate sections of the report layout. You will place the fields that control suppression in their respective header sections and the fields that display the account number and description in the distribution account header section.

1. In the Toolbox window, select Calculated Field in the resource list. Find and select the Account Index Blank field.
2. Drag the Account Index Blank field from the Toolbox window to the H4 section (or the header section you created for the Variable/Fixed Account) in the report layout. The exact section number might vary based on your report’s existing headers, but it should be the header linked to the main Account Index.
3. Double-select the Account Index Blank field you just placed on the report layout. In the Field Options window, select Invisible in the Visibility list. This field’s purpose is to control suppression via the header options, not to be seen on the printed report. Select OK.
4. In the Toolbox window, ensure Calculated Field is selected. Find and select the Distribution Account field (the conditional field).
5. Drag the Distribution Account field from the Toolbox window to the H5 section (or the header section you created for the Distribution Account) in the report layout. This is the nested header.
6. Double-select the Distribution Account field on the report layout. In the Field Options window, select Invisible in the Visibility list. This field is also used for controlling section visibility, not for display. Select OK.
7. In the Toolbox window, ensure Calculated Field is selected. Find and select the Get Dist Account Num field.
8. Drag the Get Dist Account Num field from the Toolbox window to the H5-Distribution Account section (the nested distribution account header). Position it where you want the distribution account number to appear.
9. In the Toolbox window, ensure Calculated Field is selected. Find and select the Get Dist Account Desc field.
10. Drag the Get Dist Account Desc field from the Toolbox window to the H5-Distribution Account section. Position it next to or below the account number field, as desired for formatting. You can adjust the size and position of these fields to fit your report layout.

You have now placed all the necessary fields onto the report layout. The suppression settings on the header sections, linked to the invisible calculated fields, will ensure that the H5-Distribution Account section (containing the distribution account number and description) only prints when the main account in the H4 section is an allocation account.

Step 8 - Save the Modified Report¶

After completing the layout modifications, it is time to save your changes and exit Report Writer. Saving the report updates the Reports.dic file with your modifications.

1. Close the Report Layout window. When prompted to save your changes to the report layout, select Save.
2. In the Report Definition window, select OK to save the overall report definition changes (table relationships, sorting, calculated fields, and layout link).
3. To exit Report Writer, go to the File menu. If you are using Microsoft Dynamics GP 9.0, select Microsoft Dynamics GP. If you are using Microsoft Business Great Plains 8.0, select Microsoft Business Solutions - Great Plains.

Your modified General Posting Edit List report is now saved in the Reports.dic file. The next crucial step is to grant users access to this modified version of the report.

Step 9 - Assign Security Permissions to the Modified Report¶

Even though you have modified the report, users will continue to run the original version unless you grant them security permissions to access the modified one. This step directs Dynamics GP to use your customized report instead of the standard report for specific users or roles.

There are generally two methods to assign security in Microsoft Dynamics GP: using Advanced Security (available in newer versions or if specifically configured) or using the standard Microsoft Dynamics GP Security window. The instructions provided detail the standard security method.

Method 1 - Use Advanced Security¶

(This method is not detailed in the original text but is a common alternative in later GP versions or complex security setups. Consult your Dynamics GP documentation or administrator for details on using Advanced Security roles and tasks to assign access to modified reports.)

Method 2 - Use Microsoft Dynamics GP Security¶

This method involves assigning access to the modified report directly to individual user IDs.

1. On the Tools menu, point to Setup, point to System, and then select Security.
2. If prompted by the system, type the system password and select OK to access the Security setup window.
3. In the User ID list, select the specific user ID of the user who needs access to the modified General Posting Edit List report.
4. In the Type list, select Modified Reports. This filters the list to show only modified reports available in the system.
5. In the Series list, select Financial. This further filters the list to show modified reports within the Financial series.
6. In the Access List box, locate the General Posting Edit List report. Double-select it to grant access to the selected user. The report name will move to the list on the right (or an asterisk might appear next to it depending on the GP version/build).
7. Select OK to save the security changes for the selected user.

After completing these steps, the specified user(s) will now be able to run the General Posting Edit List report, and Dynamics GP will use the modified version from the Reports.dic file, including the distribution account details for allocation accounts. Test the report thoroughly to ensure it functions as expected and displays the information correctly.

Did you find this guide helpful in customizing your Dynamics GP reports? Share your experience or any challenges you encountered in the comments below!