Issue incorporating third parameter in report

I'm having issues incorporating a third parameter into my existing 2 parameters. I currently have them all defined and referenced in all queries, but the problem is with the criteria I think. I can get 2 of them to work together either 1&2 or 2&3 (by removing the other one), but not 1,2&3 at the same time. I'm using or conditions. Is there something I'm missing? thanks.

Like I said all my datasets referrence all of these parameters 1,2&3. On another note, when I'm in the preview area and I choose a value from the third parameter and the 2nd and click view, the screen just blinks (nothing will retrieve unless I choose a parameter for 1).

Also, when I deploy it on the web server and I select values for 2&3 and click view, it says to select a value for 1. I went back into my criteria for each dataset and check the parameters and they all look correct. Here is a sample of the condition I'm using for each dataset I'm retrieving:

where (X=1 and Y=2) or (X=3 and Y=2). Logically thinking, this is what I think it should be.

Thanks in advance for the help!

Hi,

Set the parameters to allow null values and change your query:

="select some_columns from your_table where some_conditions and " & iif(Parameters!Var1.Value is Nothing,"( col3=" & Parameters!Var3.Value & " and col2=" & Parameters!Var2.Value & " )" ,"( col1=" & Parameters!Var1.Value & " and col2=" & Parameters!Var2.Value & " )" )

To preview if this works as expected just copy the statement to a textfield and verify it looks like you want it to look
|||

I keep getting unclosed quotation mark near the character string ')' somewhere on the second line.

To be more specific, Here is the exact way may query looks:

SELECT CONVERT(text, CASE WHEN fra.percent_planned_complete LIKE '[0-9].[0-9][0-9]~%' ESCAPE '~' OR
fra.percent_planned_complete LIKE '[0-9][0-9].[0-9][0-9]~%' ESCAPE '~' OR
fra.percent_planned_complete = '100.00%' THEN cast(cast(ROUND(CAST(stuff(fra.percent_planned_complete, len(fra.percent_planned_complete), 1, '')
AS DECIMAL(5, 2)), 0) AS int) AS varchar(30)) + '%' ELSE fra.percent_planned_complete END) AS FORMATTED_Planned_PCT_COMPLETE,
CONVERT(text, Percent_Complete_To_Date + '%') AS FORMATTED_PCT_COMPLETE, PROJECTMANAGER, REPORTDATE, CAPITALPROJECTNUMBER,
PROJECTNAME
FROM FRA
WHERE (PROJECTNAME = @.proj_name) AND (REPORTDATE = @.report_month) OR
(CAPITALPROJECTNUMBER = @.cap_num) AND (REPORTDATE = @.report_month) and " & iif (Parameters!proj_name.Value is null,"( REPORTDATE=" & Parameters!@.report_month.Value & " and CAPITALPROJECTNUMBER=" & Parameters!@.cap_num.Value & " )" ,"( PROJECTNAME=" & Parameters!@.proj_name.Value & " and REPORTDATE=" & Parameters!@.report_month.Value & " )" ).

Also when I run just:

"SELECT REPORTDATE, CAPITALPROJECTNUMBER,
PROJECTNAME
FROM FRA
WHERE (PROJECTNAME = @.proj_name) AND (REPORTDATE = @.report_month) OR
(CAPITALPROJECTNUMBER = @.cap_num) AND (REPORTDATE = @.report_month) and " & iif (Parameters!proj_name.Value is null,"( REPORTDATE=" & Parameters!@.report_month.Value & " and CAPITALPROJECTNUMBER=" & Parameters!@.cap_num.Value & " )" ,"( PROJECTNAME=" & Parameters!@.proj_name.Value & " and REPORTDATE=" & Parameters!@.report_month.Value & " )" ).

I get maximum length is 128 and incorrect syntax by the Select

I keep trying to resolve this, but can't seem to do it.