Can filters not filter if left blank/null? 1

[ookete]

I have tried adding a simple filter to my report's recordset, but I can't make it NOT filter when blank/null. For example, I want the user to be able to filter by Shift (Shift 1 or Shift 2), but if they leave it blank then it should have no filter... it should show all records. But when I leave the filter blank it gives me zero records. Anyone know how to get all records using a filter?

Now, I can do this easily if I use a parameter instead of a filter because I have full control in my stored procedure. But that requires a call to the DB everytime instead of just filtering the existing cached dataset.

 

[Catadmin]

Have you tried to use the IIF() function with the IsNothing() function?

This should work:

=iif(isnothing(filtercheck), true execution, false execution)

I know one of my co-workers filtered out records by using the BLANK keyword at one point, but this only filters out blank records.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[ookete]

I haven't tried that yet, but I doubt it would work. Because filters act on the already-loaded dataset, the "true execution" and "false execution" that you write above are the same thing. We seem to have no control over how report uses the filter. I would love to have something like:

=iif(isnothing(filtercheck), "don't use this filter at all", "use this filter")

But I can't find a way to tell the report not to even consider the filter.

 

[Catadmin]

You said you don't want to have a param, but I think you're going to have to create one. A report only, non-queried value that has Filter and Non-Filter. Then create your filter expression using the IIF() with the param as your true/false values. If param = Filter, then filter on this, else no filter.

As I said, I haven't worked with this myself, but it seems the only other way I can think of off the top of my head.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[ookete]

Yes, I think you're right. That is similar to what I currently do, but the logic is taken care of in the stored procedure. If the parameter is empty, then I pass a zero to the sproc.

CREATE PROCEDURE pr_MyProc
   @shift tinyint
AS

SELECT <stuff>
FROM  MyTable
WHERE Shift = CASE @shift WHEN 0 THEN Shift ELSE @shift END

The case statement lets me ignore shift when it's zero. Again, it would be nice to have Reporting Services filters with more functionality. Ah well, c'est la vie!

 

[Catadmin]

Hopefully the next version will give us all a little bit more functionality. I'm getting tired of things that only partially work and then work only if you've jumped through a million hoops to get there.

Blearghh.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[xlbo]

You can do this by setting up a lookup table
2 columns - DisplayValue & ActualValue

This table is used to populate the dropdown list for the filter. You set the Value property of the filter to ActualValue and the Display Property to DisplayValue

you then have a * for the ActualValue & a "" or an (All) for the Display value so the filter looks like it is filtering on "" or (All) but is actually filtering on * (wildcard and should therefore return everything)

Rgds, Geoff

A file that big? It might be very useful. But now it is gone

Please read FAQ222-2244 before you ask a question

 

[ookete]

That's clever... using a LIKE behind the scenes when you make it appear as you are using an (=). In my case, I don't want a blank or (ALL) selection because it is a multi-select listbox. The user can select any random number of values or have no selection at all. If they select nothing at all then I want the (ALL) condition... so I guess what I can do is code it so that if nothing is selected, send a (*) filter parameter.

Thanks for the idea.

 

[xlbo]

No probs - just happened to have pretty much exactly the same issue when developing a report here - but they wanted an (All) option rather than a "" option.

I do also have quite a nice setup for being able to do a free form text filter which utilises wildcard functionality so someone can enter "Red" for example and they get a filtered list of all values in a specific column that contain "Red"...problem is it's one of those things that I did in a moment of inspiration and I'd have to go into the report to figure out how I did it again. Would be willing to do so though if it would be useful to people...

Rgds, Geoff

A file that big? It might be very useful. But now it is gone

Please read FAQ222-2244 before you ask a question