TechKnowledge Content
Issue
How to add the Payables Management Transaction Date to the Detail Trial Balance History in General Ledger using Report Writer?
Resolution
Follow the steps below to add the Payables Transaction Date to the Detail Trial Balance History.
1. Launch into Report Writer (Tools-Customize-Report Writer).
2. Click on the Tables Button. Select Tables from the Drop-Down.
3. Scroll Down to the GL_Account_TRX_HIST. Highlight the selection and click on the Open Button.
4. From the Table Definition Window, click on the Relationships Button. In the table Relationship Window, click on New.
5. In the Table Relationship Definition Window, select the PM Key Master File as your Secondary table. Use the key PM_Key_Mstr_Key3 for the Secondary Table Key and choose Originating Document Number for the Primary table for the line that reads Document Number for the secondary table.
6. Click Ok on the window and close the others to get back to the main Report Writer Screen.
7. Click on the Reports Button. Select Trial Balance History from the list of Original Reports and then click Insert.
8. Highlight the Trial Balance History in the Modified Reports List and then click Open.
9. Click on the Tables button in the Report Definition Window. Highlight the Account Transaction History and click New. Select the PM Key Master and click OK. Close the Table Relationships Window.
10. Click on the Layout button on the Report Definition Window.
11. Select Calculated Fields in the first drop down window in the Toolbox window. Click on New. Name the Calculated Field PM Trx Date and select Date as the Result Type. Check the Conditional Expression box and create the expression as follows. Click on the Fields tab. Choose Account Transaction History under Resources and select Originating Document Number under Field. Click the Add button. Click on the not equal operator (<>). Click on the Constants tab. Choose String for the Type and tab to the Constant field below. Press the space bar once and then click the Add button. The Conditional Expression should appear as follows:
GL_Account_TRX_HIST.Originating Document Number <> '' "
12. Click in the True Case box and create the expression as follows. Click on the Fields tab. Choose PM Key Master File under Resources and select Document Date under Field. Click on the Add button. The True Case should appear as follows:
PM_Key_MSTR.Document Date
13. Click in the False Case box and create the expression as follows. Click on the Constants tab. Choose Date for the Type and tab to the Constant field below. The field should appear as all zeros. Click on the Add button. The False Case should appear as follows:
0/0/00 or you may see 0/0/0000
14. Click OK to save the new calculated field. Then drag the calculated field called PM Trx Date into the body of the report. Close the Report Layout Window and select to Save changes.
15. In the Report Definition window, click on the Restrictions button and choose New. Enter Document Date for the Restriction Name, choose the PM Key Master for the Report Table and Document Number for the Table Field. Click Add Field. Click on the equals sign (=). Choose the Account Transaction History for the Report Table and Originating Document Number for the Table Field, click on Add Field. Choose OK.
16. Choose New. Enter Control Number for the Restriction Name, choose PM Key Master for the Report Table and Control Number for the Table Field. Click on Add Field. Click on the equals sign (=). Choose Account Transaction History for the Report Table and the Originating Control Number for the Table Field, click on Add Field. Choose OK.
17. Go into Great Plains (File - Great Plains) and Grant Security to the modified Report (Setup-System-Security). Choose the User ID, Company, Product will be either Dynamics or eEnterprise, Type of Modified Reports, Series Financial.
This article was TechKnowledge Document ID: 24115
DISCLAIMER
MICROSOFT AND/OR ITS SUPPLIERS MAKE NO REPRESENTATIONS OR WARRANTIES ABOUT THE SUITABILITY, THE RELIABILITY OR THE ACCURACY OF THE INFORMATION THAT IS CONTAINED IN THE DOCUMENTS AND THE RELATED GRAPHICS PUBLISHED ON THIS WEB SITE (THE “MATERIALS”) FOR ANY PURPOSE.
THE MATERIALS MAY INCLUDE TECHNICAL INACCURACIES OR TYPOGRAPHICAL ERRORS AND MAY BE REVISED AT ANY TIME WITHOUT NOTICE. TO THE MAXIMUM EXTENT PERMITTED BY APPLICABLE LAW, MICROSOFT AND/OR ITS SUPPLIERS DISCLAIM AND EXCLUDE ALL REPRESENTATIONS, WARRANTIES, AND CONDITIONS WHETHER EXPRESS, IMPLIED OR STATUTORY, INCLUDING BUT NOT LIMITED TO REPRESENTATIONS, WARRANTIES, OR CONDITIONS OF TITLE, NON-INFRINGEMENT, SATISFACTORY CONDITION OR QUALITY, MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE, WITH RESPECT TO THE MATERIALS.
