January 23, 2009 at 7:30 am
Hello,
I have a report with 2 parameters(Contract Numbers and Vender Name). I added a third parameter that allows you to select which parameter you want to seach e.g (search by: Contract Number or Vender Name) and it works fine but I would like to have only one drop down display the Contract Number or Vender Name based on the search by: criteria. I could easly do this in VB but I am not sure how to make this happen on the report, if at all possible. Any suggestion?
Thanks.
Steve
January 23, 2009 at 9:23 am
I don't know of a way to do this, at least not how you are attempting. A work-around may be to only have 2 parameters, the Search By parameter and the Search Value drop-down parameter. Then in the dataset that returns the value for the Search Value parameter use an expression that is dependant on the first Seach By parameter, something like this:
=IIF(Parameters!SearchBy.Value = "Contract",
"Select ContractId as id, ContractNumber as value From dbo.Contracts",
"Select VendorID as id, VendorNumer as value from dbo.Vendors")
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 23, 2009 at 1:30 pm
Hi Jack,
That is exactly what I am looking for!! Unfortunatly I am not exactly sure where to put the code(=IIF(Parameters!SearchWhat.Value = "Contract Number",
"Select ContractNumber as value From ContractNumber",
"Select Distinct VendorID as value from dbo.ContractNumber")) I tried inserting this code in the dataset under the parameters tab and in the report parameters as shown in the attachment.
Thanks!
Steve
January 23, 2009 at 2:26 pm
You need to create a data set with the expression in the dataset designer. Then you use that dataset as the source for your Search Value parameter where id is the value field and value is the label field.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 23, 2009 at 2:40 pm
Hi Jack,
I got it to work but in a different way. I created a stored procedure and aliased both fields with the same name. So in the parameters I select the dataset for the SP and I make reference to the alias "value" in the value field.
Alter PROCEDURE [dbo].[SearchWhat]
@Searchby nvarchar(25) = NULL,
@debug bit = 0 AS
DECLARE @sql nvarchar(4000),
@paramlist nvarchar(4000)
Begin Try
SET ANSI_NULLS OFF
SET ANSI_WARNINGS OFF
set QUOTED_IDENTIFIER ON
SET NOCOUNT ON
--ContractNumber
IF @Searchby = N'Contract Number' OR @Searchby = 'Contract Number'
SELECT @sql = 'Select ContractNumber as value From ContractNumber'
Else IF @Searchby = N'Vender Name' OR @Searchby = 'Vender Name'
SELECT @sql = 'Select Distinct VenderID as value From ContractNumber'
IF @debug = 1
PRINT @sql
SELECT @paramlist = '@xSearchby nvarchar(25)'
EXEC sp_executesql @sql, @paramlist, @Searchby
End Try
Thanks for the help!
Steve
January 23, 2009 at 7:46 pm
Steve,
Excellent! This is actually a better solution than what I had recommended. I did not suggest this because many times the person creating reports does not have the ability (rights) to create stored procedures and/or doesn't see the need for something small like a query for a drop down list.
Glad I was able to "grease" the skids for you.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply