Mastering Date Formatting: Print Check Dates as MMDDYYYY in Dynamics GP Payables Reports

Table of Contents

This article provides a comprehensive guide on how to customize the date format for Check Date fields in Payables Management reports within Microsoft Dynamics GP. Specifically, it focuses on displaying dates in the Canadian format of MMDDYYYY. While the example used throughout this guide is the PM Check Register report, the techniques outlined are broadly applicable to various check-related reports and can be adapted for checks as well. This customization enhances report readability and ensures date consistency according to specific regional or organizational needs.

Step A - Back up the report, and then open the report¶

Before making any modifications to reports in Microsoft Dynamics GP, it is crucial to create a backup. This precautionary step safeguards your existing report configurations and allows for easy restoration in case of unintended changes or errors during the customization process. Backing up the Reports.dic file, which stores modified report layouts, is essential to prevent data loss or disruption to your reporting environment.

Backing up the Reports.dic file¶

1. Locate the Reports.dic file: The location of the Reports.dic file can be found through the Edit Launch File window within Microsoft Dynamics GP.

   • For Microsoft Dynamics GP 10.0: Navigate to Tools on the Microsoft Dynamics GP menu, then Setup, then System, and finally select Edit Launch File.
   • For Microsoft Dynamics GP 9.0: Go to Setup on the Tools menu, then System, and select Edit Launch File.

   

2. System Password: If prompted, enter the system password to proceed. This ensures that only authorized users can access and modify system settings.

3. Identify Reports.dic Path: In the Edit Launch File window, select Microsoft Dynamics GP. The full path to the Reports.dic file will be displayed in the Reports box. Note this path down for easy access to the backup file.

Once you have located the Reports.dic file path, manually copy this file to a safe backup location. This could be a different folder on your computer, a network drive, or an external storage device. This ensures that you have a secure copy of your original reports before proceeding with any modifications.

Open Report Writer¶

With the backup complete, the next step is to open Report Writer, the tool used to customize reports within Dynamics GP.

1. Access Report Writer: The method to open Report Writer varies slightly depending on your Dynamics GP version.

   • For Microsoft Dynamics GP 10.0: Point to Tools on the Microsoft Dynamics GP menu, then Customize, and select Report Writer.
   • For Microsoft Dynamics GP 9.0: Point to Customize on the Tools menu, and select Report Writer.

   

2. Select Product: In the Report Writer window, from the Product list, choose Microsoft Dynamics GP, and then click OK. This sets the context for report modifications within the Dynamics GP environment.

3. Open Reports Window: Within Report Writer, select Reports from the menu bar to open the Reports window, which lists all available reports.
4. Insert PM Check Register Report: In the Original Reports column, find and select the PM Check Register report. Click Insert to move this report to the Modified Reports list. This action creates a copy of the original report that you can customize without altering the original.
5. Open Modified Report: In the Modified Reports list, select the PM Check Register report again, and then click Open. This opens the Report Definition window for the modified report, allowing you to make changes to its structure and layout.
6. Access Layout: In the Report Definition window, click Layout. This action opens the Report Layout window, where you can visually modify the report’s design, including fields, formatting, and sections.

Step B - Create the first calculated field¶

Calculated fields in Report Writer allow you to create new data fields based on existing data or formulas. In this case, we will create a calculated field to format the Check Date into the desired MMDDYYYY format.

1. Open Calculated Fields Definition: In the Toolbox window, locate and select Calculated Fields in the resource list. Then, click New to open the Calculated Field Definition window.

   

2. Define Calculated Field Properties: In the Calculated Field Definition window, specify the following settings:

   • Name: Enter TEST FORMAT as the name for this calculated field. Choose a descriptive name that reflects the field’s purpose.
   • Result Type: Select String from the dropdown list. Since we are formatting the date into a text representation, the result type should be String.
   • Expression Type: Ensure Calculated is selected. This indicates that the field’s value will be determined by a formula or function.
3. Select User-Defined Functions: Navigate to the Functions tab within the Calculated Field Definition window and select User-Defined. User-defined functions provide access to built-in Dynamics GP functions that can be used in calculated fields.
4. Choose System Core and RW_DateToString Function: In the Core list, select System. Then, in the Function list, find and select RW_DateToString. This function is specifically designed to convert a date value into a string with a specified format.
5. Add Function to Expression: Click Add to insert the RW_DateToString function into the calculated field’s expression. The expression window will now contain FUNCTION_SCRIPT(RW_DateToString.
6. Select Check Date Field: Go to the Fields tab. In the Resource list, choose Check Register Temp, and in the Field list, select Check Date. This specifies the date field that we want to format.
7. Add Check Date to Expression: Click Add to include the Check Date field as the first parameter of the RW_DateToString function. The expression should now look like FUNCTION_SCRIPT(RW_DateToStringPM_Check_Register_TEMP.Check Date.
8. Add Date Format Constant: Go to the Constants tab. In the Type list, select String. In the Constants field, type MM DD YYYY. This string defines the desired date format: MonthMonth DayDay YearYearYearYear.
   > Note: The spaces between MM, DD, and YYYY are important as they will be included in the output string as separators.
9. Add Format Constant to Expression: Click Add to insert the format string as the second parameter of the RW_DateToString function. The complete equation for the first calculated field should now be:
   

   FUNCTION_SCRIPT(RW_DateToStringPM_Check_Register_TEMP.Check Date"MM DD YYYY")
   

10. Save Calculated Field: Click OK to save the TEST FORMAT calculated field. This field now contains the date value from the Check Date field, formatted as MMDDYYYY string.

Step C - Create other calculated fields that reference the first calculated field from step B¶

To display each component of the formatted date (Month, Day, Year) separately in the report layout, we need to create additional calculated fields that extract individual characters from the TEST FORMAT string. We will use the RW_Substring function to achieve this.

1. Open Calculated Fields Definition (Again): In the Toolbox window, select Calculated Fields and click New to create another calculated field.
2. Define Calculated Field Properties for A1: In the Calculated Field Definition window, specify the following settings:
   • Name: Enter A1. This will be the first of a series of calculated fields (A1 to A8).
   • Result Type: Select String. The substring function will return a string value.
   • Expression Type: Ensure Calculated is selected.
3. Add STRIP Function: Go to the Functions tab, select System-Defined, and choose STRIP from the Function list. Click Add. The STRIP function removes leading and trailing spaces, ensuring clean output.
4. Add RW_Substring Function: Go back to the Functions tab, select User-Defined, choose System in the Core list, and then select RW_Substring in the Function list. Click Add. The RW_Substring function extracts a portion of a string based on starting position and length.
5. Reference TEST FORMAT Field: Go to the Fields tab, select Calculated Fields in the Resources list, and choose TEST FORMAT in the Field list. Click Add. This uses the formatted date string from the TEST FORMAT field as the input for the substring function.
6. Add Starting Position Constant (1): Go to the Constants tab, select Integer in the Type list, and type 1 in the Constant field. Click Add. This sets the starting position for the substring to the first character of the TEST FORMAT string.
7. Add Length Constant (1): Type 1 again in the Constant field (ensuring Integer is still selected) and click Add again. This specifies that we want to extract a substring of length 1, meaning a single character.
8. Save Calculated Field A1: Click OK to save the calculated field. The equation for A1 will be:
   

   STRIP(FUNCTION_SCRIPT(RW_SubstringTEST FORMAT 11 ))
   

   
   This field A1 will extract the first character of the formatted date string.

9. Repeat for A2 through A8: Repeat steps 1 through 7 to create calculated fields A2 through A8. The only change in each step is the starting position integer value in steps 6 and 7. The length will remain 1. Refer to the table below for the starting positions and the corresponding character extracted for each field, based on the MMDDYYYY format and spaces:

   Calculated Field	Starting Position	Extracted Character (Example: 12/10/2009)
   A1	1	Month - First Digit (1)
   A2	2	Month - Second Digit (2)
   A3	4	Day - First Digit (1)
   A4	5	Day - Second Digit (0)
   A5	7	Year - First Digit (2)
   A6	8	Year - Second Digit (0)
   A7	9	Year - Third Digit (0)
   A8	10	Year - Fourth Digit (9)

   The equations for A2 through A8 will be similar to A1, only differing in the starting position integer value:

   • A2: STRIP(FUNCTION_SCRIPT(RW_SubstringTEST FORMAT 21 ))
   • A3: STRIP(FUNCTION_SCRIPT(RW_SubstringTEST FORMAT 41 ))
   • A4: STRIP(FUNCTION_SCRIPT(RW_SubstringTEST FORMAT 51 ))
   • A5: STRIP(FUNCTION_SCRIPT(RW_SubstringTEST FORMAT 71 ))
   • A6: STRIP(FUNCTION_SCRIPT(RW_SubstringTEST FORMAT 81 ))
   • A7: STRIP(FUNCTION_SCRIPT(RW_SubstringTEST FORMAT 91 ))
   • A8: STRIP(FUNCTION_SCRIPT(RW_SubstringTEST FORMAT 101 ))

   These calculated fields, A1 through A8, now hold individual characters of the formatted date string, allowing for precise placement in the report layout.

Step D - Place the calculated fields in the report layout¶

Now that we have created the calculated fields to format and separate the date components, the next step is to place them in the report layout to replace the original Check Date field.

1. Delete Original Check Date Field: In the Report Layout window, navigate to the Body (B) section of the report. Select the existing Check Date field and press the Delete key to remove it. We will replace this with our formatted date components.
2. Expand Body Section (if needed): If necessary, expand the Body (B) section of the report to create enough space to accommodate the new calculated fields. You can drag the section borders to resize it.

3. Drag and Position Calculated Fields: From the Toolbox window, drag the calculated fields A1 through A8 into the Body (B) section of the report. Position these fields in the desired order (A1, A2, A3, A4, A5, A6, A7, A8) and spacing to represent the MMDDYYYY date format. Ensure there is appropriate spacing between month, day, and year components if desired, mimicking the “MM DD YYYY” format. Avoid overlapping the fields to ensure each character is displayed correctly.

   

By carefully placing the calculated fields A1-A8 in the layout, you reconstruct the date in the MMDDYYYY format using individual character fields. This level of control allows for highly customized date representations in your reports.

Step E - Save the report, and then exit Report Writer¶

After making the necessary layout changes, it’s important to save the modified report and exit Report Writer to apply the changes within Dynamics GP.

1. Close Report Layout: Close the Report Layout window. When prompted to save your changes, click Save. This saves the layout modifications you made to the report.
2. Close Report Definition: In the Report Definition window, click OK. This closes the report definition and saves any changes made at this level.
3. Exit Report Writer: Select File from the Report Writer menu, and then choose Microsoft Dynamics GP. This closes Report Writer and returns you to the main Dynamics GP application.

Step F - Grant access to the report¶

To make the modified report accessible to users within Dynamics GP, you need to grant appropriate security permissions. The method for granting access varies depending on your version of Dynamics GP and the security features you are using.

Method 1 - By using security in Microsoft Dynamics GP 10.0¶

1. Open Alternate/Modified Forms and Reports: On the Microsoft Dynamics GP menu, point to Tools, then Setup, then Security, and select Alternate/Modified Forms and Reports.
2. Specify User ID: In the ID box, type the User ID of the user who should have access to print this modified report. You can grant access to specific users or user classes.
3. Select Product and Type: In the Product list, choose Microsoft Dynamics GP. In the Type list, select Reports.
4. Navigate to Modified Report: Expand Payables Management. Find and expand the node for PM Check Register report that you modified.
5. Grant Access to Modified Report: Select Microsoft Dynamics GP (Modified). A check mark will appear next to it, indicating that access is granted to the modified version of the report.
   > Note: The “(Modified)” designation indicates the customized version of the report.
6. Save Security Changes: Click Save to apply the security settings. The specified user(s) will now be able to access and run the modified PM Check Register report with the MMDDYYYY date format.

Method 2 - By using Advanced Security in Microsoft Dynamics GP 9.0¶

1. Open Advanced Security: On the Tools menu, point to Setup, then System, and select Advanced Security. If prompted, enter the system password.
2. Change View: Select View, and then choose by Alternate, Modified and Custom. This view organizes security settings by object type and modification status.
3. Navigate to Modified Report: Expand the following nodes in order: Microsoft Dynamics GP, Reports, Payables Management.
4. Select Modified Report: Expand the node for the PM Check Register report.
5. Grant Access to Modified Report: Select Microsoft Dynamics GP (Modified).

6. Apply and Save Security Changes: Click Apply, and then click OK to save the security settings.

   Note: Advanced Security settings are user and company specific by default. Ensure you are applying settings for the correct user and company. You can select additional users and companies in the Company and User areas of the Advanced Security window if needed.

Method 3 - By using Microsoft Dynamics GP security in Microsoft Dynamics GP 9.0¶

This method refers to utilizing the standard security features within Microsoft Dynamics GP 9.0. The steps are generally similar to Method 1, but accessed through the older security interface. You would navigate to security settings, locate the report, and assign permissions to users or roles to access the modified version. Consult the Dynamics GP 9.0 documentation for detailed steps on using the standard security window for report access management if needed.

By following these steps, you have successfully modified the PM Check Register report to display check dates in the MMDDYYYY format and granted access to the modified report to your users. This tailored date format enhances report clarity and aligns with specific regional or business requirements.

Feel free to share your experiences or questions in the comments below! Have you formatted dates in Dynamics GP reports before? What challenges did you face?