RSMUS.comReport Writer and SmartList
In my last post I provided some basic conditions to consider in determining which reporting tool to use as well as outlining the standard Microsoft Dynamics GP reporting tools. In this post I will discuss the two most commonly used tools: Report Writer reports and SmartLists.
Report Writer reports
Report Writer is the inherent reporting tool that all internal edit lists, posting journals, trial balances and analysis reports are written with. Not much enhancement has been made to the reports in the 18 years I have been implementing and supporting Dynamics GP.
In my opinion, Report Writer reports are best for:
- Maintaining journal entry posting journals for compiling a month-end package of work papers
- Reprinting a posting journal/journal entry report
- Maintaining bank reconciliation reports (bank reconciliation, outstanding checks, cleared transactions, etc.)
- Printing a check register at the end of the month
- Printing trial balances (GL, AP and AR)
- Fixed assets reports
The pros and cons of Report Writer are:
| Pros | Cons |
| Suitable for reviewing data before posting (edit lists), maintaining with month end work papers or for audit purposes | Must be logged into GP in order to run; can be tricky to run some analysis reports due to limited search criteria |
| Can be exported to tab or comma delimited file; can be exported to PDF if you own adobe writer or some free PDF tool | Cannot be exported to Excel
Tab or comma delimited file isn’t suitable if you are reformatting data in columns |
| Adding new fields to reports can create a need to reformat the entire report | |
| Modifying the report formats (e.g. adding new tables and/or fields) is not always easy as Report Writer is not very user friendly | |
| Tend to have too much information on some reports or poor report formatting which makes them too length and/or not very aesthetically pleasing |
The most commonly used reports are:
Financial
- Financial – Reports – Trial Balance – Detail or Summary
- Financial – Reports – Cross Reference (reprinting a posted journal entry)
- Financial – Reports – Bank Posting Journals
- Reconciliation Journal
- Cleared Transactions Journal
- Outstanding Transactions Report
- Financial – Reports – Checkbook – Check Register
Purchasing
- Purchasing – Reports – Trial Balance
- Aged Trial Balance with Options
- Historical Aged Trial Balance
- Purchasing – Reports – History
- Transaction History – Detail and Summary
- Purchase Order History
- Receivings Trx History
- Purchasing – Reports – Analysis
- Cash Requirements
- Purchase Order Status
- Received/Not Invoices
- Purchasing – Reports – Check Information
- Vendor Check Register
Sales
- Sales – Reports – Trial Balance
- Aged Trial Balance with Options
- Historical Aged Trial Balance
- Sales – Reports – Analysis
- Unapplied documents
Fixed Assets
- Financial – Reports – Fixed Assets – Activity
- Fixed Assets to General Ledger Reconciliation
- Detail Activity
- Financial – Reports – Fixed Assets – Depreciation
- Depreciation Detail
- Depreciation Ledger
- Depreciation Expense to General Ledger
Many of the Report Writer reports have been replicated into SSRS reports. SSRS reports are free with a
Dynamics GP implementation but require a separate deployment and generally a report server. I will discuss SSRS reports in the next blog.
SmartList
SmartList is real time query tool that allows you to query transactional and master data across fiscal years which can be easily exported to Excel for grouping, filtering and subtotaling. It is very easy to use and allows users to add a multitude of fields to existing queries so that specialized queries can be created and saved (to a Favorite).
SmartLists are most valuable when:
- you need to gather and evaluate transactional data across several fiscal years
- you need to be able to search on or include specific fields such as transaction amounts, customer or vendor (master IDs), dates or date ranges
- you need to save customized queries (favorites) that can be used repeatedly by one or more users
- you need the ability to export data in columnar format (Excel) in order to add subtotals, create pivot tables or graphs
- you need to be able to send the data, in Excel or Word format, to a stakeholder of the organization that does not have access to GP
| Pros | Cons |
| Real-time data that includes a vast number of data elements that can be queried on | Must be logged into GP in order to run |
| Can be exported easily to Excel (preferably) or Word | Must refresh the query every time new data is added to the system and re-export |
| Can easily add fields, enter search restrictions and design very specific data queries | Some users find the number of fields available to use overwhelming |
| Can easily save data queries (favorites) to be accessed by all system users or restricted to the user who created the favorite | The list of favorites will keep growing as users save their specialized queries – can become messy if not managed |
| Can report on data in every status: unposted, posted, work, open or history, voided | Does not replace certain reports like trial balances or aging reports |
| Up to four sets of search criteria can be used to filter the data | Search criteria can be limiting – sometimes you need more than four variables |
| A number of pre-defined, out of the box favorites are available with installation (marked with an asterisk) | Out of the box queries can’t be combined (except through SmartList Designer) |
Once you get to know SmartList you will not understand how you lived without it – it is that helpful!
SmartList Designer
One cannot mention SmartList without mentioning SmartList Designer. Smartlist Designer is a free tool offered by Microsoft to enhance the SmartList experience. Smartlist Designer allows the GP user the ability to join tables in order to create SmartLists that cannot be created by adding or removing fields from the current “out of the box” SmartLists. SmartList Designer also allows GP users to create queries on data where SmartLists just do not exist – such as deferrals or budget information. Additional benefits of SmartList Designer are that it allows GP users to:
- Create SmartLists based on existing or newly created views
- Export custom SmartLists (based on views) to Excel Refreshable reports
- Create calculated fields
- Specify filters to restrict data
- Allows users to combine data from GP and other data sources when creating new or modifying existing SmartLists and building Refreshable Excel reports
In order to create SmartLists using SmartList Designer one should have some familiarity with the GP tables and a basic understanding of how to join tables.
There is never any harm in experimenting with SmartList Designer as SmartLists created using SmartList Designer can be modified or deleted any point in time. Keep in mind if you have built restrictions into your SmartLists clearly name your new SmartList so that other GP users understand the data that they are querying.
If you are a power GP user and want to learn how to use SmartList Designer it is well worth your time to set up a training session with a RSM GP consultant that should take around a couple of hours.
In my next post I will talk about SSRS reports and Navigation Lists.
