Pass a Parameter in SSRS while removing Dashes

user_3303578 0
Viewed 122

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

ssrs-2008 reporting-services
2 Answers

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 Hidden Parameter

  • Set the default value expression:

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

    Default value for parameter

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: Hidden multivalue parameter

  • 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:

Raw and sanitised input

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:

hidden sanitised parameter

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

SELECT * FROM Table WHERE Unique_ID IN (@sanitisedMultiValue)
user_16902170

You should be able to use the replace function in your report to format the parameter value after it has been entered, something like the below

replace(Fields!Paramater.Value,"-","")=FieldinYourTable

user_96066120
Related