Hi, I have two parameters in report named ID and Name both with blank and multi values property enabled. Report query is based on both the parmeters with or condition: Select * from table where ID= @ID or name = @name. Both the parameters are populated from other datasets with the queries to retreiving id's and names. It works fine if i select at last one value in each parameter. But if i keep any of the parameter blank with some value in other, report do not show anything. Is it possible to write the expression for something like below - if the value of parmater is blank, then return value would be "-"/"nothing"if the value is not blank, the return value would be the value of dataset (dataset with output of id/name (select distinct id/name from table)) field Please let me know if there is an work around for this. Any help is appreciated. Thanks, Punia

Hi There Thanks for your posting. did you try like this Select * from table where ID in (@ID) or '' in (@ID) or name in (@name) or '' in (@name) Select * from table where ID in (@ID) or '' in (@ID) or name in (@name) or '' in (@name) Many thanks Syed Qazafi Anjum

Hi There Thanks for your posting. did you try like this Select * from table where ID in (@ID) or '' in (@ID) or name in (@name) or '' in (@name) Select * from table where ID in (@ID) or '' in (@ID) or name in (@name) or '' in (@name) Many thanks Syed Qazafi Anjum Hi Punia, Just to add one comment to this answer. As ID and Name are multivalued parameters, we have to use JOIN() to make them comma separated. Here are some cool references: http://msifed.wordpress.com/2012/02/28/ssrs-passing-multi-value-parameters-between-reports/ http://munishbansal.wordpress.com/2008/12/29/passing-multi-value-parameter-in-stored-procedure-ssrs-report/ Thanks, Khilit http://www.bigator.com

Hi Khilit Thanks for your posting. That is not right. you do not need to use Join function any more if you are using this in SSRS 2008 Datset. However if you are using multivalued parameter inside Stored procedure then you need to use custom function to split those values Many thanks Syed Qazafi Anjum

