Filtering with Parameters
-
I am stuck with something that I could not think of this moment. See the data below.
1. I created a parameter called "TimeOff". It has two available values, 0 and 1, which are corresponding to the label of Yes and No, respectively. All records in the table has either value of 1 or 0 in the "TimeOffIncluded" field.
2. If the user wants the timeoff to be included, the report will display everything, (the values of 0 and 1). However, if the user wants the timeoff NOT to be included, the report will display the result of the records that have the "1" value in the "TimeOffIncluded" field.
3. I was able to create a filter for the properties of "NO" (to not include the TimeOff." I used the value of "1" to show only the records that have the 1 in the value.
My question is, how can I set up the value below to have it accept both 0 and 1? I tried using something like "=0 or 1" and it did not work for me. This option will be equivalent to "Select All", but has a "Yes" label.
Thanks.
-
The easiest way of doing this is to make the parameter optional. If nothing is selected, then the report doesn't get filtered (Same as "ALL"/"BOTH")
-
Thank you for your reply.
The problem about this is, the query came from the results of the shared dataset.
I tried to search on the Internet regarding "one label and multiple values" in the parameter properties. I tried everything that I found on the suggestions, such as using the wildcard, etc. But it was not successful. Some of the posts that I found in the stackoverflow also sent me to something really weird, such as put the value like ="*" , which won't work for sure.
Some of the suggestions is ---> change the parameter to the text. I tried that by putting the value of "T" and "F". I even put the value to include like "T,F" in the expression. Then, I used the "IN" command plus =split(Parameters!name.value, ",") and I still received an error.
I am still looking and searching for a possible solution.
Thanks again for your comment.
-
Okay, I think I worked this out. Since a BIT column stores either 1 or 0, I filtered the dataset by setting the Filter property of the report to this:
[@TimeOffIncluded] <= [@prmTimeOff]Sounds really weird, but what happens is that if you select @TimeOffIncluded=True you get all values <= 1, so you bet both True (1) and False (0).
-
Hi Tamrak
If I understand correctly, the user can choose to either show only rows with TmeOffIncluded = 1, OR to show the whole table, these are the only 2 options.
Can you adjust your select?
If so, perhaps try changing it to something like this
Select * from <Table>
where TimeOffIncluded =
Case
When @Param = 1 then @Param
Else TimeOffIncluded
End
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply