SSRS with UDF and dynamic SQL

I have an SSRS report with a datasource connecting to Oracle db, that then uses a transparent gateway to pull DB2 data. Due to using date range validation, and to have an optional parameter, I am using a udf to build the sql statement dynamically. This works well...

Public Function getData(FromDate AS DATE, ToDate AS DATE, Plant AS STRING, PartNumber AS STRING, ExtTxnCd AS STRING, DateRangeValid AS STRING) AS STRING
  Dim sqlStmt AS STRING = ""

  sqlStmt = "SELECT ITEM_NO, TXN_DATE, TXN_TIME, QUANTITY, ITEM_DESC, PLANT_NO, ORDER_NO, INT_TXN_CD, EXT_TXN_CD, AREA||ROW||BIN||SHELF Secondary_Loc FROM TABLEA, TABLEB"

  sqlStmt = sqlStmt + " WHERE TXN_DATE >= '" + FromDate + "'"

  sqlStmt = sqlStmt + " AND TXN_DATE < '" + ToDate + "'"

  sqlStmt = sqlStmt + " AND PLANT_NO = '" + Plant + "'"

  IF PartNumber <> Nothing THEN

    sqlStmt = sqlStmt + " AND REPLACE(ITEM_NO,' ','') = '" + PartNumber + "'" 

  END IF

  sqlStmt = sqlStmt + " AND EXT_TXN_CD = '" + ExtTxnCd + "'"

  sqlStmt = sqlStmt + " AND 'True' = '" + DateRangeValid + "'"

  sqlStmt = sqlStmt + " AND A_INT_TXN_CD||A_TXN_DESC_CODE = B_INT_TXN_CD||B_TXN_TYPE_CD ORDER BY TXN_DATE, TXN_TIME"

  RETURN sqlStmt

END Function

The issue arises in the requirement to have one of the report parameters (ExtTxnCd) allow multiple values to be selected (I've done so many times, just not yet when building the sql statement dynamically like this). If I were to hardcode it, that condition would look something like...

AND EXT_TXN_CD IN ('ABC123','ABC234','ABC678', 'ABC789')

When I change the parameter property to allow multiple values, and change 2 lines in the function's where clause to:

sqlStmt = sqlStmt + " AND THF_EXT_TXN_CD IN ('" + ExtTxnCd + "'"
sqlStmt = sqlStmt + ") AND 'True' = '" + DateRangeValid + "'"

...and try running the report, I get an error:

'Cannot add multiple value query parameter '@ExtTxnCd' for dataset 'Dataset1' because it is not supported by the data extension.'

Then I changed my dataset query, call to the function, to pass in that parameter value as Join(Parameters!ExtTxnCd.Value,","), and in the dataset parameters, changed that parameter's value to also use the JOIN. Now I don't get the errors, but I get no data returned when expecting data based on the parameters selected.

I believe the issue is that the parameter values ... 'ABC123,ABC234,etc..' is being passed in as comma-separated values but as one long string value, rather than each value being passed in being wrapped in single quotes. How would I build the dynamic sql statement with each value in single quotes?

Solution

You need to separate the parameters with quotes. Try Join(Parameters!ExtTxnCd.Value, "','")

This means your string is being built in two places however: the intervening quotes and commas in SSRS and the brackets and outside quotes by your Oracle udf. It would be less confusing to build it all in the one spot, so SSRS becomes:

"('" & Join(Parameters!ExtTxnCd.Value, "','") & "')"

and in the Oracle udf it is simply:

sqlStmt = sqlStmt + " AND THF_EXT_TXN_CD IN " + ExtTxnCd