Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

finding the maximum of a sum in SSRS 2012 Expand / Collapse
Author
Message
Posted Friday, January 24, 2014 1:24 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 1:20 PM
Points: 15, Visits: 63
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




  Post Attachments 
SSRS2012 Max of Sums.bmp (4 views, 4.70 MB)
Post #1534633
Posted Monday, January 27, 2014 12:43 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Yesterday @ 3:59 PM
Points: 696, Visits: 4,466
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
Post #1535168
Posted Tuesday, January 28, 2014 12:58 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 1:20 PM
Points: 15, Visits: 63
Hi Pieter

Thank you for thinking about this

Here is some sample data for Product A

Invtid Case_ship Trandate FiscYr PerPost week_period day_period
A 10 1/1/2104 2014 1 1 1
A 3 1/1/2014 2014 1 1 1
A 50 1/2/2104 2014 1 1 2
A 30 1/3/2014 2014 1 1 3
A 20 1/9/2104 2014 1 2 2
A 5 1/9/2104 2014 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
Post #1535593
Posted Tuesday, January 28, 2014 1:06 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:50 AM
Points: 12,876, Visits: 31,783
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1535597
Posted Tuesday, January 28, 2014 1:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 1:20 PM
Points: 15, Visits: 63
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!!!
Post #1535602
Posted Tuesday, January 28, 2014 1:14 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 1:20 PM
Points: 15, Visits: 63
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>
Post #1535605
Posted Tuesday, January 28, 2014 1:28 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:50 AM
Points: 12,876, Visits: 31,783
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1535616
Posted Tuesday, January 28, 2014 1:55 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 1:20 PM
Points: 15, Visits: 63
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
Post #1535628
Posted Thursday, January 30, 2014 11:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 1:20 PM
Points: 15, Visits: 63
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
Post #1536505
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse