Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


finding the maximum of a sum in SSRS 2012


finding the maximum of a sum in SSRS 2012

Author
Message
tdiroff
tdiroff
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 66
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
Attachments
SSRS2012 Max of Sums.bmp (5 views, 4.00 MB)
pietlinden
pietlinden
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2178 Visits: 12518
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
tdiroff
tdiroff
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 66
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
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14957 Visits: 38958
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

tdiroff
tdiroff
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 66
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!!!
tdiroff
tdiroff
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 66
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>
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14957 Visits: 38958
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

tdiroff
tdiroff
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 66
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
tdiroff
tdiroff
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 66
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search