Expression in SSRS to put filter on a group
Posted on 2011-10-12
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 ?