Adding a where condition to Reporting Services report

I have a report with a dataset of:

select Field1, Field2, Field3, ... FieldN from records inner join customers on customers.customer_id = records.customer_id

In reporting services, how do I programatically add - "where Field3 = 'SomeData'"

or do I have to change my SQL to - select Field1, Field2, Field3, ... FieldN from records inner join customers on customers.customer_id = records.customer_id where Field3 = @Field3Parameter

and set my parameter at run time?

I'd like to let my cu开发者_开发问答stomers have the ability to select from well over 100 fields and don't want to have to set each as a parameter.

Thanks in advance!

---

"I'd like to let my customers have the ability to select from well over 100 fields and don't want to have to set each as a parameter."

How are the customers selecting the fields currently? If this is a set-in-stone requirement, you're pretty much stuck with filters or parameters (as Cory pointed out) or building dynamic SQL, which poses its own problems.

One other option: depending on how complicated the business logic is, you could go with a couple of multi-select parameters that contain related items, and implement the checks in the stored proc. For example, have a multi-select parameter like below, and in the stored proc pull out the options the customer has selected and implement them in the WHERE clause. You get the same result as Cory has mentioned, but cut down on parameters.

Multi-select Parameter
-----------------------
Include Widgets
Include Completed Sales
Include West Coast 
(lots more options) 

---

You could either do it in the SQL, parameterized, as you have suggested, or as Filter conditions in your Tablix. I prefer the parameterized SQL, setting the @parameter to be a report parameter value, as it will perform much better. I usually follow this pattern:

SELECT
    c.Field1
    , c.Field2
    , r.Field3
    , ...
FROM
    records r
JOIN
    customers c ON c.customer_id = r.customer_id
WHERE
    ((@Field1Parameter IS NULL) OR (c.Field1 = @Field1Parameter)) AND
    ((@Field2Parameter IS NULL) OR (c.Field2 = @Field2Parameter)) AND
    ((@Field3Parameter IS NULL) OR (r.Field3 = @Field3Parameter)) ...

This allows you to leave your report parameters nullable or have an "All" option with a default value of NULL. I realize you don't want to have to create parameters, but if you use 2008 reports it will create your list of parameters for you automatically from your SQL. You still have to create the report parameters though, I don't think there's any way around that.