April 27, 2012 at 8:06 am
Hi,
I need to give my User an option in the report where he can choose an option to filter records based on either of the two conditions.
e.g. User may want to filter Employees in his report based on either the Employee Name or Employee Number. Both these fields already exist in Database, so thats not a problem.
Concern was, Is it possible that I create a parameter "Choose your option" having two values "Emp No" and "Emp Name" and if user selects, say, Emp No, then he gets a parameter "Emp No" with the list of all employees and there he can choose mutiple employees, and at this time the parameter "Emp Name" should not be visible. Similarly, if User selects, Emp Name, he should get Emp Name parameter to choose values from and simialrly this time around the parameter "Emp No" should be hidden and not come in picture.
Hope I have made my point clear. Any suggestions please ?
April 27, 2012 at 9:05 am
Yes it is possible, with a few caveats. You will not really hide or show different parameters, but use a generic parameter that meets both needs.
Your second query & parameter will need to be a little bit generic. First the label for the second parameter should be something like Choose Employee instead of Employee Number or Employee Name
Your query will have to be a little dynamic where depending on the input parameter it will return different datasets, however the datasets returned must have the same column names. Assuming you are only returning one column of data in either case, emp number or emp name, then make them both return a column named employee or something like that so that when you choose the dataset that feeds the parameter you choose the same column value regardless of the actual data being returned.
Finally your report dataset query needs to have a somewhat dynamic WHERE clause. You can either use dynamic SQL or perhaps a flexible statement with some OR like:
WHERE (@EmployeeTypeInputParm = 'Name' AND EmployeeName = @EmployeeValueInputParm) OR
(@EmployeeTypeInputParm = 'Number' AND EmployeeNumber = @EmployeeValueInputParm)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply