Creating Multi Value Parameter Through OLE DB Data Source

Question

Creating Multi Value Parameter Through OLE DB Data Source

dapoole

Default port

Points: 1470
More actions
November 18, 2014 at 5:13 am

#301301

Hi,
How can I go about creating a multi value parameter that passes through an OLE DB data source? Whenever I try to pass through a multi parameter from SSRS 2012 to a legacy 2000 SQL Server database I get the error message:
"Cannot add multi value query parameter 'Reason' for dataset 'Logs' because it is not supported by the data extension".
I've tried this is SQL:
SELECT TOP 1 LOGNO, LOG_DATE_TIME, LOG_STAFF_NAME, LOG_NOTES, LOG_REASON
FROM LOG
WHERE LOG_REASON IN (?)
And by creating an Expression:
="SELECT TOP 1 LOGNO, LOG_DATE_TIME, LOG_STAFF_NAME, LOG_NOTES, LOG_REASON
FROM LOG
WHERE LOG_REASON IN (" & Join(Parameters!Reason.value, ", ") & ")"
The only query to work is hardcoding it but I need a parameter to be dynamic:
SELECT TOP 1 LOGNO, LOG_DATE_TIME, LOG_STAFF_NAME, LOG_NOTES, LOG_REASON
FROM LOG
WHERE LOG_REASON IN ('HIRE','RELEASE')
Can anyone help?
TIA

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

And the expression doesn't work? It should result in the same query as the hardcoded one.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

dapoole Default port Points: 1470 More actions · Answer 2

Koen Verbeeck (11/18/2014)
And the expression doesn't work? It should result in the same query as the hardcoded one.

Thanks for replying. Unfortunately it does not work. I keep getting the error "Cannot add multiple value query parameter 'Reason' for dataset 'Logs' because it is not supported by the data extension."

It doesn't even work for a single parameter because when I untick 'Allow multiple values' in the Report Parameters Properties box and try to run the report I get an error: "Cannot set the command text for dataset 'Logs'. Error during processing of the CommandText expression of dataset 'Logs'."

dapoole Default port Points: 1470 More actions · Answer 3

Ok I've managed to resolve the issue. Unless anyone else has other methods this is the only way I could get multi value parameters to work on an OLE DB data provider. My example is given.

1. Create the expression on one single line in the Query section of the Dataset Properties box, i.e.

="SELECT TOP 1 LOGNO, LOG_REASON FROM LOG WHERE LOG_REASON IN ('" & Join(Parameters!Reason.Value,"', '") & "')"

2. Add the Fields mentioned in the above expression as 'Query Field' in the Fields section of the Dataset Properties box, i.e. LOGNO, LOG_REASON.

3. If created then delete any mention of Parameters in the Parameters section of the Dataset Properties box, i.e. Reason.

4. Create the Parameters as 'Allow multiple values', i.e. Reason.

Multi value parameters now working through OLE DB.

JCYuuu Newbie Points: 1 More actions · Answer 4

I made an account with the sole purpose of thanking you for being the only answer I could find for this and saved me a lot of time and work and my college project