Pass a Parameter in SSRS while removing Dashes

Question

I am passing a unique ID as parameter in SSRS report. In the source table, unique id does not contain dashed. However, the user may insert Unique ID including dashes "-" and in some cases without dashes. Is there a way that we could remove dashes from the parameter.

For example, unique id 3120-20268-8 is stored in table as 3120202688. How I could retrieve if user pass multiple values with or without dashes in the SSRS Report.

When is used below query, it gives record against single value only. However, gives error when more than one values are provided.

select * from Table where Unique_ID in (REPLACE(@Unique_ID,'-',''))

For more than 1 values, it gives errors mentioned below:

The replace function requires 3 argument(s).

Query execution failed for dataset 'ATL_List'.

Thanks

Answer 1

One of the simplest mechanisms for this is to create an expression based parameter to hold the sanitised input. This parameter would be hidden so the user is not aware of it, but the rest of the usage of the parameter is the same.

NOTE: You could do something similar with a query based default value, but this case is easier to do via a simple expression

Single Value Parameter

Create a new parameter:

• set it to hidden

• Set the default value expression:

     =Str(Parameters!inputID.Value).Replace("-","")
  

  

Multi-Value Parameter

This is only slightly trickier, in the expression we can join the selected values together into a CSV string, then process that value and then split it back:

• Set the parameter to multi-value, but still hidden:

• Set the default value expression:

     =Join(Parameters!inputID.Value,",").Replace("-","").Split(",")
  

Without going to detailed, if we made the sanitised parameter temporarily visible, just to demonstrate the conversion, it should look like this:

The parameter MUST be hidden!

NOTE: DO NOT make your sanitised parameter visible as in the above screenshot in your deployed report! Doing so will mean that it will not pickup changes made to the input value after it has rendered the first time.
remember that we have exploited the default value, we haven't arbitrarily defined en expression to always execute.

The output when the parameter is hidden is calculated when the report is rendered, it's just harder to visualise the behavior in this static post:

In your DataSet query you would just use the sanitised parameter:

SELECT * FROM Table WHERE Unique_ID IN (@sanitisedMultiValue)