Expression in SSRS to put filter on a group

Posted on 2011-10-12
Last Modified: 2012-05-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 ?

Thanks !
Question by:pratz09
    LVL 10

    Expert Comment


    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
    LVL 7

    Accepted Solution

    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.


    Author Closing Comment

    Thanks a lot !

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now