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'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?
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"
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.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,235
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (