one parameter value dependant on another parameter in report

Question

I have 2 report parameters as customerNumber and customerName. The user must enter one or the other - Not both, so text entered on the first one will disabled the second parameter and vice versa.
Can this be done using dataset or how to link both so as only 1 of them accept values?

Many thanks

Answer 1

I don't 'think' you will be able to do this.

You can simulate one being disabled based on the other but you will only be able to do that one way I think otherwise you would have a circular reference.

Imagine your customerName dataset looked something like

IF @customerNumber IS NULL
    BEGIN
        SELECT '' as customerName
    END
ELSE
    BEGIN
        SELECT DSITINCT customerName from myTable
    END

This would be OK, but you could not then do the same to the customerNumber parameter's dataset as it would have to reference the customerNumber parameter, and round we go again.

Other option?

Assuming you want the user to supply a name or number but not both, you could use a single field for name/number entry and then a list based on that that contains the actual value you want.

So the first parameter customerSearch is plain text single value.

The dataset for the second parameter 'customerNumber` available values might look like this

SELECT customerName, customerID 
    FROM myTable t 
    WHERE customerName LIKE '%' + @customerSearch + '%'
        OR customerID = @customerSearch -- CAST the parameter if required

Set the parameter to multi-value and optionally set the default values to the same dataset.

Not ideal but maybe a reasonable compromise.