|by Briley Acker, CPA|
Among the standard lists under the SmartList tool in GP is the Account Transactions report. This standard list can be quickly modified to give users a detailed General Ledger report that can be exported to Excel cleanly and promptly.
Unlike the General Ledger report that can be pulled from the GP Reports module, a SmartList will not require any additional column formatting once it is exported to Excel.
To access this list, navigate to Microsoft Dynamics GP >> SmartList.
From the left report list, select Financial >> Account Transactions >> *.
From here, you can begin to modify your list by including and ordering your columns.
To do this, select the Columns icon at the top of the list screen.
In the Change Column Display window, you’ll see the columns that are already provided with the standard report. Select the "Add" button to search for new columns to include in your list, and use the “Remove” button to delete any unwanted columns.
Some suggested columns are:
- Reference – the transaction header description
- Description – The memo/line reference for each line in the transaction
- Originating Master ID/Name – Customer or Vendor ID & name
- Period ID – Used to show the posting month, can be used filter out data from the year-end closing transaction (Period 0)
As desired columns are added/removed, you can select the column name and use the right side arrows to shift the column order of your list.
In addition to modifying the columns that are used in the list, you can also use the Search feature to filter the list output and to set the date parameters for the posted transactions.
To start, select the “Search” icon at the top of the list screen.
Choose the field or column to which you would like to apply a filter, and then select the filtered value.
Common filters include:
TRX Date – between dates or greater than dates
Originating Master ID – set it equal to customer or vendor ID to see related transaction data
Main Account Segment – is between a range to see all income statement or balance sheet data
Once the filters have been set, update the “Maximum Records” box with all 9’s, so that all data that meets the filter requirements is included in your list. The default setting will only return the first 1,000 records that match your criteria.
If the field is not actually a column header (which is common if you need to filter on an account segment), change your selection pool from “Selected Columns” to “All Columns.”
Finally, if you want to sort the data by something other than the transaction ID, select the “Order By” button at the bottom of the Search form.
Choose the field(s) that you would like to order the list by, and choose which order those values should be sorted. Then hit “Ok”
Press “Ok” on your filtered Search form and your list should populate with the transaction data that meets the specified criteria. This report can then be exported to Excel.
Once you’ve created a General Ledger report that produces the data you’re looking for, be sure to save the list by selecting the “Favorites” icon. Name your report and select to whom it should be visible, and then hit “Add”
Next time you access SmartList, your custom GL report will be listed under the “Account Transaction” section