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

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / YTD sales showing last...
Microsoft Dynamics GP (Archived)

YTD sales showing last year's data...

(0) ShareShare
ReportReport
Posted on by Steve P 170

When I run an SSRS report that has YTD sales, it shows not only this years data, but the second half of last year as well. I figure this is happening because we previously had fiscal years that started July 1st. We changed at the beginning of 2012 to a calendar year. I looked through the SQL and couldn't see anything going after fiscal year data. Is it OK to post the SQL code here?

*This post is locked for comments

I have the same question (0)
  • Community Member Profile Picture
    Community Member on at
    Re: YTD sales showing last year's data...

    Hi Steve - yes - post the code here.

    If you are getting your data from the Sales YTD field in the Debtor Summary table - this could be your problem. You may not have run a Sales Year end at the end of the last calendar year when you changed over. If so this field will be collecting data from last year and this new year.

    There is really no simple solution to this - you could maybe look at calculating the sales for each year for each customer (from the RM Posted transactions tables) and updating this summary table...or changing the SSRS report to calculate the sales to date based on the RM Posted Transaction tables.

    Ian.

  • Steve P Profile Picture
    Steve P 170 on at
    Re: YTD sales showing last year's data...

    OK, here's the first:

    --declare @MBeg as datetime = '2017-04-01'
    --declare @YBeg as datetime = '2016-07-01'
    --declare @YEnd as datetime = '2017-04-30'

    select c.CUSTNMBR, c.CUSTNAME, c.ITEMNMBR, c.ITEMDESC
    , sum(c.M_QUANTITY) as M_QUANTITY
    , sum(c.M_XTNDPRCE) as M_XTNDPRCE
    , sum(c.Y_QUANTITY) as Y_QUANTITY
    , sum(c.Y_XTNDPRCE) as Y_XTNDPRCE 
    from
    (
    select b.DOCDATE, b.CUSTNMBR, b.CUSTNAME, b.ITEMNMBR, b.ITEMDESC, b.ITMCLSCD, b.Y_XTNDPRCE, b.Y_QUANTITY
    , case 
     when b.DOCDATE between @MBeg and @YEnd
     then b.Y_XTNDPRCE
     else 0
    end as M_XTNDPRCE 
    , case 
     when b.DOCDATE between @MBeg and @YEnd
     then b.Y_QUANTITY
     else 0
    end as M_QUANTITY    
    from
    (
    select a.SOPTYPE, a.SOPNUMBE, a.DOCDATE, a.CUSTNMBR, d.CUSTNAME, b.ITEMNMBR, c.ITEMDESC, c.ITMCLSCD
    , case
     when a.SOPTYPE = 3 then b.XTNDPRCE
     when a.SOPTYPE = 4 then b.XTNDPRCE * -1
     else 0
    end as Y_XTNDPRCE
    , case
     when a.SOPTYPE = 3 then b.QUANTITY
     when a.SOPTYPE = 4 then b.QUANTITY * -1
     else 0
    end as Y_QUANTITY
    from SOP30200 a
    join SOP30300 b on b.SOPTYPE = a.SOPTYPE and b.SOPNUMBE = a.SOPNUMBE
    join IV00101 c on c.ITEMNMBR = b.ITEMNMBR
    join RM00101 d on d.CUSTNMBR = a.CUSTNMBR
    where a.SOPTYPE in (3,4)
    and a.VOIDSTTS = 0
    and a.DOCDATE between @YBeg and @YEnd
    ) b
    ) c
    where c.ITMCLSCD in (@ItemClass)
    group by c.CUSTNMBR, c.CUSTNAME, c.ITEMNMBR, c.ITEMDESC
    order by c.CUSTNAME, c.ITEMNMBR

    The second one is:

    select year(getdate()) as YEAR1
    union all
    select YEAR1 from sy40101
    order by YEAR1 desc

    The third one is:

    select distinct rtrim(ITMCLSCD) as ITMCLSCD from IV00101
    order by ITMCLSCD

    On the report itself, the qty ytd is "=Fields!Y_QUANTITY.Value" the sales ytd is "=Fields!Y_XTNDPRCE.Value"

  • Steve P Profile Picture
    Steve P 170 on at
    Re: YTD sales showing last year's data...

    I'm going through Mark Polino's excel sheet of tables and cannot find a RM Transaction Table. Would it be the "RM Realtime Posting File" or RM20102?

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

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

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

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September 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