Mastering Date Formatting in Dynamics GP Reports: A Comprehensive Guide

Table of Contents

Reports are a critical component of any business management system, providing insights into operational data. In Microsoft Dynamics GP, reports generated using Report Writer often display dates in a standard short format determined by regional settings, typically MM/DD/YYYY or DD/MM/YYYY. While this default format is sufficient for many purposes, there are frequent requirements to display dates in alternative formats to meet specific business needs, improve readability, or comply with regional standards. This guide details how to leverage the RW_DateToString user-defined function within Dynamics GP Report Writer to gain complete control over date formatting in your reports.

The ability to customize date formats is essential for creating professional and user-friendly reports. Whether you need to display dates as “Month Day, Year” (e.g., April 12, 2007), “DD-MMM-YYYY” (e.g., 12-Apr-2007), or any other combination, the RW_DateToString function provides the flexibility required. By converting a standard date field into a string field formatted according to specified criteria, you can overcome the limitations of the default date display. This process involves modifying the report layout, creating a calculated field using the RW_DateToString function, and assigning the necessary security permissions to ensure users can access the modified report.

Understanding the Need for Custom Date Formats¶

Microsoft Dynamics GP’s Report Writer is a powerful tool for customizing existing reports or creating new ones. However, date fields pulled directly onto a report layout typically inherit formatting based on the system’s regional settings or the field’s intrinsic properties, which are often rigid. For international organizations, or even domestic ones with specific reporting standards, a fixed MM/DD/YYYY or DD/MM/YYYY format may not be suitable.

Consider scenarios where a report needs to be presented to external stakeholders who are accustomed to a different date standard (e.g., DD/MM/YYYY instead of MM/DD/YYYY). Or perhaps a report requires dates to be spelled out (e.g., “April 12, 2007”) for formal documentation like invoices or contracts. The default formatting options in Report Writer are limited in these cases. The RW_DateToString function provides a robust solution by allowing developers or experienced users to define a precise output string based on the components of a date. This transformation turns the date from a date/time data type into a string data type, applying the desired formatting during the conversion.

Step 1: Prepare and Open the Report for Modification¶

Modifying reports in Microsoft Dynamics GP should always begin with proper preparation, especially backing up the existing reports dictionary. The Reports.dic file contains all modified reports. If this file is lost or corrupted, your customizations will be lost. Therefore, safeguarding this file is paramount before making any changes.

1. Back Up the Reports.dic File:

   • Navigate to the Tools menu, point to Setup, then System, and click Edit Launch File.
   • If prompted, enter the system password and click OK. This screen shows the location of critical dictionary files.
   • Identify the relevant Dynamics GP instance (e.g., Microsoft Dynamics GP 9.0 or higher, or Great Plains 8.0/7.5). Click on the corresponding entry in the list.
   • Note the full file path displayed in the Reports box. This is the location of your Reports.dic file.
   • Click OK to close the Edit Launch File window.
   • Using Windows File Explorer, navigate to the noted path and create a copy of the Reports.dic file. Store this copy in a safe location, perhaps on a network drive or external media, along with the date of the backup.

2. Open Report Writer:

   • Return to the Tools menu, point to Customize, and click Report Writer.
   • In the Product list, select the appropriate Dynamics GP instance (Microsoft Dynamics GP or Great Plains).
   • Click OK to launch the Report Writer application.

3. Select and Open the Report:

   • Within the Report Writer window, click on the Reports button.
   • Locate the report you wish to modify in the Original Reports list.
   • Click Insert to move the original report into the Modified Reports list. This action creates a copy of the report within your Reports.dic file that you can safely modify.
   • In the Modified Reports list, select the report you just inserted.
   • Click Open. This opens the Report Definition window for the selected report.

4. Access the Report Layout:

   • In the Report Definition window, click the Layout button. This opens the Report Layout window, which is a visual designer where you can arrange fields, add text, and define the structure of your report.

You are now ready to begin the modification process by adding a calculated field that will handle the custom date formatting. The Report Layout window is where you will visually place this newly formatted date field onto the report sections (header, body, footer, etc.).

Step 2: Modify the Report Using a Calculated Field¶

The core of custom date formatting in Report Writer lies in creating a calculated field. This field will use the RW_DateToString function to take a standard date field as input and output a string formatted according to your specifications.

1. Access Calculated Fields:

   • In the Report Writer layout window, look for the Toolbox area.
   • From the dropdown list in the Toolbox, select Calculated Fields.

2. Create a New Calculated Field:

   • Click the New button within the Calculated Fields toolbox. This opens the Calculated Field Definition window.
   • In the Name field, type a descriptive name for your new field (e.g., “FormattedInvoiceDate”, “CustomReportRunDate”). Choose a name that clearly indicates the field’s purpose.
   • In the Result Type list, select String. This is crucial because the output of the RW_DateToString function is always a text string, regardless of the input date.
   • Ensure that the Expression Type area has Calculated selected.

3. Define the Calculation - Using RW_DateToString:

   • Click on the Functions tab.
   • Under the Type area, click User-Defined. RW_DateToString is a built-in user-defined function provided by the Dynamics GP system.
   • In the Core list, click System.
   • In the Function list, find and click on RW_DateToString.
   • Click the Add button. This adds the function signature FUNCTION_SCRIPT(RW_DateToString ) to the Expression box.

4. Add the Date Field Parameter:

   • Click on the Fields tab.
   • In the Resources list, select the table that contains the date field you want to format (e.g., Sales Transaction History, Payables Transaction). You may need to refer to the report’s tables if you are unsure which table contains the date field.
   • In the Field list, find and select the specific date field (e.g., Invoice Date, Document Date, GL Posting Date).
   • Click the Add button. The selected field will be added as the first parameter within the RW_DateToString function call in the Expression box, looking something like FUNCTION_SCRIPT(RW_DateToString SOP_HDR_HIST.Invoice Date ).

5. Add the Format String Parameter:

   • Click on the Constants tab.
   • In the Type list, click String. This is because the second parameter for RW_DateToString is a text string that defines the desired output format.
   • In the Constant field, enter the format string. This string uses special codes that RW_DateToString interprets to build the output date string. For example, to format a date as “Day-MonthAbbreviation-FullYear”, you would enter “DD-mmm-YYYY”. (Refer to the “Options for the RW_DateToString User-Defined Function” section below for a list of format codes).
   • Click the Add button. The format string constant will be added as the second parameter within the function call in the Expression box. The complete expression will now look like FUNCTION_SCRIPT(RW_DateToString SOP_HDR_HIST.Invoice Date "DD-mmm-YYYY").

6. Finalize the Calculated Field:

   • Carefully review the expression in the Expression box to ensure it matches the required structure: FUNCTION_SCRIPT(RW_DateToString [Date Field] "[Format String]").
   • Click OK to save the calculated field definition. The new calculated field will appear in the Calculated Fields list in the Toolbox.

7. Place the Calculated Field on the Layout:

   • In the Report Layout window, locate the area where you want the formatted date to appear (e.g., Report Header, Page Header, Body, Footer).
   • From the Calculated Fields list in the Toolbox, click and drag your newly created calculated field onto the desired section of the report layout.
   • Adjust the position and size of the field box as needed.

8. Save and Exit Report Writer:

   • Close the Report Layout window. When prompted to save changes, click Save.
   • Close the Report Definition window. Again, click Save when prompted.
   • Exit Report Writer. Depending on your version:
     • In Microsoft Dynamics GP 9.0 and higher, click Microsoft Dynamics GP on the File menu.
     • In Microsoft Business Solutions - Great Plains 8.0 or 7.5, click Microsoft Business Solutions - Great Plains on the File menu.

Your report has now been modified to include the custom-formatted date field. The next crucial step is to grant users permission to view and use this modified report.

Step 3: Assign Security Permissions to the Modified Report¶

