TechKnowledge Content
Question:
How can I view the percentages of the customer balance for each aging bucket on the Receivables Management Summary Historical Aged Trial Balance report (Reports - Sales - Trial Balance - Historical Aged Trial Balance - Summary )?
Answer:
The report can be modified in Report Writer to create a calculated field to determine the percentage for each aging bucket using the steps provided below.
Note: This TechKnowledge Document will provide the calculated field for the first aging bucket. You can use the same type of calculated fields for each aging bucket you have.
Opening the Report in Report Writer:
• Within Report Writer ( Tools - Customize - Report Writer) choose the Reports icon from the top menu bar. Highlight the RM Summary Historical Aged Trial Balance report in the Original Reports column and Insert it into the Modified Reports column.
• Highlight the same report in the Modified Reports column and choose the Open button.
• In the Report Definition window choose the Layout button.
Creating Calculated Fields:
• On the left side of the screen, locate your Toolbox and use the drop-down box to choose Calculated Fields .
• Choose the New button at the bottom of the Toolbox .
• In the Calculated Field Definition window, enter a name for your calculated field. Example: Bucket 1 Percent of Balance
• Leave the Result Type as Integer and the Expression Type as Calculated.
• Under the Operators section, choose the left parenthesis symbol to place it in the Expressions Calculated box.
• Choose the Fields tab and choose Report Fields for Resources .
• Using the Fields dropdown box, choose F1_SUM Current TRX Amount1 and choose the Add button to add the field to the Expressions Calculated box.
• Under the Operators section, choose the divide by symbol ( / ) to add it to the Expressions Calculated box.
• Using the Fields dropdown, choose F1_SUM Balance and choose the Add button to add the field to the Expressions Calculated box.
• Under the Operators section, choose the right parenthesis symbol.
• Under the Operators section, choose the multiply symbol ( * ).
• Choose the Constants tab and leave the Type set to Integer.
• Type the number 10000 in the Constant box and choose the Add button to add the constant to the Expressions Calculated box.
• The Expressions Calculated for the first aging bucket's calculated field should look like this:
(F1_SUMCurrentTRXAmount1 / F1_SUM Balance) * 10000
• Choose the OK button to save the calculated field.
Create similar calculated fields for each aging bucket by choosing F1_SUMCurrentTRXAmount2, F1_SUMCurrentTRXAmount3, F1_SUMCurrentTRXAmount4 , etc. for the numerator and keeping the denominator set to F1_SUMBalance .
Report Layout:
• When the calculated fields are saved, drag them on to the F1 section of the layout.
• Double click on the first calculated field in the layout and change the Field Type to data.
• While in the Report Field Options window for the first calculated field, click on the ellipse icon (...) to the right of the word Format .
• In the Format Lookup window, choose the New button.
• In the Format Definition window, name your format Percent Two Decimals.
• Type the number 2 in the Decimal Digits box and check the Show Percent Sign box.
• Choose the OK buttons in the Format Definition window, the Format Looku p window, and the Report Field Options window.
• Double click on the remaining calculated fields you added and change the Field Type to data and also choose the Percent Two Decimals format you created.
Exiting Report Writer
• Close the report, and Save changes when prompted.
• Choose File - Great Plains Dynamics/Enterprise and grant security to the report using Security Setup (Setup - System - Security).
Note: If you want to see percentages on the grand totals only, you can use RF report fields rather than the F1 report fields used in this TechKnowledge Document.
This article was TechKnowledge Document ID: 24411
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.
-------------------------------------------------------------------
