SSRS Report Builder 3.0 - Filter Table Based on Checkbox

I am using Report Builder 3.0 to create and SSRS report.  I want to filter the table.  I have a Paramater PullOrder that is a checkbox.

I want to filter based on this checkbox.  The field I want to filter is Calculated_PullOrder.  This field has only two values, NoPull and Pull,

If the checkbox = 1, I only want NoPull.  If the checkbox = 0 I want all values.

I tried
=IIF(Parameters!PullOrder.Value=1,"NoPull",Contains("Pull"))
=IIF(Parameters!PullOrder.Value=1,"NoPull",InStr(Fields!Calculated_PullOrder.Value,"Pull"
=IIF(Parameters!PullOrder.Value=1,"NoPull",right(Fields!Calculated_PullOrder,4)="Pull")

None of these solutions worked.  

How can I filter to return only NoPull if checkbox = 1 otherwise return all records.

Thank you in advance for your assistance
LVL 1
ScamquistAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Koen Van WielinkBusiness Intelligence SpecialistCommented:
When using parameters to filter records, you should do this on the database end, not the report end.
Add a parameter to your SQL query/Stored procedure that filters the records based on the checkbox:

Create procedure yourProcedure

@noPull smallint

as

IF @noPull = 1
Begin

Select yourColumns
from yourTable
where calculated_pullorder = 'NoPull'

End
Else
Begin

Select yourColumns
from yourTable

End;

Open in new window

ScamquistAuthor Commented:
Unfortunately, I am using Epicor ERP system.  The data tables are generated then the report is run.  The options to include Pull and NoPull are selected at runtime and kept in another table.  While it would be preferred to do this within the query, I need a method to filter the table based on the runtime parameter.
Koen Van WielinkBusiness Intelligence SpecialistCommented:
Hmm, that makes it a whole lot more complicated. As far as I know you can't filter out records after the query is executed. The only way I can think of is to use this parameter to set the visibility of the records you want to see, and also to adjust any aggregates that are affected. Meaning that if you have aggregates in your report and you set the visibility for some records to false, these records need to be excluded from the aggregates as well.
A final option might be to look at VBA, but I'm not experienced enough in that area. But perhaps you could write some VBA code that filters the records.
ScamquistAuthor Commented:
I rewrote the query using standard SQL and not Epicor BAQ.  This way I was able to use SSRS parameters and table filters

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ScamquistAuthor Commented:
The suggested solution did not help.  I changed the method of query.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.