June 7, 2013 
Someone asked me for code to get monthly SOP sales and I could have sworn I already had that. But I searched and didn’t come up with anything, so here is a new view to show SOP sales (the total of SOP invoices less returns) by month. As usual, I am making a few assumptions (listed in the view comments), and am hard coding one year at a time (2013 in this example). You can easily change the year as needed on line 59.
create view view_Sales_by_Customer_by_Month as -- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ -- view_Sales_by_Customer_by_Month -- Created June 7, 2013 -- by Victoria Yudin - Flexible Solutions, Inc. -- For updates see https://victoriayudin.com/gp-reports/ -- Returns total sales (invoices - returns) for each customer -- by month (for the specified year) -- Amount used is the invoice total (including freight, -- taxes, discounts, etc.) -- Only posted invoices and returns are included -- Shows functional currency -- Voided transactions are excluded -- Document Date is used (not GL Posting Date) -- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ select d.CUSTNMBR Customer_ID, d.CUSTNAME Customer_Name, sum(case when month(d.DOCDATE) = 1 then d.SALES else 0 end) Jan_Sales, sum(case when month(d.DOCDATE) = 2 then d.SALES else 0 end) Feb_Sales, sum(case when month(d.DOCDATE) = 3 then d.SALES else 0 end) Mar_Sales, sum(case when month(d.DOCDATE) = 4 then d.SALES else 0 end) Apr_Sales, sum(case when month(d.DOCDATE) = 5 then d.SALES else 0 end) May_Sales, sum(case when month(d.DOCDATE) = 6 then d.SALES else 0 end) Jun_Sales, sum(case when month(d.DOCDATE) = 7 then d.SALES else 0 end) Jul_Sales, sum(case when month(d.DOCDATE) = 8 then d.SALES else 0 end) Aug_Sales, sum(case when month(d.DOCDATE) = 9 then d.SALES else 0 end) Sep_Sales, sum(case when month(d.DOCDATE) = 10 then d.SALES else 0 end) Oct_Sales, sum(case when month(d.DOCDATE) = 11 then d.SALES else 0 end) Nov_Sales, sum(case when month(d.DOCDATE) = 12 then d.SALES else 0 end) Dec_Sales, sum(d.SALES) Yearly_Total from (select s.DOCDATE, s.CUSTNMBR, c.CUSTNAME, case s.SOPTYPE when 3 then s.DOCAMNT when 4 then s.DOCAMNT*-1 end SALES from SOP30200 s left outer join RM00101 c on s.CUSTNMBR = c.CUSTNMBR where s.VOIDSTTS = 0 and s.SOPTYPE in (3,4) and year(s.DOCDATE) = 2013 --change year as needed ) d group by d.CUSTNMBR, d.CUSTNAME -- add permissions for DYNGRP go grant select on view_Sales_by_Customer_by_Month to DYNGRP
Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.
Victoria Yudin 
Is it possible to run this as a union all against multiple companies in GP?
LikeLike
Hi Philip,
I don’t see why not. You can follow the same logic as I show in the example here; https://victoriayudin.com/2008/11/10/sql-view-for-crystal-reports-to-show-open-ap-invoices-from-multiple-companies-in-gp/
Let me know if you need additional help on that.
-Victoria
LikeLike
Thanks Victoria however I was able to get them ehat they needed already. It is working out great. Thank you so much.
LikeLike
Excellent! Glad to hear you worked it out.
-Victoria
LikeLike
Victoria
Now I need to add a location filter to this. How can I add locncode of sales?
LikeLike
Hi Philip,
I have a whole bunch of different views available here: https://victoriayudin.com/gp-reports/sop-sql-views/. Perhaps one of them is close enough to what you need that you can take it and modify it as needed?
-Victoria
LikeLike
Hi Victoria,
I am trying to adapt this view to provide the number of orders by customer per month and am having some trouble getting the data to summarize correctly.
select
D.SALES,
D.CUSTNMBR Customer_ID,
sum(case when month(d.DOCDATE) = 1
then d.SALES else 0 end) Jan_Sales,
sum(case when month(d.DOCDATE) = 2
then d.SALES else 0 end) Feb_Sales,
sum(case when month(d.DOCDATE) = 3
then d.SALES else 0 end) Mar_Sales,
sum(case when month(d.DOCDATE) = 4
then d.SALES else 0 end) Apr_Sales,
sum(case when month(d.DOCDATE) = 5
then d.SALES else 0 end) May_Sales,
sum(case when month(d.DOCDATE) = 6
then d.SALES else 0 end) Jun_Sales,
sum(case when month(d.DOCDATE) = 7
then d.SALES else 0 end) Jul_Sales,
sum(case when month(d.DOCDATE) = 8
then d.SALES else 0 end) Aug_Sales,
sum(case when month(d.DOCDATE) = 9
then d.SALES else 0 end) Sep_Sales,
sum(case when month(d.DOCDATE) = 10
then d.SALES else 0 end) Oct_Sales,
sum(case when month(d.DOCDATE) = 11
then d.SALES else 0 end) Nov_Sales,
sum(case when month(d.DOCDATE) = 12
then d.SALES else 0 end) Dec_Sales,
sum(d.SALES) Yearly_Total
from
(select count(SH.SOPNUMBE) as ‘SALES’, SH.CUSTNMBR, SH.DOCDATE– as ‘SALES’
from SOP30200 SH
where SH.SOPTYPE = ‘2’
and SH.VOIDSTTS = ‘0’
and year(SH.DOCDATE) = 2018
and SH.CUSTNMBR = ‘LIVE0021’
group by SH.CUSTNMBR, SH.DOCDATE
) D
group by D.CUSTNMBR , D.SALES
The total number of orders in the result is correct however it is in two rows rather than the single row I expected. Do you mind pointing me in the correct direction?
Thank you,
Kirk
LikeLike
Hi Kirk,
Try taking out the D.SALES from the select list and also from the group by. I think that will fix it. 🙂
-Victoria
LikeLike
Thanks Victoria. Correct as usual.
Kirk
LikeLike
Hi Victoria. I need to use GL POSTING DATE. Here you used Document Date
Is it possible?
LikeLike
Hi Sandeep,
You can replace DOCDATE with GLPOSTDT everywhere to use the GL Posting Date instead.
-Victoria
LikeLiked by 1 person
Thanks Victoria.
But I need to Change Amount also. Bcoz it Showing Document Amount. Instead of Document Amount i need Total Sales wrt to GLPOST Date
LikeLike
Sandeep,
There is no ‘total sales’ field and how that is calculated might differ depending on what you count as part of ‘total dales’. However, you can try using SUBTOTAL instead of DOCAMNT.
-Victoria
LikeLike
I’m Sorry. After Replying you, I tried SUBTOTAL. It’s Working.
But You’re doing a Great Job.
Thanks Victoria
LikeLike
Hi Victoria,
Is it possible to multiple years in this view? I would like to create a pivot table year over year for the 12 periods. Similar to the RM00104 table. I have 15 years of history and would like to be able to go back at least 5 years.
Thank you
Christine
LikeLike
Hi Christine,
Do you want the year added as a column? If so, you can use the following code:
selectd.CUSTNMBR Customer_ID,
d.CUSTNAME Customer_Name,
year(d.DOCDATE) [Year],
sum(case when month(d.DOCDATE) = 1
then d.SALES else 0 end) Jan_Sales,
sum(case when month(d.DOCDATE) = 2
then d.SALES else 0 end) Feb_Sales,
sum(case when month(d.DOCDATE) = 3
then d.SALES else 0 end) Mar_Sales,
sum(case when month(d.DOCDATE) = 4
then d.SALES else 0 end) Apr_Sales,
sum(case when month(d.DOCDATE) = 5
then d.SALES else 0 end) May_Sales,
sum(case when month(d.DOCDATE) = 6
then d.SALES else 0 end) Jun_Sales,
sum(case when month(d.DOCDATE) = 7
then d.SALES else 0 end) Jul_Sales,
sum(case when month(d.DOCDATE) = 8
then d.SALES else 0 end) Aug_Sales,
sum(case when month(d.DOCDATE) = 9
then d.SALES else 0 end) Sep_Sales,
sum(case when month(d.DOCDATE) = 10
then d.SALES else 0 end) Oct_Sales,
sum(case when month(d.DOCDATE) = 11
then d.SALES else 0 end) Nov_Sales,
sum(case when month(d.DOCDATE) = 12
then d.SALES else 0 end) Dec_Sales,
sum(d.SALES) Yearly_Total
from
(select s.DOCDATE, s.CUSTNMBR, c.CUSTNAME,
case s.SOPTYPE
when 3 then s.DOCAMNT
when 4 then s.DOCAMNT*-1
end SALES
from SOP30200 s
left outer join RM00101 c
on s.CUSTNMBR = c.CUSTNMBR
where s.VOIDSTTS = 0
and s.SOPTYPE in (3,4)
) d
group by d.CUSTNMBR, d.CUSTNAME, year(d.DOCDATE)
-Victoria
LikeLike
Thank you Victoria, that worked!
LikeLike
Victoria,
You show a calendar fiscal year. If I set this up for a fiscal year that ends June 30th, would your script pull only the information for the year defined? And if that is the case, how would you tell it to pull July – December for the year and then January to June for the next year?
LikeLike
Anne,
One thing you can do is change the WHERE clause at the end to look for a date range instead of the year. For example, I have it as
year(s.DOCDATE) = 2013You can change it to be
s.DOCDATE between '2015-07-01' and '2016-06-30'The rest of the logic will still work, but you might want to change the order and/or the names of the columns.
-Victoria
LikeLike
Hi Victoria,
Your code works very well for what we are trying to accomplish. Only difference is we wish to have our totals one level lower than your query. We are trying to do “Sales by Customer by Ship To Address ID by month.” We don’t have to have a subtotal by Customer today so I just tried to insert the field “PRSTADCD” (Ship To ID) from the SOP30200 table. Must have done something wrong as could not get data correctly displayed. Is this something you might be able to suggest how to modify so proper results would be displayed? My background is accounting not programming but tinker in SQL when company needs additional information and no one else can help.
Thanks for any assistance you are able to provide and thank you for offering these examples on your website!
Scott
LikeLike
Hi Scott,
Try the following code:
selectd.CUSTNMBR Customer_ID,
d.CUSTNAME Customer_Name,
d.PRSTADCD Ship_To_Address_ID,
sum(case when month(d.DOCDATE) = 1
then d.SALES else 0 end) Jan_Sales,
sum(case when month(d.DOCDATE) = 2
then d.SALES else 0 end) Feb_Sales,
sum(case when month(d.DOCDATE) = 3
then d.SALES else 0 end) Mar_Sales,
sum(case when month(d.DOCDATE) = 4
then d.SALES else 0 end) Apr_Sales,
sum(case when month(d.DOCDATE) = 5
then d.SALES else 0 end) May_Sales,
sum(case when month(d.DOCDATE) = 6
then d.SALES else 0 end) Jun_Sales,
sum(case when month(d.DOCDATE) = 7
then d.SALES else 0 end) Jul_Sales,
sum(case when month(d.DOCDATE) = 8
then d.SALES else 0 end) Aug_Sales,
sum(case when month(d.DOCDATE) = 9
then d.SALES else 0 end) Sep_Sales,
sum(case when month(d.DOCDATE) = 10
then d.SALES else 0 end) Oct_Sales,
sum(case when month(d.DOCDATE) = 11
then d.SALES else 0 end) Nov_Sales,
sum(case when month(d.DOCDATE) = 12
then d.SALES else 0 end) Dec_Sales,
sum(d.SALES) Yearly_Total
from
(select s.DOCDATE, s.CUSTNMBR, c.CUSTNAME, s.PRSTADCD,
case s.SOPTYPE
when 3 then s.DOCAMNT
when 4 then s.DOCAMNT*-1
end SALES
from SOP30200 s
left outer join RM00101 c
on s.CUSTNMBR = c.CUSTNMBR
where s.VOIDSTTS = 0
and s.SOPTYPE in (3,4)
and year(s.DOCDATE) = 2013 --change year as needed
) d
group by d.CUSTNMBR, d.CUSTNAME, d.PRSTADCD
-Victoria
LikeLike
That change for Ship To was PERFECT! Thanks again for your help and sharing all that you do on your web site!
LikeLike
Victoria,
I am trying to get the Customer Sales by GL account designation, we have an Account 4000 for Commercial/Residential Sales, and 4800 for Rentals, …. I used your view_GL_Trx and then Updated the missing Originating Master IDs with Update Joins to JC30001, RM00101 and SV00300. I have some remaining non-allocated transactions in the COG area with IVADJ as the source document. I am not sure what to join these records to.
Do you have a query that gets Revenue by Customer tied to GL Account Number?
LikeLike
Hi Warren,
Transactions with IVADJ as the source are inventory transactions, which are not tied to a customer in GP unless you’re putting in a manual note somewhere that can be linked to.
Unfortunately, there is no one universal query that will give you ‘revenue by customer tied to GL account number’ because how this is accomplished will greatly differ on a number of variables. There are many different ways to use GP and enter transactions into it and often what will work for one company, will not work for others. There are also many other nuances, like what makes up “revenue”, how transactions are posted, etc.
-Victoria
LikeLike
Victoria, I run this report for 2013. And when I sum up the totals for all sales for all customers it is quite a bit different then my sales total when I run the Sales Total from an Income Statement report within GP. Do you know what would be some possible causes of that discrepancy? I appreciate your blog so much it is a data miner’s lifeline sometimes. Keep up the great work.
LikeLike
Hi Zach,
This view only takes onto consideration transactions posted in the SOP module. Typically, your Income Statement report would look at the General Ledger. It’s entirely possible that you have entries from sources other than SOP that go into your GL sales accounts. You can take a look at your sales accounts in the Account Transactions SmartList to get a better idea of where the transactions are coming from. Typically, if you’re looking to ‘reconcile’ the sales GL accounts, you would start from the GL and work backwards to get the details of what makes those up.
Hope that helps,
-Victoria
LikeLike
Victoria,
That makes perfect sense. I am a little confused because I would expect my Sales by customer view to show a lower amount than my Income Statement. But it is quite a bit higher than that. I will dive into it further.
LikeLike
Zach,
Maybe your users are entering credit memos in the Receivables module (as opposed to SOP)? That is a common thing and would explain sales being lower in the GL than in SOP.
-Victoria
LikeLike
Thanks Victoria. I bet that is it. Is there a view you already have to look at that or a Smart List that I can build that will give me that figure of Credit Memos to take off of the figure I get in the Sales by Customer View?
LikeLike
Zach,
I have something that looks at receivables (which includes SOP) by year, maybe that will help get you started? Here is a link: https://victoriayudin.com/2012/01/09/sql-view-for-customer-yearly-totals-in-dynamics-gp/.
-Victoria
LikeLike
Hi Victoria,
I was thinking of trying to adapt this view to show by sales by customer by year but also showing the average time between orders across the life of the account. Do you have any pointers on how to calculate this average?
Thank you,
Kirk Livermont
LikeLike
Hi Kirk,
There is an example of how to calculate the average here: http://stackoverflow.com/questions/16591130/getting-average-time-between-list-of-successive-dates-in-tsql. Hope that helps.
-Victoria
LikeLike
Hi Victoria, can we do similar sum type calculations in SmartList Viewer without creating a SQL view? I.e. can we create calculated fields for each month and subtotal them all up as a summary SmartList? I tried the WHEN statement but I couldn’t get it to work, I kept getting a syntax error. Can I use this same formula in SmartList viewer?
LikeLike
Hi Rob,
You can probably do a lot of this in SmartList, but at some point you might run into a limitation. And sometimes the syntax is a bit of a mystery. I personally much prefer to get all my logic coded in a SQL view, then simply point the SmartList to the view.
If you really want to try it in SmartList Builder, take a look at Devon Southall’s blog for help: http://gpbuilders.wordpress.com/2010/11/01/from-the-forums-sales-report-how-to-get-data-in-columns-in-smartlist-builders/.
-Victoria
LikeLike
Hi Victoria, I am using the above customer sales by month, however I am still unable to join a GL table that has ACTNUMST to this query. I have tried several options, however I can’t find a way to join ACTNUMST I’m looking at all GL tables that have this but haven’t found a common denometer to add to this query. Can you help?
Thanks
Diane
LikeLike
Diane,
This code shows summaries of sales by customer by month fro the SOP module. Within each transaction there are at least 2, but often many more General Ledger accounts used. For each customer, there are about a dozen GL accounts that can be set up. What GL account are you trying to link to?
-Victoria
LikeLike
Victoria,
I am trying to link to the customer GL numbers, however I understand what you are saying, there are a few to each client in our situation . Is there any way to write something that will sum based on customer GL account and the invoices that are associated. Like the current code however sum on certain account numbers?
I look into smart lists such as Financial/Account Summary/Customer Revenue balance, and i am able to see by period and account number the sales for each client, however those “sales” also will include accruals, which I do not want.
Thanks
Diane
LikeLike
Hi Diane,
It sounds like you want a custom report that is very specific to your situation. I think it would be impossible for me to help with something like this in blog comments. I can help with this as a consulting project (for a fee). If you are interested, let me know.
-Victoria
LikeLike
Hi Victoria,
I was wondering if I could join a GL table to this code, to add in the customers GL account numbers? Not sure if these tables are able to join.
Thanks
Diane
LikeLike
Hi Diane,
Can you explain a little more about the logic you’re looking for? Are you looking for an account on the Customer Account Maintenance window? Or on the transactions themselves? If so, which account(s)?
-Victoria
LikeLike
Hi Victoria
I am looking for the cutomer GL account on the transactions themself. Then have it sum on the customer GL account and not just the customer name.
Is that possible?
Thanks
Diane
LikeLike
Diane,
I am not sure what you mean by the ‘customer GL account’?
-Victoria
LikeLike
Hi Victoria,
I think its Segement 1 in the tables. Does that help?
Diane
LikeLike
Hi Diane,
Sorry, not really. 😦 In any case, I think what you’re asking for is most likely doable, but would require a more precise specification. It would also not likely to be something I would publish on my blog, as it would be specific to your company. If you’re interested in having this created for you (or getting help with creating it yourself), we can do this as a consulting project. Please let me know if that’s something you would like to discuss further.
-Victoria
LikeLike
Hi Victoria,
I am writing this query below where I am joining SOP table, RM table and GL table. Its erroring out on GL table join. Are these tables all able to join each other?
select
d.CUSTNMBR as Customer_ID,d.CUSTNAME Customer_Name, coalesce(G.ACTNUMST,”) Account_Number,
sum(case when month(d.DOCDATE) = 1 then d.SALES else 0 end) Jan_Sales,
sum(case when month(d.DOCDATE) = 2 then d.SALES else 0 end) Feb_Sales,
sum(case when month(d.DOCDATE) = 3 then d.SALES else 0 end) Mar_Sales,
sum(case when month(d.DOCDATE) = 4 then d.SALES else 0 end) Apr_Sales,
sum(case when month(d.DOCDATE) = 5 then d.SALES else 0 end) May_Sales,
sum(case when month(d.DOCDATE) = 6 then d.SALES else 0 end) Jun_Sales,
sum(case when month(d.DOCDATE) = 7 then d.SALES else 0 end) Jul_Sales,
sum(case when month(d.DOCDATE) = 8 then d.SALES else 0 end) Aug_Sales,
sum(case when month(d.DOCDATE) = 9 then d.SALES else 0 end) Sep_Sales,
sum(case when month(d.DOCDATE) = 10 then d.SALES else 0 end) Oct_Sales,
sum(case when month(d.DOCDATE) = 11 then d.SALES else 0 end) Nov_Sales,
sum(case when month(d.DOCDATE) = 12 then d.SALES else 0 end) Dec_Sales,
sum(d.SALES) Yearly_Total
from(select s.DOCDATE, s.CUSTNMBR, c.CUSTNAME, s.DOCAMNT as SALES, coalesce(G.ACTNUMST,”) Account_Number
from SOP30200 s left outer join RM00101 c
on s.CUSTNMBR = c.CUSTNMBR
LEFT OUTER JOIN
RM30301 DH
on dh.CUSTNMBR = c.CUSTNMBR
and DH.DEX_ROW_ID = c.DEX_ROW_ID
LEFT OUTER JOIN
GL00105 G
ON DH.DSTINDX = G.ACTINDX
and DH.DEX_ROW_ID = G.DEX_ROW_ID
where s.VOIDSTTS = 0 and s.SOPTYPE = 3
and year(s.DOCDATE) = 2013 –change year as needed
and (s.SOPNUMBE not like ‘0i%’
OR s.SOPNUMBE not like ‘%a’)
) d
group by d.CUSTNMBR, d.CUSTNAME, coalesce(G.ACTNUMST,”)
LikeLike
Hi Diane,
I am little confused by what you’re doing here. Why are you linking on DEX_ROW_ID? That field should never be used for linking.
And why are you linking to the Receivables distribution history table (RM30301) from SOP and why on just the Customer Number? Why not use the SOP distributions table (SOP10102) instead and link to it by transaction number and type?
-Victoria
LikeLike
OK! Thanks! I’ll try that!
Thank you!
LikeLike