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:
-
Log into the software with IE for your browser
-
Admin > Settings & Utilities > Report Manager
-
Click YES to message 'An existing session could be active with the same UserID on domain\machine'
-
The Reports window will open
-
Click the green + to add/create a new report
-
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
-
Click View Layout
-
To open the Report Designer, in the Preview window - click Edit
-
To create a new calculated field, View > Windows > Field List
-
In the Field List window, right-click on the DataSet, click add calculated field
-
Right click on new field, edit Calculated Field
-
Properties: Design section - rename the field
-
Properties: Data Member, verify this is set, should not be blank, use the drop-down to set if blank
-
Click OK to save properties
-
Right click on calculated field and edit the expression
-
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.