Can a single Parameter be used for multiple columns in the query.
Hi all, I was trying to create a report with a slight different kind of requirement, Actually I have 5 Columns in a table from which I want the user to be capable to view the data based on any combination for those columns. For Example: I have a table Employee with attributes: Name / Age / Salary / STATE Now I want the user to be capable of selecting any combination for these columns and then running the report, Actually the report will behave like a Select Query with Where clause with AND conditions for all columns. Report design should be like this: FIRST prompt: Select Column name ex: AGE SECOND prompt: Select Column Value ex: 30 (now store this value somewhere and allow the user to again select a different column name like STATE, and the SECOND prompt will show all distinct STATEvalues, then user selects for ex: NEW YORK Then when USER clicks VIEW REPORT: Show all the employees where AGE = 30 and STATE = NEW YORK. Is it possible ? as I dont want to create 10 prompts for 5 columns separately. Any help is appreciated. Thanks
October 20th, 2010 9:29pm

Yes, it can be done using cascading report parameters (parameters based on a table of values that are themselves selected based on the values of other parameters). One thing that will be touchy, with a disparate set of types like you've shown is parameter validation. For example, State should be a drop-down list, Name should be a text box, Age should be a positive integer, salaray should be a positive real. In order to mix all of those, you'll need to use a simple text box for the value, which seriously limits your ability to do data validation. First thing, I'd recommend reading up on cascading report parameters, here. Let's suppose that you want to let the user pick any two of the five columns to use as filters. To do that, you'll need to have 4 parameters (plus whatever other parameters you need for other purposes). The details are more than a bit subtle, so before I spend an hour writing up an example, please take a look at the reference material above and determine whether you think that will meet your needs, then we can go from there. All in all, for just 5 columns, I'd recommend simply putting 5 parameters on the report, with a well defined "Match anything" value for each column (null is frequently a good choice). -cd Mark the best replies as answers!
Free Windows Admin Tool Kit Click here and download it now
October 20th, 2010 11:03pm

You can create report with Store Procedure with 5 cascaded parametes and Case When structure in where condition. It will work.
October 21st, 2010 12:21am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics