I have created a Smartlist that gives me the sales per customer per Months. How can I get the sales in the same report for the same months but from the previous year?
*This post is locked for comments
You could make a SQL view and base your SmartList on the view.
To me, the best option is to create your view on the SQL management studio taking into consideration all the required data you want, then get the view published to Smart list builder as mentioned above by Mr. Jeff.
Although, you can still use a various functions in Smart list builder to manipulate the data as requested. A list of functions (including Date functions) is illustrated in the knowledge base article below;
List of the Functions that You can Use for Calculations in SmartList Builder, Excel Report Builder, Navigation List Builder, and Extender in Microsoft Dynamics GP
In addition, you may refer to the article published by Dynamics GP Support and Services blog on the link below;
Calculated Fields in Extender and the Builders
Hope this helps out in your inquiry,
Hi,
Like Jeff and Mahmoud suggested, a view is probably your best bet. Routinely, however, I have to create SmartLists I can send to other people, so I need to create it inside SLB. Below is an example of the SQL code that you could use in your calculated field to find the first day in July of the previous year:
select DATEADD(m, 6, DATEADD(yy, DATEDIFF (yy, 0, DATEADD(yy, -1, GETDATE())), 0))
Replace 'GETDATE' with your data field. Use your date field, parse the month out of it and then subtract one from the resulting 'month' number.
Change the '6' as follows to get the different months.
January = 0
February = 1
March = 2
April = 3
May = 4
June = 5
July = 6
August = 7
September = 8
October = 9
November = 10
December = 11
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.
As AI tools become more common, we’re introducing a Responsible AI Use…
We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…
These are the community rock stars!
Stay up to date on forum activity by subscribing.
