[Last Call] Learn how to a build a cloud-first strategyRegister Now


Expression in SSRS to put filter on a group

Posted on 2011-10-12
Medium Priority
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

ID: 36956224

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

Accepted Solution

stelth240 earned 2000 total points
ID: 36974313
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

ID: 36974361
Thanks a lot !

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

829 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