Announcements
No record found.
I'm trying to do some inventory analysis, is there a way to create a smart list or report that has item sales listed by year by month?
*This post is locked for comments
Ben,
Do you have access to SmartList/Excel Report Builder?
You could create a SmartList with Year and Month Calculated fields and group on Year and Month with Summary on Extended Amount, Quantity, or other measure.
You could also do this with standard SmartList, export to Excel, establish the Year and Month columns and use a Pivot Table to analyze.
Just make sure whatever tool is used that you account for voids and returns and anything else that may impact your report totals.
If you have SmartList Builder or another reporting tool, I have a bunch of SQL code posted to show sales data a few different ways if you are using the SOP module: victoriayudin.com/.../sop-sql-views. Hopefully some of these will help with what you're looking for.
In GP, there is an Item Period History by Site/Item report found under Reports | Inventory | History. Select the report called "Item Period History" and see how that works for you.
I'm trying to get access to the report builder and may have it on friday. Mike, when you say establish do you mean manually input the month and year? I ask because I was doing what David recommended, but having to input all of the data was getting to be too time consuming.
You can create 2 calculated field in SmartList/Excel Report Builder for this: "CalcMonth" and "CalcYear" (or whatever you want to call them).
CalcMonth:
DATEPART(MM,DataFieldGoesHere)
CalcYear:
DATEPART(YYYY, DataFieldGoesHere)
This will create the necessary "Year" and "Month" numbers for creating summary reports or Pivot Table analysis (no extra data entry).
While I haven't reviewed Victoria's SQL queries, you may be time and resource commitment ahead to review these with the IT team as she may have already done some heavy lifting already in these queries.
Yea it looks like Victorias written what I need, just need to get familiar with report builder. Thanks everybody for your help.
When you mention "report builder", are referencing SQL Reporting Services "Report Builder" or GP's SmartList/Excel Report builder?
If using SQL Reporting Services "Report Builder", there is a report wizard that works awesome with prebuilt queries like what Victoria has. You can basically take the "select" portion of a query and embed this in the report and then work through the wizard, grouping by the "Month" and "Year" fields in the query (assuming some items in the query here but it is super easy once you get used to it).
It would be GP's SmartList/ Excel Report builder, Which is different than what Victoria wrote her code in right?
What I recommend is using my code to create a SQL view, then pointing SmartList/Excel Builder at that view. Here is a blog post going through the steps of doing this with SmartList Builder: victoriayudin.com/.../how-to-use-a-sql-view-in-smartlist-builder.
Finally, got the information I needed; thanks for everyone's help.
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.
Congratulations to our 2026 Super Stars!
Thanks to all of our 2025 Community Spotlight stars!
These are the community rock stars!
Stay up to date on forum activity by subscribing.
