January 24, 2014 at 1:24 pm
Hi
For each part number I am trying to find out in which week of a month did the largest daily shipment occur... so using report builder 3.0 in SSRS 2012 I can find daily, weekly and monthly totals for the units shipped but I am having a hard time thinking through the logic to find the daily max for each week.
in my data I can have multiple orders/shipments for the same product on the same day.
The ultimate goal is to set inventory levels
So..
In my matrix report I have
Row groups:
Classid => Product class
InvtID => Item Part Number
Column Groups:
FiscYr=> Fiscal Year
PerPost => Month or period the transaction occurred
Week_period => the week the transaction occurred
Day_period => The day that the transaction occurred
The aggregations are
Sum(case_shipped)
Max(case_shipped)
The Sum(case_shipped) is working as desired but the Max(case_shipped) is picking out the max amount shipped on any one order when looking at the week, month or fiscal period and that is not what I need.
I have attached a screenshot since a picture is worth more than my words.
I would very much appreciate any thoughts on this. I am guessing that the solution has something to do with using the inscope function but I can't wrap my head around how to use it
Thank you
Tom D
January 27, 2014 at 12:43 pm
Could you post some data as a UNION query or a CREATE TABLE and then some INSERTs... (Not millions, just enough to get an idea of what you're dealing with.) I'm thinking this might be most easily done by a Stored Procedure with a CTE, but it's hard to tell without seeing some data.
(Maybe the ADD is just damaging my brain!)
Pieter
January 28, 2014 at 12:58 pm
Hi Pieter
Thank you for thinking about this
Here is some sample data for Product A
InvtidCase_shipTrandateFiscYrPerPostweek_periodday_period
A10 1/1/21042014 1 1 1
A3 1/1/20142014 1 1 1
A50 1/2/21042014 1 1 2
A30 1/3/20142014 1 1 3
A20 1/9/21042014 1 2 2
A5 1/9/21042014 1 2 2
A 20 1/10/2014 2014 1 2 3
A 60 1/10/2104 2014 1 2 3
On 1/1/2104 I shipped a total of 13 cases
On 1/2/2104 I shipped a total of 50 cases
On 1/3/2104 I shipped a total of 30 cases
On 1/9/2014 I shipped a total of 30 cases
On 1/10/2014 I shipped a total of 80 cases
On 1/9 I shipped a total of 25 cases
I would like to show that in week 1 the maximum number of cases shipped on any day is 50 (1/2/2014)
I would like to show that in week 2 the maximum number of cases shipped on any day is 80 (1/10/2104)
I would also like to show that in perpost (monthh) 1 the maximum number of cases shipped on any day is 60 (1/10/2104)
Does this help a little?
Thanks
Tom
January 28, 2014 at 1:06 pm
based on your data, and some substantial cleanup of what you pasted,(year 2104) i get a max of 60 shipped for week 2:
;WITH MyCTE([Invtid],[Case_ship],[Trandate],[FiscYr],[PerPost],[week_period],[day_period])
AS
(
SELECT 'A',CONVERT(int,'10'),convert(date,' 1/1/2014'),'2014',' 1',' 1',' 1' UNION ALL
SELECT 'A','3',' 1/1/2014','2014',' 1',' 1',' 1' UNION ALL
SELECT 'A','50',' 1/2/2014','2014',' 1',' 1',' 2' UNION ALL
SELECT 'A','30',' 1/3/2014','2014',' 1',' 1',' 3' UNION ALL
SELECT 'A','20',' 1/9/2014','2014',' 1',' 2',' 2' UNION ALL
SELECT 'A','5',' 1/9/2014','2014',' 1',' 2',' 2' UNION ALL
SELECT 'A','20','1/10/2014','2014','1','2','3' UNION ALL
SELECT 'A','60','1/10/2014','2014','1','2','3'
)
SELECT max(Case_Ship),datepart(week,Trandate) FROM MyCTE group by datepart(week,Trandate) order by datepart(week,Trandate) ;
Lowell
January 28, 2014 at 1:10 pm
I apologize for the data... I tried to make it readable by putting in columns but I lost all of that when it was posted
arghhhh!!!
January 28, 2014 at 1:14 pm
I should also add that
"I would also like to show that in perpost (month) 1 the maximum number of cases shipped on any day is 60 (1/10/2104)"
should be 80 not 60 <sigh>
January 28, 2014 at 1:28 pm
the fix is to group the data by day to get daily totals when more than one shipement exists per day, then get the max per week or month, depending on which total you wanted.
;WITH MyCTE([Invtid],[Case_ship],[Trandate],[FiscYr],[PerPost],[week_period],[day_period])
AS
(
SELECT 'A',CONVERT(int,'10'),convert(date,' 1/1/2014'),'2014',' 1',' 1',' 1' UNION ALL
SELECT 'A','3',' 1/1/2014','2014',' 1',' 1',' 1' UNION ALL
SELECT 'A','50',' 1/2/2014','2014',' 1',' 1',' 2' UNION ALL
SELECT 'A','30',' 1/3/2014','2014',' 1',' 1',' 3' UNION ALL
SELECT 'A','20',' 1/9/2014','2014',' 1',' 2',' 2' UNION ALL
SELECT 'A','5',' 1/9/2014','2014',' 1',' 2',' 2' UNION ALL
SELECT 'A','20','1/10/2014','2014','1','2','3' UNION ALL
SELECT 'A','60','1/10/2014','2014','1','2','3'
),
GroupedData
AS
(
SELECT
SUM([Case_ship]) AS [Case_ship],
[Trandate]
FROM MyCTE
GROUP BY [Trandate]
)
SELECT max(Case_Ship),datepart(week,Trandate) FROM GroupedData group by datepart(week,Trandate) order by datepart(week,Trandate) ;
Lowell
January 28, 2014 at 1:55 pm
Hi Lowell
Thank you very much for looking at this and cleaning up my data. Please excuse my naive question that I am about to pose.
Am I correct that with your query I will be be creating a new dataset that operates at the "day" level instead of the "order" level that is currently the lowest detail level?
If so, is there no way to accomplish this from within the current report builder format as I can see the daily sums of shipments I "just" need to determine the max.
I fear that my novice report builder level is on display here
Thanks again
Tom
January 30, 2014 at 11:51 am
Hi
I was able to find a solution thanks to the prodding by Lowell and Pieter
I built a new dataset in report builder using the same table as before but tried out the "Group and Aggregate" function and for the case_ship field I chose "Sum" as my aggregate.... In essence this gave me a very cool and easy daliy sum of the shipped cases.
When I used this new dataset and built my matrix report I was able to easily get the max daily shipment for each product by week, month and year.
Tom
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply