I have added a condition to only display the qty on hand if the binnum is not SHIP. This seems to work fine if there is one line, however, if there are multiple lines with only one SHIP binnum, it does not exclude that one line. Any ideas on why it is acting this way. Example shown below.
Below, the 44 should be 40 by excluding the one line with qty of 4 which has a “SHIP” binnum.
Hi Kyle - I am guessing that SSRS is evaluating your statement as false because the first value is not SHIP. I believe you would need code to loop through the binnum values and keep track of a variable to arrive at 40 in that scenario.
It’s not very slick, but something that might work… add a hidden table to the report that excludes the parts with binnum=SHIP, with an aggregate/sum field for the on hand qty. Then you can name that table field and reference it wherever you need in the report.
If you don’t want to see items from the SHIP bin, exclude it from the dataset in the first place (in the table filter in BAQ) rather than filter it out in a report expression.
If you want the option of choosing between including SHIP or not, same answer. Just have two filters (@IncludeShipParameter = true or BinNum <> ‘SHIP’)
John- Thanks for the reply, can you elaborate on how to add a table filter in BAQ to be applied to a report style?
Could you elaborate on how to add a hidden table to the report?
Query Builder tab in BAQ Designer. Note the whole list of icons and tabs towards the bottom left of the screen. Use them to add a new “criteria” to your table. This works exactly like a WHERE clause in a SQL script.
After you add the table, open the Tablix Properties and you can select Hide under the Visibility tab. You can also hide individual rows or columns.
I’m familiar with BAQ’s. How do you apply a baq to the actual report though?
In the expression try something like:
=sum(iif(Fields!Partbin_BinNum.Value = "SHIP",0,Fields!Partbin_OnhandQty.Value))
You might need to make the 0 in the proper type, like 0.0 Or 0m if it is a decimal
You can access BAQ’s through the API, though you might want to ignore everything I’ve said about that if you don’t know what I’m talking about.
If you’re doing direct database access, then just add the WHERE clause to the report dataset query.