We currently use Frx for our financial reporting. When we want to find out reasons for expense fluctuations, we have to drill down in GP which takes a lot of time.
Has anyone had success in building (either in Frx or directly within GP) a report showing expenses classified by the following priority order:
1. G/L expense code
2. Summarized by vendor within the above g/l expense code
Thank you in advance for your help!
This would be pretty easy report and you have several of options.
You could do this in any reporting tool (SQL Reporting Services, Crystal Reports...), a SmartList export to Excel with a pivot table, refreshable Excel report with a pivot table, etc...
What reporting tools do you have at your immediate disposal or what is the preferred tool of choice? Let us know and we can get you going in the right direction.
Thank you. I would say a smartlist export to excel w/pivot table or the refreshable excel w/pivot would be preferred. We do use the smart list somewhat; one thing I'm struggling with is figuring out exactly which tables/fields I would want in the report, but I guess I'm also struggling in general. I appreciate your help.
Try this out:
You could start with the SmartList Purchasing > Payables Transactions > * (this is an unfiltered list). Under columns, add/remove the columns needed/not needed. Add columns “Purchase Account Number” and Purchases Amount, this will give you your expense account and its distribution amount. Under Search, you can choose to set-up any number of filters, perhaps you only want to see Document Type = Invoices with Voided = No. Once you have the subset of data using the tools in SmartList, you can export this to Excel. Once in Excel, you can insert a Pivot Table with the exported data as the data source. From there, take your Purchases Account Number, Vendor ID/Name, and Document Date and drag those to the “Row Labels” section for the report grouping and Purchase Amount to values for the totals. Try this and see if the data looks like what you’re after.
This same approach could be taken from the Financial side of the system as GP does record the “Originating Master ID” and “Originating Master Name” which would be you underlying Customer/Vendor of any transaction. We just have to do a bit more filtering from this angle. The method I detailed above can have some discrepancies depending on batch posting process and potential interception of the Financial batch prior to posting as well.
This is not an ideal long term solution as you would have to build this out each time (save the SmartList as a Favorite though!). I would really look into using SQL Reporting Services or Crystal Reports (I am a bit biased on reporting tools) for a long term build out as you can use parameters to filter on any number of fields that may be relevant.
If you're looking for some SQL code to help with this, take a look at this: victoriayudin.com/.../sql-view-to-show-all-gl-distributions-for-ap-transactions.
This will give you the GL distributions for all payables transactions. Once you have this, you can decide what reporting tool to use to display the data.
If not all of your transactions for these accounts come from the payables module, you may instead want to look directly at the GL. Here is code for all posted GL transactions:
As Mike mentioned, you can then use the Originating Master ID to group by Vendor.
Victoria Yudin - Dynamics GP MVP (2005-2013)
Want to use Crystal Reports or SSRS with Dynamics GP? Check out GP Reports Viewer
Mike, the smartlist suggestion you provided is a great start! One more question for you: our g/l codes are x-xxx-xxx-xxx, where the 3rd string is the expense code. for example, 6-440-254-000 is one of our location's purchased services. We have multiple locations; is there a way I can set a filter for it to show me what our company wide expense was for each expense code such as 254, and so on? Thank you.
Once the data is in Excel, you can set-up a formula field on your account number to pull that peice out: =MID(AccountNumberColumn, 7, 3). This will pull data out of the string starting at character 7 and 3 characters. In 6-440-254-00, it should return 254. From there, you can set-up a filter on your columns in Excel (Home > Sort & Filter > Filter), and you can select whatever value(s) you want to report on.
Save a link to this thread as well because once a more permanent solution is in place with SRS, Crystal, or SmartList Builder (or any other reporting tool), Victoria's SQL queries will save the team a lot of work.
With a typical GP setup you should be able to isolate each of your GL account segments on the Financial SmartLists. For example, if in your account format setup (GP | Tools | Setup | Company | Account Format), the 3rd segment is called Code, when you go to add columns to the Account Transactions SmartList, you should have a column available to add called "Code".
You will not be able to do this in Payables SmartLists, however, I strongly advise against using the out-of-the-box Payables SmartLists for what you're asking - the account number you will see is only the default Purchase account set up for the vendor - not what was actually on the transaction. And if there are multiple expense accounts on a transaction, there is not way to see this either. I would only use the Financial Account Transactions SmartList for this.
I didn't realize the account was coming from the Vendor Master and not the actual transaction, this will certainly not be the best option.
Other Microsoft Sites
I'm a Customer
I'm a Partner
Use the official Twitter tags:
#MSDYNCOMM | #CONV13