Report Builder, prompt filter, how to ignore the filter?

Question

Hi,

I'm looking at the report builder from 2005. I know I can prompt a filter so the user can filter the data themselves. But what if they don't want that? how can I set the filter optional. so either they see everything or they use the filter.

Now I only can have a filtered report.

Answer 1

Firs, you have to set the Default values for all your parameters. For numeric params it may be 0, and for varchar - NULL or empty string. For multivalued params I'd say to just include all available values. So lets say you have report params:
UserIDParam, int, default value 0
UserNameParam, varchar, default value NULL
UserRelatedIDList, multivalue int, default value (0, 1, 2, 3, 4)
Then in query you just use thouse params in filters:

SELECT U.ID, U.Name, U.Phone, U.Adress, RU.ID, RU.Name, RU.Phone, RU.Adress
FROM Users U
LEFT JOIN RelatedUsers RU ON U.ID = R.UserID
LEFT JOIN Users ON R R.RelatedUserID = ON RU.ID
WHERE ((ISNULL(@UserIDParam, 0) = 0) OR (U.ID = @UserIDParam)) AND
  ((ISNULL(@UserNameParam, '') = '') OR (U.Name = @UserNameParam)) AND
  ((0 IN (@UserRelatedIDList) AND R.UserID IS NULL) OR 
      (R.UserID IN (@UserRelatedIDList)))

See Chris Hays's Reporting Services Sleazy Hacks Weblog - "All" Parameter Value
Chris Hays's Reporting Services Sleazy Hacks Weblog - "All" Parameter Value Redux
SQLCenral Forum - Default Parameter Value in the Reporting Services

Now by default filters will not filter out any data.
What you also can do is to hide report parameters area in report viewer. If you providing access to your reports with ReportViewer, or using URL, you can configure Toolbar, Parameters and other report settings:

http://servername/reportserver?/YourReportName&rs:Command=Render&rs:Format=HTML4.0&rc:Parameters=false

Setting Parameters to:
true - will show parameters panel,
false - will hide parameters panel,
Collapsed - will hide parameters panel, but can be toggled by the end user.
See Using URL Access Parameters