Link to home
Start Free TrialLog in
Avatar of JustinBMak
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?
Avatar of Shaun Kline
Shaun Kline
Flag of United States of America image

Are they looking for distinct items (i.e. they want to know if someone ordered just bolts versus someone that ordered both nuts and bolts) or are they only concerned with the total quantity (i.e. someone ordered just one bolt versus someone that ordered 3 bolts)?

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.
Avatar of JustinBMak
JustinBMak

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?
ASKER CERTIFIED SOLUTION
Avatar of Shaun Kline
Shaun Kline
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi there:

So it is summing up or something. I entered in the above command, and it shows 62.

User generated image


User generated image
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.