Expression in SSRS to put filter on a group


I am building a report to track status of a Sales order. I have ord_status, ord_qty, picked_qty, invoiced_qty, cancel_qty columns. Here ord_qty is the orginal total order quantity for all the items for that PO (ord_qty (of all products) = picked_qty + invoiced_qty + cancel_qty). Ord_status could be 'P' (for picked), 'I' (for invoiced) and 'C' (for Cancelled).

Now there may be situations in which orders are split shipped which means some goods are shipped (and invoiced) and some are still in pick status.

If the order is completely shipped then I dont want to include that in my report. Report is in a drill down format so channel is expanded to customer, then to collection and then above four columns (or in this case rows) appears.

I thought of putting a filter on details group by writing an expression something like:

Sum(Fields!Invoiced_Qty.Value) + Sum(Fields!Cancel_Qty.Value) != Ord_qty

I have to put it on the lowest level of the drill down because I want to check if SUM of invoiced_qty and Cancel_qty "for each PO" is not equal to Ord_qty of that PO.

Above expression is incorrect. Can't I add 2 Summations in an expression ? If I put this filter on details group will it only filter out rows for that group ?

Thanks !
Who is Participating?
stelth240Connect With a Mentor Commented:
In the details group, you can add a group expression on the PO number.  Then you can use Sums correctly.  Does the data look like this?

po_num	product	ord_qty	invoiced_qty	cancel_qty
1001	1	200	30		10
1001	2	200	60		0
1001	3	200	60		20
1001	4	200	20		0

Open in new window

If so, you can do Sum(Fields!Invoiced_Qty.Value) + Sum(Fields!Cancel_Qty.Value) != First(Ord_qty)

I'm confused about what you said in your post: "ord_qty (of all products) = picked_qty + invoiced_qty + cancel_qty".  Your filter expression does not look similar to this.  Is Invoiced + Cancelled really = Ordered?

Hope this helps.


You can put two sum function. My approach to this kind of tasks is to split the logic and put it into several new columns. This way I see if they work individually and then I try to combine it into one and put it where I want it.

I hope this approach will help you in solving your task
pratz09Author Commented:
Thanks a lot !
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.