Share
ReportI'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
You’re offline. This is a read only version of the page.
195
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
Finally, got the information I needed; thanks for everyone's help.
Ben,
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.
It would be GP's SmartList/ Excel Report builder, Which is different than what Victoria wrote her code in right?
Ben,
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).
Yea it looks like Victorias written what I need, just need to get familiar with report builder. Thanks everybody for your help.
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.
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.
Ben,
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.
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.
#1
André Arnaud de Cal...
292,516
Super User 2025 Season 1
#2
Martin Dráb
231,407
Most Valuable Professional
#3
nmaenpaa
101,156
All responses (
Answers (