After modifying a report in Report Writer, users will not see the changes until security permissions are configured to point to the modified version instead of the original one. Microsoft Dynamics GP offers two primary methods for managing security: Advanced Security and Standard Security.

Method 1: Use Advanced Security¶

Advanced Security provides a more granular and flexible way to manage access to modified windows, reports, and other resources based on user roles and tasks.

1. Access Advanced Security:

   • On the Tools menu, point to Setup, then System, and click Advanced Security.
   • If prompted, enter the system password and click OK.

2. Set the View:

   • In the Advanced Security window, click View.
   • Select By Alternate, Modified and Custom. This view organizes resources by their origin (original, alternate, modified, custom).

3. Locate the Modified Report:

   • In the left-hand pane, expand the product corresponding to your Dynamics GP version (Microsoft Dynamics GP or Great Plains).
   • Expand Reports.
   • Expand the Series under which your report falls (e.g., Sales, Purchasing, Financial).
   • Expand the name of the specific report you modified.

4. Select the Modified Version:

   • Beneath the report name, you will see different versions listed. Click on the entry that indicates the modified version (e.g., Microsoft Dynamics GP (Modified) or Great Plains (Modified)). This selection indicates that for the selected users and companies, the system should use this modified version of the report.

5. Assign Permissions:

   • In the Company area, select the companies for which this security setting should apply.
   • In the Users area, select the specific users or user roles that should have access to this modified report.
   • Click the Apply button.
   • Click OK to close the Advanced Security window.

Users selected in the previous steps will now run the modified version of the report with the custom date formatting. Advanced Security is recommended for environments where different users or roles require access to specific alternate or modified forms and reports.

Method 2: Use Standard Security¶

Standard Security is the older method and is simpler for assigning access to modified reports directly to individual users.

1. Access Standard Security:

   • On the Tools menu, point to Setup, then System, and click Security.
   • If prompted, enter the system password and click OK.

2. Select User and Report Type:

   • In the User ID list, select the user for whom you want to assign access to the modified report.
   • In the Type list, select Modified Reports.
   • In the Series list, select the series where your report is located.

3. Select the Modified Report:

   • In the Access List, you will see a list of reports. Locate the report you modified.
   • Click on the report name to highlight it.
   • Click Double Arrows >> to move the report name from the left-hand list (Available Reports) to the right-hand list (Reports Granted Access). This grants the selected user access to the modified version of this report.

4. Save Changes:

   • Click OK to save the security settings and close the Security window.

Repeat this process for every user who needs access to the modified report. Standard Security is less flexible than Advanced Security as it assigns permissions on a user-by-user basis rather than through roles or tasks.

Options for the RW_DateToString User-Defined Function¶

The flexibility of RW_DateToString comes from the format string parameter. This string contains literal characters combined with special codes that represent different parts of the date. When the function runs, it replaces these codes with the corresponding values from the date field.

The structure of the function call in the calculated field expression is:
FUNCTION_SCRIPT(RW_DateToString DateField "FormatString")

Where:
* DateField is the actual date field from a table in your report.
* "FormatString" is a string constant containing the format codes and any desired literal text or punctuation.

Here are the common format codes recognized by RW_DateToString:

Code	Description	Example (for April 12, 2007)
ddd	Day of week as three letters	Thu
dddd	Day of week in full	Thursday
N	Day of year	102
NN	Day of year (padded with leading zero)	102
D	Day of month	12
DD	Day of month (padded with leading zero)	12
M	Month of year	4
MM	Month of year (padded with leading zero)	04
mmm	Month of year as three letters	Apr
mmmm	Month of year in full	April
YY	Year of date (two digits)	07
YYYY	Year of date (four digits)	2007

You can combine these codes with literal characters like hyphens (-), slashes (/), commas (,), spaces, or any other text you want to include in the output string.

Here are several examples demonstrating how different format strings result in varied date displays, based on the date April 12, 2007:

