FILTER ANY COLUMN IN REPORT
-
I have a report which have around 20 columns, now user wants to filter on ANY of these 20 columns.
My question is that is there any way to do this except Parameters? I can't have 20 parameters on the report.
Is there any way that we can provide functionaily of Filters on the report so that user can filter it at runtime?
Any help will be appreciated.
I'm using MS SQL Server Reporting Services 2000 (not 2005).
-
you could try adding just one visible parameter where the user types in the value of the column and then add a 'jump to report' link on the column heading which fires up the same report again. If your dataset were based on a stored procedure which accepted the column name and column value as parameters, it could then filter the rows and return the required resultset.
I can't guarantee it but you could try and if it works, let the rest of us know!
-
I suppose you have a 3-tiered system, and you just don't want users to see 20 fields in the GUI:
1. In your GUI, as Jon mentioned, you allow the user to specify the column and its value. Your code behind the UI should have 20 variables for the columns and by default set them all to null. Then change just the user selected column to specified value.
2. In your Reports Designer, the report should have 20 parameters as well.
3. In your stored procedure, you have 20 parameters and they are all nullible. Your select statement may look like this:
SELECT some_columns
FROM your_tables
WHERE COLUMN1 = COAELESCE(parm1, COLUMN1, parm1)
AND COLUMN2 = COAELESCE(parm2, COLUMN2, parm2)
---
AND COLUMN20 = COAELESCE(parm20, COLUMN20, parm20)
This should work, unless I made incorrect assumptions.
-
I believe there is a typo in the above post.COAELESCE should be replaced with COALESCE.
Prasad Bhogadi
www.inforaise.com -
Prasad is right. Sorry for the typo.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply