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

Notifications

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) / Period Sales Analysis ...
Microsoft Dynamics GP (Archived)

Period Sales Analysis Report

(0) ShareShare
ReportReport
Posted on by Lance Berg 110

Hi, I was wondering if anyone could give me any information about how the tables behind the Period Sales Analysis Report - Summary are created.  The tables are RM_Period_Sales_Analysis_Header_TEMP and RM_Period Sales_Analysis_Detail_TEMP.  I'm trying to figure out how the sales amount column is calculated.  I'm fairly new to Microsoft GP and am used to looking at the query behind the report I am analyzing or knowing where the code is that creates a temp table or view but have been unable to locate that information for this report so far.

Thanks in advance,

Lance Berg 

 

*This post is locked for comments

I have the same question (0)
  • Sunbeam De Jesus Profile Picture
    Sunbeam De Jesus on at

    Programmatically you have to open the program of GP so you will get what you want, how the fields are calculated as shown in the report. You may need dexterity know how and source code.

    On the other hand, from front end, you can also try to create your own test customer and post a few sample invoices, payments and credit memo then you can generate the report sales analysis against the customer. Obviously you know your expected output and derive the formula of these figures.

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

    Lance,

    By comparing this to some other reports I have created it looks to me like this report is simply using data from the RM00104 table.  Also bringing in RM00101 for the customer name.

  • Lance Berg Profile Picture
    Lance Berg 110 on at

    Thanks for the feedback.  I did open the report and print out a report definition which is where I discovered that the report looks like it is based off of temp tables.  I was trying to find out where I can find the code that creates these temp tables for the report so I can figure out how the sales amount field is calculated.

  • Lance Berg Profile Picture
    Lance Berg 110 on at

    Victoria, thanks for the feedback.  What I really need to be able to do is find out how the sales amount is calculated in RM00104 (or on the Period Sales Analysis Report - Summary.  What we are trying to do is create a report that is broken down by the last 4 digits of the account number ( or the natural code i.e. 4300 from 100-10-100-420-4300) and shows the revenue for each different natural code that will balance back to the sales amount on the Period Sales Analysis Report - Summary.  I'm trying to find out how the sales amount is calculated so I know what I need to add or subtract to balance out with RM00104.  I'm not sure if there are certain natural codes I should leave out of the equation etc.

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

    Lance, I don't have the code behind the report, but as far as I can see the Sales amount is taken directly from the SMRYSALS column of the RM00104 table.  HISTTYPE is 0 for calendar year or 1 for fiscal year - use one or the other, not both.

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

    Ah!  This might be a little more difficult.  In your Receivables Setup you would have specified what makes up the 'Sales' total for each transaction.  To see this go to Tools > Setup > Sales > Receivables - there is a section at the bottom which shows what types of amounts are included in the 'Sales' summary amount.  If everything is checked there you may have more being included in the sales amount than you are expecting.  The problem with this is that these settings can be changed at any time.  GP does not keep track of what makes up the sales summary, it just adds to it as needed. 

    Because of that....typically, this is not a report I would look to match in the GL because there is no way to ensure it is 100% accurate.  I would instead look to match the amounts in the General Ledger sales accounts to the totals of the detailed sales transactions.  If you are using SmartList, I would probably look at the Receivables Transactions one.  Or, under Reports > Sales > Analysis, maybe look at the Receivables Transaction report.

  • Lance Berg Profile Picture
    Lance Berg 110 on at

    I printed out our setup and we seem to have everything check off at the moment (Sales history includes Sales, discount, freight, misc., and tax) assuming this is what you are talking about.  I can see where this would be a problem if any of the settings were changed.  Our current problem is in the script we wrote we are coming up with a higher amount than the summary sales amount and are trying to see what we should leave out to balance with the sales history amount.  We will also look into your alternative of matching the GL sales accounts to the totals of the detailed sales transactions.  Thanks again for your help...

  • Hemant Ashar Profile Picture
    Hemant Ashar 5 on at

    Hello all. I am new to this. Could somebody please tell me, in the Period sales report, the sales amount is taken from the salesman entered in the customer sales master. But what if I want it to pick up the salesman from the Invoicing point? Awaiting some help please.. and thanks,,

  • maman Profile Picture
    maman 235 on at

    Greetings!

    The Period Sales Analysis report seems to include 'Freight' in the sales revenue field but not in the costs field. Is there a setting where this can be adjusted? I followed Victoria's instructions but could not find the check boxes to select the amounts that are included in sales summary.

    Any hints and suggestions would be greatly appreciated!  

  • Suggested answer
    L Vail Profile Picture
    L Vail 65,271 on at

    The checkboxes go horizontally across the bottom of the Receivables Setup Options window. To access this window (2010 nav), go to Sales > Setup > Receivables and then hit the Options button in the lower right-hand corner of the Receivables Management Setup window.

    Kind regards,

    Leslie

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans