One of the most commonly asked questions from my Microsoft Dynamics GP customers is – how do I see how much I paid a certain vendor by GL account? For example: if you need to see all the legal fees paid to several different law firms, where do you go to get this information quickly and easily? If you follow these steps, you can build a report in SmartList using SmartList Designer or eOne SmartList Builder.
Create a SQL Query
- First, realize that you need a SQL view. This is a virtual table that gathers data from several tables in your Dynamics GP database. You need to pull AP invoices to get the GL account distributions (header, line, and distribution tables), vendor information (vendor master), and payment information for amounts and dates (apply to information). You could pull all this data from these various tables or build your own SQL view.
- Review Dynamics GP MVP Victoria Yudin’s website for popular pre-built SQL views. There is a list of SQL views on this page and I selected Payables.
- Select the GL Distributions for AP Transactions. You should copy this script into Notepad or Word or OneNote to save it to plug into a SQL query in a few minutes.
- To run a SQL query, you need to have appropriate access to the SQL Server Management Studio, so make sure you talk to someone in IT. They might prefer to do this for you.
- Click on the company database in which you want to create the view (TWO in this case)
- Click on New Query and paste the script into this area on the right.
- Click on Execute. You should get the message: Command completed successfully. If you get errors, you might have selected the wrong database.
Now, Create the SmartList
- Now you are ready to use SmartList Designer or eOne SmartList Builder to find the view and bring it into a SmartList. I’m going to use SmartList Designer because everyone has that tool. The process is similar in eOne SmartList Builder. Here are instructions from Victoria’s website on how to build a report using a view. She’s a great resource (and amazing to share all this information with you and me!)
- Open SmartList and click on New.
- Give the list a name, the product is Dynamics GP, and Purchasing is a good series to put this report.
- Look under Database View and scroll to the end under Views and look for the name of the view you just created: view_AP_Distributions.
- Click on it and all the fields in the view will populate the Selected Fields on the right. You can uncheck to remove some, but I left them all in. This is the default view and you might need them down the road.
- Execute the Query to view the returned data in the Preview window at the bottom.
- You don’t need to build any relationships because the SQL view did that for you. You can filter some information if you like. For instance, this view has posted and unposted data. If you want to see only posted transactions, you can filter by posted. You could also filter by year. But keep in mind that you can do this in SmartList too. I recommend leaving all the data there for the default.
Now that the SmartList is built, customize it for your needs!
- You can create a Go To for drilling to another SmartList or Navigation List with payables invoices. To do this, click on Create Go To and reference the SmartList or Navigation List.
If you want to drill directly to a payables invoice, you will need eOne SmartList Builder. Here is a list of the differences between eOne SmartList Builder and SmartList Designer. I recommend buying SmartList Builder and you will have a lot more functionality.
- Click Save and OK to get back to SmartList.
- You will find the new SmartList under Purchasing. Start to have fun by filtering the data to get that analysis of legal fees by searching by the legal fee account number or by account name. You can move the fields around if you like.
Hope you have good luck with this report and if you have other suggestions for SmartList or Navigation Lists, please let us know. Contact your partner for a special price on eOne SmartList Builder and make sure to check out other resources provided by your Microsoft Dynamics MVPs and their vast knowledge.
