Report Parameters

[goodspeed7]

I know this is easy but i just have no clue.

I have a report parameter that has a drop down list and lets the user pick what project they want to query. Is there a way to put an option "All" in so they could get the totals for all projects??

 

[dragonwell]

I do this by first making the query's parameter optional. If you use a sp, you could do something like:

CREATE PROCEDURE rpt_ProjectReport

@ProjectID INT = NULL

AS

SELECT * FROM  Projects 
WHERE
  ProjectID = 
  (CASE WHEN @ProjectID IS NULL THEN ProjectID ELSE @ProjectID END)

Then for your list of ValidValues for projectID, something like

SELECT NULL, '(All Projects)'
UNION
SELECT ProjectID, ProjectName FROM Projects

That gives you a result set like
NULL, (All Projects)
1, Project A
3, Project C

etc.,

So when "(All Projects}" is selected, a NULL value for ProjectID is submitted to main report SP. The CASE statement in the where clause allows the projectID to be optional - if it's null you get all projects, otherwise only the ones with the id submitted.

Hope that helps.