locked
blank value expression for ssrs report parameter RRS feed

  • Question

  • 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

    Thursday, July 19, 2012 8:41 PM

Answers

  • 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 Yin


    Mike Yin

    TechNet Community Support

    • Marked as answer by Mike Yin Monday, July 30, 2012 2:06 AM
    Thursday, July 26, 2012 11:40 AM
  • 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/

    • Marked as answer by Mike Yin Monday, July 30, 2012 2:06 AM
    Thursday, July 26, 2012 1:07 PM

All replies

  • 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

    Thursday, July 19, 2012 9:19 PM
  • 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

    Friday, July 20, 2012 1:52 AM
  • 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


    Friday, July 20, 2012 2:02 AM
  • 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

    Friday, July 20, 2012 12:36 PM
  • Any other suggestions?
    Monday, July 23, 2012 12:54 PM
  • 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 Yin


    Mike Yin

    TechNet Community Support

    • Marked as answer by Mike Yin Monday, July 30, 2012 2:06 AM
    Thursday, July 26, 2012 11:40 AM
  • 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/

    • Marked as answer by Mike Yin Monday, July 30, 2012 2:06 AM
    Thursday, July 26, 2012 1:07 PM
  • 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

    Monday, July 30, 2012 3:37 PM
  • 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

    Tuesday, July 31, 2012 1:20 AM
  • 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

    • Edited by babbupunia Wednesday, August 1, 2012 4:33 PM
    Wednesday, August 1, 2012 4:31 PM
  • 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 Yin


    Mike Yin

    TechNet Community Support

    Friday, August 3, 2012 12:58 AM
  • Thanks for replying Mike!

    Can I set some value (say "Unknown" or any word) as default , so that if dont select anything on one parmaters ID (it will take that unknown value as parameter value) and select some values in other parameter NAME, hence final query will give result based on second selected values.

    Final query:

    Select * from table

    where ID in (@ID) or NAME in (@NAME)

    Thanks,

    Punia

    Tuesday, August 7, 2012 4:11 PM