Format String	Output Example	Explanation
“DD-mmm-YYYY”	12-Apr-2007	Padded day, 3-letter month, 4-digit year.
“DD/MM/YYYY”	12/04/2007	Padded day, padded month, 4-digit year with slashes.
“MM/DD/YY”	04/12/07	Padded month, padded day, 2-digit year with slashes.
“M/D/YY”	4/12/07	Unpadded month, unpadded day, 2-digit year.
“ddd, DD-mmm-YYYY”	Thu, 12-Apr-2007	3-letter day of week, comma, space, DD-mmm-YYYY.
“dddd, mmmm the DD in the year YYYY”	Thursday, April the 12 in the year 2007	Full day name, comma, full month name, literal text.
“DD-mmm-YY is the NN day of YYYY”	12-Apr-07 is the 102 day of 2007	Combines date parts, day of year, and literal text.
“mmmm DD, YYYY”	April 12, 2007	Full month name, padded day, comma, space, 4-digit year.

By carefully constructing the format string, you have complete control over how dates appear in your Microsoft Dynamics GP reports. Experiment with different combinations to achieve the exact presentation required for each specific report.

Saving and Deploying Modified Reports¶

Once you have successfully modified a report and tested it, you might need to deploy it to other workstations or a new Dynamics GP installation. Modified reports are stored in the Reports.dic file specific to each Dynamics GP installation or shared across a network for a multi-user setup.

1. Locate the Reports.dic: As noted in Step 1, the location of the Reports.dic file is found via the Edit Launch File window.
2. Copy the Reports.dic: Copy the Reports.dic file from the location where you made the modifications.
3. Deploy:
   • Single Workstation: Copy the modified Reports.dic file to the exact same path on the target workstation, overwriting the existing one (ensure you back up the existing one first!).
   • Networked Environment: If your Dynamics GP setup uses a shared Reports.dic file on the network, saving the changes automatically updates the shared file. If users have local Reports.dic files, you will need to deploy the modified file to each relevant workstation.
   • New Installation: When setting up Dynamics GP on a new machine, ensure the Reports.dic file from your existing, customized environment is placed in the correct location as specified in the Edit Launch File.

Always ensure that users log out and back into Dynamics GP after the Reports.dic file has been updated to pick up the changes.

Troubleshooting Common Issues¶

• Modified Report Not Showing: The most common issue is security. Verify that the correct security method (Advanced or Standard) has been used to grant the user access to the modified version of the report. Ensure the user is assigned to the correct company and user ID in the security settings.
• Calculated Field Appears Blank:
  • Double-check the calculated field definition in Report Writer. Ensure the Result Type is set to String.
  • Verify that the RW_DateToString function signature is correct and includes both the date field and the format string parameters within the parentheses, separated by a space, and the format string enclosed in double quotes.
  • Ensure the underlying date field (DateField in the expression) actually contains data for the records being reported.
• Incorrect Date Format Output: Review your FormatString in the calculated field definition. Even a single incorrect character or case (e.g., “yyyy” instead of “YYYY”) can cause unexpected results or the function to fail. Compare your format string against the list of valid codes.
• Report Writer Won’t Open/Save: This could indicate issues with permissions to the Reports.dic file or the Dynamics GP installation directory. Ensure the user has read/write access to the Reports.dic file location.

Mastering date formatting in Dynamics GP reports using RW_DateToString is a valuable skill for anyone working with Report Writer. It allows for greater control over report presentation, making your reports more professional, adaptable, and user-friendly for diverse audiences.

Conclusion¶

Customizing date formats in Microsoft Dynamics GP reports using the RW_DateToString function is a straightforward process involving creating a calculated field and setting appropriate security permissions. By following the steps outlined in this guide, you can transform standard date fields into strings formatted exactly as your reporting requirements dictate. The flexibility provided by the various format codes allows for a wide range of output styles, from simple DD/MM/YYYY to complex strings incorporating the day of the week or day of the year. Implementing these changes ensures your reports are not only accurate but also presented clearly and professionally.

What date formats are most common or most challenging for you to implement in your Dynamics GP reports? Share your experiences or questions in the comments below!