none
how to display blank values in SSRS filter

    Question

  • Hi All;

    Below is my report

    I need to add a filter on Reason for leaving in such a way that the filter shows value like "Left Early", "Completed Programme" and Blank

    how do i show the filter to display  bank values as well

    Any help much appreciated

    Thanks


    Pradnya07

    Tuesday, January 08, 2013 10:47 AM

Answers

  • Hi Pradnya07,

    To achieve your goal, please do as follows:

    1. Create another dataset from which the parameter “ReasonForLeaving” retrieves values, and add the blank value to the field. The query is as follows:

      SELECT DISTINCT Reason_For_Leaving
      FROM            MyTable
      UNION
      SELECT        '  ' AS Expr1

    Note:  There are two spaces in the quotes. We don’t use one space here because there are spaces in the values of the Reason_For_Leaving field which affects the organization of the visibility expression of the target group.

    2. Create a multi-value parameter “ReasonForLeaving”, don’t need to check the “Allow bank value” option for the parameter. Configure the parameter to get available values from the query we write just now.

    3. Because the Reason_For_Leaving field includes null value, it won’t work at run time if we set the parameter to get default values from the query. So, I suggest that you specify the parameter default values manually. If you need to display all the records of the report initially, we can add an “All” value into the above query. In this way, we can set the parameter default value to “All” and make all the records visible when the parameter value is “All”.

    4. In the query of the main dataset, modify the WHERE clause as follows:

      WHERE (Reason_For_Leaving IN (@Reason_For_Leaving)) OR (Reason_For_Leaving IS NULL) 

    5. When we run the report at this time, we can find that even if we don’t select the blank value for the parameter, the records which have null Reason_For_Leaving values are still displayed. To resolve the issue, we can set the visibility of the proper group or column as follows:

      =IIF(InStr(Join(Parameters!p1.Value),"  ")=0 and Fields!Reason_For_Leaving.Value Is Nothing, True, False)

    Note: There are two spaces in the double quotes within the InStr() function.

    If you have any questions, please feel free to ask.

    Regards,


    Mike Yin
    TechNet Community Support

    Wednesday, January 09, 2013 9:30 AM

All replies

  • Hi

    1. Add a parameter (Parameter1), on the general tab, check "Allow Blank"

    2. On the Available Values, select specify values and add "Left Early", "Completed Programme" and blanks for both the labels and the values.

    2a. If you are using a query to specify your values in the parameter, add "UNION select null from dual" to your query to add a blank value

    3. Modify the sql statement/text of the dataset of your report to something like:

    select name from employees_table where reason_for_leaving=@Parameter1


    Hope this helps!

    k r o o t z



    Tuesday, January 08, 2013 11:06 AM
  • it didnt help

    Pradnya07

    Tuesday, January 08, 2013 1:20 PM
  • Hi Pradnya07,

    To achieve your goal, please do as follows:

    1. Create another dataset from which the parameter “ReasonForLeaving” retrieves values, and add the blank value to the field. The query is as follows:

      SELECT DISTINCT Reason_For_Leaving
      FROM            MyTable
      UNION
      SELECT        '  ' AS Expr1

    Note:  There are two spaces in the quotes. We don’t use one space here because there are spaces in the values of the Reason_For_Leaving field which affects the organization of the visibility expression of the target group.

    2. Create a multi-value parameter “ReasonForLeaving”, don’t need to check the “Allow bank value” option for the parameter. Configure the parameter to get available values from the query we write just now.

    3. Because the Reason_For_Leaving field includes null value, it won’t work at run time if we set the parameter to get default values from the query. So, I suggest that you specify the parameter default values manually. If you need to display all the records of the report initially, we can add an “All” value into the above query. In this way, we can set the parameter default value to “All” and make all the records visible when the parameter value is “All”.

    4. In the query of the main dataset, modify the WHERE clause as follows:

      WHERE (Reason_For_Leaving IN (@Reason_For_Leaving)) OR (Reason_For_Leaving IS NULL) 

    5. When we run the report at this time, we can find that even if we don’t select the blank value for the parameter, the records which have null Reason_For_Leaving values are still displayed. To resolve the issue, we can set the visibility of the proper group or column as follows:

      =IIF(InStr(Join(Parameters!p1.Value),"  ")=0 and Fields!Reason_For_Leaving.Value Is Nothing, True, False)

    Note: There are two spaces in the double quotes within the InStr() function.

    If you have any questions, please feel free to ask.

    Regards,


    Mike Yin
    TechNet Community Support

    Wednesday, January 09, 2013 9:30 AM