Monday, January 21, 2019

Saved Search to show Transactions with Different Posting Period and Date

This saved search is useful when the total amounts showing in the AR or AP Aging Reports compared with the AR or AP totals in in the Balance Sheet Report only matches when the preference for Report by Period is set to All Reports.


The preference is found if we navigate to Home > Set Preferences > Analytics tab > Reporting section > Report by Period field.


To get a list of transactions where the month of the transaction Date and the Posting Period are different, consider following the steps below:


1. Navigate to Reports > Saved Searches > All Saved Searches > New.

2. Select Transaction from the list.

3. In the Criteria tab > Standard subtab > set the following filters:
---- Formula (Numeric) = is 0


** Enter the following in the formula box: case when {postingperiod} = to_char({trandate}, 'Mon YYYY') then 1 else 0 end


Note: Do not change 'Mon YYYY' to for example, 'Jul 2013'.
It will generate this error: "Your formula has an error in it. It could resolve to the the wrong datatype, use an unknown function, or have a syntax error. Please go back, correct the formula, and re-submit.


Search: Transaction Search
Formula: case when {postingperiod} = to_char({trandate}, 'Jul 2012') then 1 else 0 end"


4. In the Results tab > Columns subtab > make sure to add the following fields:
---- Type

---- Number
---- Date
---- Period

5. Enter a title for the search.

6. Click the Save and Run button.


No comments:

Post a Comment