Let’s say you have a report that executes a dataset which produces fields for your report. You define that dataset like this:
select firstname, lastname, middlename from name_table
You then pull the columns from that table and load the data into your report fields (First Name, Last Name, and Middle Name).
Now, let’s say you want to make the SQL more complex by adding filtering with a WHERE statement like this:
select firstname, lastname, middlename from name_table where lastname = 'DOE'
Now comes the tricky part.
Let’s say that the report is executed from an application that has a search form. That form has 25 controls on it used for filtering. The client enters or chooses their filtering from those 25 controls and clicks a search button. The results are returned to a grid. The application then provides a PRINT button that executes your SSRS report that displays the same data provided in their grid.
You can do this two ways.
1) Define 25 parameters in your report and have your dataset interrogate each parameter in your WHERE clause. But what if every month, the application adds or removes controls from their search form? Now you have to change the report too!
2) Have the application pass the SQL statement used to get and populate their grid as a parameter to your report. You then define that one parameter in your report like this:
Then in your Dataset Properites, you must do two things:
a) In the Query Tab, inside the Query textbox, use the EXEC statement to execute the parameter that contains the SQL like this:
b) And lastly, you must manually define the fields in the Fields tab that are needed for the report like this:
As long as the data displayed in your report (See below) never changes, although the filtering in the application may change, you won’t need to change your report again. 