Hi Syed, I used the condition in where command already. Select * from table where ID in (@ID) or '' in (@ID) or name in (@name) or '' in (@name) But when i do not select any thing in (let' say) ID and select any value in Name, it do not fail, perhaps it do not show anything. i have to select atleast one value in report to run the report. Thanks, Punia

Any other suggestions?

Hi babbupunia, Thanks for your posting. To ahiceve your goal, you need to first add a null value to both of the queries that the parameters "ID" and "Name" retrieve data from. To do this, you need to modify the query from which the parameter gets values from as follows: SELECT DISTINCT ID FROM Your_Table UNION SELECT '' AS ID ORDER BY ID Then, you need to modify the WHERE clause in the dataset query of the report as follows: WHERE ((''IN (@ID) AND Name IN (@Name)) OR (ID IN (@ID) AND '' IN (@Name)) OR (ID IN (@ID) AND Name IN (@Name))) In this way, when both of the two parameters only select the blank value, no data will be displayed in the report. Hope this helps. Regards, Mike YinMike Yin TechNet Community Support

Hi babbupunia, Thanks for your posting. To ahiceve your goal, you need to first add a null value to both of the queries that the parameters "ID" and "Name" retrieve data from. To do this, you need to modify the query from which the parameter gets values from as follows: SELECT DISTINCT ID FROM Your_Table UNION SELECT '' AS ID ORDER BY ID Then, you need to modify the WHERE clause in the dataset query of the report as follows: WHERE ((''IN (@ID) AND Name IN (@Name)) OR (ID IN (@ID) AND '' IN (@Name)) OR (ID IN (@ID) AND Name IN (@Name))) In this way, when both of the two parameters only select the blank value, no data will be displayed in the report. Hope this helps. Regards, Mike YinMike Yin TechNet Community Support

Hi BabbuPunia, Please proceed as below... 1. First of all, use "IN" instead of "=", becuase multiple values requires IN not = in Main Query SP. 2. You have to create a tabulated function for passing multiple value for a parameter.. *** Code **** CREATE FUNCTION [Split] ( @Data varchar (MAX), @Split nvarchar(5) ) RETURNS @Return table ( Id int identity(1,1), Data varchar (MAX) ) AS BEGIN DECLARE @Count INT Set @Count = 1 While (Charindex(@Split,@Data)>0) Begin Insert Into @Return (data) Select Data = ltrim(rtrim(Substring(@Data,1,Charindex(@Split,@Data)-1))) Set @Data = Substring(@Data,Charindex(@Split,@Data)+1,len(@Data)) Set @Count = @Count + 1 End Insert Into @Return (data) Select Data = ltrim(rtrim(@Data)) Return END 3. In main query pass values like this Select * from <TableName> where ID in (SELECT Data from Split(@id,',') both for Id and name. 4. Now move to ID and Name dataset. As a matter of fact you cannot leave report parameter blank even you use opt Allow blank in parameter in report,for this you have to include null values in dataset for both id and name, similar to the Mike has suggested. SELECT DISTINCT ID FROM Your_Table UNION SELECT '' AS ID ORDER BY ID 5. Now you will find Blank option in Paramter, if you wanna pass Blank value, just select blank in the dropdown. Please let me know if you stil have issue with the same.Amit Please mark as answer if helpful http://fascinatingsql.wordpress.com/

Hi BabbuPunia, Please proceed as below... 1. First of all, use "IN" instead of "=", becuase multiple values requires IN not = in Main Query SP. 2. You have to create a tabulated function for passing multiple value for a parameter.. *** Code **** CREATE FUNCTION [Split] ( @Data varchar (MAX), @Split nvarchar(5) ) RETURNS @Return table ( Id int identity(1,1), Data varchar (MAX) ) AS BEGIN DECLARE @Count INT Set @Count = 1 While (Charindex(@Split,@Data)>0) Begin Insert Into @Return (data) Select Data = ltrim(rtrim(Substring(@Data,1,Charindex(@Split,@Data)-1))) Set @Data = Substring(@Data,Charindex(@Split,@Data)+1,len(@Data)) Set @Count = @Count + 1 End Insert Into @Return (data) Select Data = ltrim(rtrim(@Data)) Return END 3. In main query pass values like this Select * from <TableName> where ID in (SELECT Data from Split(@id,',') both for Id and name. 4. Now move to ID and Name dataset. As a matter of fact you cannot leave report parameter blank even you use opt Allow blank in parameter in report,for this you have to include null values in dataset for both id and name, similar to the Mike has suggested. SELECT DISTINCT ID FROM Your_Table UNION SELECT '' AS ID ORDER BY ID 5. Now you will find Blank option in Paramter, if you wanna pass Blank value, just select blank in the dropdown. Please let me know if you stil have issue with the same.Amit Please mark as answer if helpful http://fascinatingsql.wordpress.com/

Thanks Mike and Amit for your suggstions! It works ! I modify the @parametera ID and Name query using union with Blank '' as below: SELECT DISTINCT ID FROM Your_Table UNION SELECT '' AS ID ORDER BY ID Now user is like : He don't have to select even the blank ' ' value from drop down in parameter in report. So if he leaves the parameter just blank without selecting '' say in ID and select some values in @Name , then also it should work. If i am not wrong, i think this can not be done in reporting services. I have to select at least one value in both the paramters to run the report. Please let me know if this not the case. Or if there is any other work around for this new user requirement, please let me know. Thanks, Punia

Thanks for replying Mike! I tried the way you suggested, but does work out. As @ID or @Name parameter are getting the values from their respective queries: Lets say for @ID, under "available values" tab, i got for option ( get values from query) as: SELECT DISTINCT ID FROM Your_Table ORDER BY ID So when i tried to put some values under "Default values" tab, for example blank '', it dont allow me to do this. Am i wrong somewhere? Thanks, Punia

Hi Punia, Thanks for your posting. It didn't allow you to set the blank value as the default value because the blank value is not in the available values of the parameter. So, you need to modify your query from which the parameter get values to return the blank value. As I posted, the query should includes UNION operation: SELECT DISTINCT ID FROM Your_Table UNION SELECT '' AS ID ORDER BY ID Please let me know if it doesn't work. Regards, Mike YinMike Yin TechNet Community Support

Hi Punia, Thanks for your posting. You are right that we need to select at least one value for the parameter before running the report. Based on your requirement, you can set the default value of the "ID" parameter to the blank value and set the default value of "Name" parameter to "No default value". That way, the user can select values for the "Name" parameter to run the report. Additionally, you can also set the default value of the two parameters to blank value. In this way, the report returns no data initially and user can select values for either or both of the parameters to run the report. If you have any questions, please don't hesitate to let me know. Regards, Mike Yin Mike Yin TechNet Community Support