Tuesday, January 08, 2013 10:47 AM
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
Tuesday, January 08, 2013 11:06 AM
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
- Edited by krootz Tuesday, January 08, 2013 11:38 AM
Tuesday, January 08, 2013 1:20 PMit didnt help
Wednesday, January 09, 2013 9:30 AMModerator
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.
TechNet Community Support
- Marked As Answer by Mike YinMicrosoft Contingent Staff, Moderator Tuesday, January 15, 2013 1:21 AM