JustinBMak
asked on
SRSS - IF statements in Report Builder 3.0 to sum of number of items in each order
Hello:
I am trying to generate a report in Report Builder 3.0 for SQL 2008 for a client.
I am looking for something very specific, but having a hard time finding it.
I have 99% of my report built, however the client needs to know the following. The report is a Daily report for the previous day of orders received. It is broken down my Order #, Customer, SKU, Description and Quantity.
I currently have a table on the report that takes the data below and sums of the # of orders that came (ex: 500) and another table that shows the total quantity of items (in those 500 orders) received (ex: 1,500 items). The client wants to know, out of those 500 orders, how man total orders have a Quantity of 1 (ex: 550), how many orders have quantity of 2, etc. etc. based off the Details listed below on the report. Call it a "summary" area at the top of the report for the info listed below that area on the report.
Does this make sense to anyone?
I am trying to generate a report in Report Builder 3.0 for SQL 2008 for a client.
I am looking for something very specific, but having a hard time finding it.
I have 99% of my report built, however the client needs to know the following. The report is a Daily report for the previous day of orders received. It is broken down my Order #, Customer, SKU, Description and Quantity.
I currently have a table on the report that takes the data below and sums of the # of orders that came (ex: 500) and another table that shows the total quantity of items (in those 500 orders) received (ex: 1,500 items). The client wants to know, out of those 500 orders, how man total orders have a Quantity of 1 (ex: 550), how many orders have quantity of 2, etc. etc. based off the Details listed below on the report. Call it a "summary" area at the top of the report for the info listed below that area on the report.
Does this make sense to anyone?
ASKER
They want to know how many orders have a total quantity of 1, 2, etc. based off the order.
So I'm pulling the order # and the total quantity of what's in that order.
So if order AAA had 3, Order BBB has 5 and order CCC has 3
They want to see:
QTY - Total
3 - 2
5 - 1
Make sense?
So I'm pulling the order # and the total quantity of what's in that order.
So if order AAA had 3, Order BBB has 5 and order CCC has 3
They want to see:
QTY - Total
3 - 2
5 - 1
Make sense?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Using that formula, the IIF portion will output 1 for any order that has 2 and only 2 items on it, and zero for all others. The SUM portion then adds up all the computed values. If you include additional columns orders with 1, 3, 4, 5, more than 5, you should be able to add up those results to get the total number of orders.
The answers provided do answer the request based on the information provided. Deleting the question because the requester hasn't come back and confirmed all results should not be a reason for deletion.
If it is the total quantity, you can simply use this type of formula with your existing Quantity field:
=SUM(IIF(<Quantity Field>.Value = <quantity you are concerned with>, 1, 0))
If it is distinct items, you would need to add a column to your query to calculate that and then use the same formula above.