web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / creating report showin...
Microsoft Dynamics GP (Archived)

creating report showing expenses by vendor, date

(0) ShareShare
ReportReport
Posted on by Community Member

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

3. Date

Thank you in advance for your help!

Neil

*This post is locked for comments

I have the same question (0)
  • Mike Smith Profile Picture
    Mike Smith 6,840 on at

    Hi Neil,

    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.

  • Community Member Profile Picture
    Community Member on at

    Hi Mike,

    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.

    Neil

  • Mike Smith Profile Picture
    Mike Smith 6,840 on at

    Neil,

    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.

  • Victoria Yudin Profile Picture
    Victoria Yudin 22,769 on at

    Neil,

    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:

    victoriayudin.com/.../sql-view-for-all-posted-gl-transactions-in-dynamics-gp

    As Mike mentioned, you can then use the Originating Master ID to group by Vendor.

  • Community Member Profile Picture
    Community Member on at

    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.

  • Mike Smith Profile Picture
    Mike Smith 6,840 on at

    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.

  • Victoria Yudin Profile Picture
    Victoria Yudin 22,769 on at

    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.

  • Mike Smith Profile Picture
    Mike Smith 6,840 on at

    Thanks Victoria,

    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Meet the Microsoft Dynamics 365 Contact Center Champions

We are thrilled to have these Champions in our Community!

Congratulations to the March Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans