Report Filter - For Yes, No, All

Question

Post reply

Report Filter - For Yes, No, All

DoubleEx

SSCommitted

Points: 1523
More actions
February 5, 2011 at 8:23 am

#244719

I have a report with a parameter that allows the user to select "Yes" "No" or "All". The underlying data field that corresponds to this filter has only Yes or No in the field. This data comes from a Stored Procedure that I cannot change.
Getting a report filter to only show the Yes rows when Yes is selected and only show the No rows when No is selected is easy but they need to also have the option to "not filter". Is there way to conditionally apply a filter? Or is there a way to build an expression in the filter that takes into account all three scenarios?
Thanks for any ideas or help

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 1

Hi,

Please explain how is your code at present.? Is the code returns data based on the parameter as for example

select * from table

where column_name = @p

So that the @p value you are passing from the report as either 'Yes' or 'No' ?

If so change the WHERE condition as:

where column_name in (@p)

So that in the report for the option 'Not Filter' you pass the value as 'Yes','No'

Thanks & Regards,
MC

DoubleEx SSCommitted Points: 1523 More actions · Answer 2

Thanks but as I mentioned in my post - this report is driven by a stored procedure that I have no ability to change. I am limited to using SSRS filters as I cannot pass a new parameter to the SPROC.

exec rpt_WeeklyActivityReport @StartDate, @EndDate, @CompanyID, @DepartmentID

I have report parameters for all 4 of the SPROC parameters. This call will return a number of fields pre-filtered by my calling parameters. However it returns another field that is set to Yes or No. I would like to further limit the content of the report based on a user report parameter selection.

I can easily do that with filters if I just want to show a report with Yes or another report with No but there is a condition where the report should include both Yes and No.

Thanks for taking time to post a response. I appreciate it.

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 3

Ok..

Do like this , add a report parameter , say Status, with the three available values (Yes,No,NoFilter) as:

Lable = Yes , Value = No

Lable = No , Value = Yes

Lable = NoFilter , Value = abc

And then put a filter in the data set on that filed as

Filed != Parameters!Satus.Value

So

1) When the user selects 'Ye's, it pass the value as 'No' then condition will be

Fetch all the data where Status != No --> Gives 'Yes' data

2) When the user selects 'No', it pass the value as 'Yes' then condition will be

Fetch all the data where Status != Yes --> Gives 'No' data

3) When the user selects 'NoFilter', it pass the value as 'abc' then condition will be

Fetch all the data where Status != abc--> Give both 'Yes' and 'No' data

So here you don't have to modify your stored procedure. Hope this helps.

Thanks & Regards,
MC

DoubleEx SSCommitted Points: 1523 More actions · Answer 4

Now that's some good thinking! I never thought to reverse the logic and if I was maintaining code that did that I would think "what's this guy doing?" but I bet it's going to work perfectly. I'll check back after I get in there to try it. Thank You!

DoubleEx SSCommitted Points: 1523 More actions · Answer 5

You nailed it! Very nice answer. I think I learned a bit more than how to write filters 😉

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 6

Happy to know your issues is solved.

Thanks for the feedback...

Thanks & Regards,
MC