runtime filters 1

[access97to2000]

Hi,
Is there anyway that we can have like runtime filters ?
For ex:
i have like 10 records in a table on a report with fields filename, size, location etc.
i would like to filter out whose size is more than 4kb.
is there any way ?

thanks in advance

 

[xlbo]

Do it in the query itself

SELECT blah, blah, blah
...
WHERE Size < 4

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]

You create a parameter or a filter in your dataset. The difference between a parameter and a filter is that a parameter affects your base dataset query, and a filter acts on the dataset that you already have queried. There are advantages and disadvantages to each.

If you want to create a Size parameter, your query would be

SELECT filename, size, location 
FROM YourTable
WHERE side <= @maxsize

The "@maxsize" criteria is the parameter, and your report should automatically hook it up if you type it that way from the start. From then on, you will be prompted to enter "maxsize" every time you want to run the report. You can enter a different size every time.

A filter is similar, but your base query is just

SELECT filename, size, location 
FROM YourTable

Notice no WHERE condition for size. The report will pull in all records. This makes for a larger/slower initial query, but then all the records are cached and you can filter those faster than re-querying the database every time you want a new size limit. You create a new parameter in the report properties and then go to the DataSet's filter page and create a new filter where size = @sizeparameter.

Let me know if you need more clarification. Both methods work.