Wednesday, June 19, 2019

Bank Register by Period Report Through a Saved Search

There are instances when users would need to have a Bank Register that accounts for transactions using the Posting Period. However, the Bank Register uses Transaction Date by design. Also, changing Home > Set Preferences Analytics > Reporting > Report By Period = All Reports does not change the filter at the footer of this report.

Transaction Saved Search can be created using Period in the Available Filters. However, the values available using this field is canned and the ability to use Custom or to use multi-select for the Period is also not supported.

To illustrate, assume that today is 03/01/14 and users want to get a Bank Register Report that will include transactions within Periods: Aug 2013 to Dec 2013 for Account: Checking. Please note that Aug 2013 – Dec 2013 does not fall on the available selection for Period (e.g. Last Quarter, Last Fiscal Year, etc.)

The alternate solution is to create Saved Search using advanced formula with the following prerequisite: note the distance between the beginning period and ending period in relation to today's period. In the given scenario Aug 2013 has a distance of 7 periods from Mar 2014 while Dec 2013 has a distance of 3.

1.    Navigate to Transactions>Management>Saved Searches>New

2.    Select Transaction

3.    On the Criteria tab add the following fields:

 

         Account is Checking

         Formula (Numeric) = TO_CHAR({today}, 'MM')-DECODE(SUBSTR({postingperiod},1,3), 'Jan', 1, 'Feb', 2, 'Mar', 3, 'Apr', 4, 'May', 5, 'Jun', 6, 'July', 7, 'Aug', 8, 'Sep', 9, 'Oct', 10, 'Nov', 11, 'Dec', 12)+12*(TO_CHAR({today},'YYYY')-SUBSTR({postingperiod}, 5, 8)) is greater than 3

         Formula (Numeric) = TO_CHAR({today}, 'MM')-DECODE(SUBSTR({postingperiod},1,3), 'Jan', 1, 'Feb', 2, 'Mar', 3, 'Apr', 4, 'May', 5, 'Jun', 6, 'July', 7, 'Aug', 8, 'Sep', 9, 'Oct', 10, 'Nov', 11, 'Dec', 12)+12*(TO_CHAR({today},'YYYY')-SUBSTR({postingperiod}, 5, 8)) is less than 7

 

4.    On the Results tab check the Show Totals box and add the following fields:

         Date

         Period

         Number

         Name

         Account

         Amount (Debit)

         Amount (Credit)

         Amount

5.    Hit Preview or Save and Run

The Saved Search will now display transactions using the Period as criteria instead of the transaction dates. Users can change the greater than and less than values on the two formula used on the criteria to match the Periods to be covered.

No comments:

Post a Comment