Skip to main content

How do I add a calculated field with totals on a report using the Report Manager?

Updated

EDITION: Standard, Enterprise

RESOLUTION

To only show specific transactions on the Open Invoice report (or the Statement report), you must hide the transactions that you do not want to appear using conditional formatting on the Details section of the report but you should also change the report Totals to reflect only invoices with balance over 60 days using a calculated field.

You must be logged into the software with Internet Explorer to launch the Report Manager:

  1. Log into the software with IE for your browser

  2. Admin > Settings & Utilities > Report Manager

  3. Click YES to message 'An existing session could be active with the same UserID on domain\machine'

  4. The Reports window will open

  5. Click the green + to add/create a new report

  6. Include the Report Name, description and select the Report Base from the drop-down to select one of the 4 standard reports to base your new report

  7. Click View Layout

  8. To open the Report Designer, in the Preview window - click Edit

  9. To create a new calculated field, View > Windows > Field List

  10. In the Field List window, right-click on the DataSet, click add calculated field

  11. Right click on new field, edit Calculated Field

  12. Properties: Design section - rename the field

  13. Properties: Data Member, verify this is set, should not be blank, use the drop-down to set if blank

  14. Click OK to save properties

  15. Right click on calculated field and edit the expression

  16. Create a new invoice balance calculation to only include invoices with a due date older than 60 days, using the If() function, DateDiffDay (), function and the today() function:

  • Iif(DateDiffDay([TranDate],today())>60, [Balance] ,0 )

Click OK to save the condition

Add the new Calculated field to the GroupFooter section of the report where you want the total open amount over 60 days late

Click on the field properties, select Summary

In Summary Editor, change the Summary Running to 'Group'

Use the Format String to format the field

Click OK to save

Save the Report and preview to verify the total is correct an only for invoices over 60 days late.