SQLTeam.com | Weblogs | Forums

SSRS Multi-Value Parameter using OpenQuery (MySQL database)

#1

Hey Guys,

I would like to ask what is causing the problem, multi-value parameter doesn't work here's the query.

=Join(Parameters!Account.Value,", ")

=Split(Join(Parameters!Account.Value,", "),", ")

I used those two on the parameter column

when selecting one value on the parameter its working fine, but when you selected multi-values its not give the correct results.

its passing the values like this : 'AccountName1, AccountName2, AccountName3'

--Local Variables
DECLARE @ContractList VARCHAR(MAX)
DECLARE @Account_Name VARCHAR(100)

SET @Account_Name = @Account

SELECT @ContractList =

'SELECT * FROM OPENQUERY
(MYSQLC,''SELECT a.id AS account_id,
a.name AS account_name,
c.name AS contract_name
FROM Accounts a
LEFT JOIN contracts c ON c.accountid = a.id AND c.deleted = 0
LEFT JOIN (
SELECT accountid, MIN(start_date) as_of
FROM contracts
GROUP BY accountid) cao ON cao.accountid = a.id
WHERE a.deleted = 0 AND a.active = 1 AND
(
SELECT COUNT(*)
FROM contracts
WHERE deleted = 0 AND date_closed IS NULL AND accountid = a.id) > 0
AND a.name IN ( ''''' + @Account_Name + ''''')
ORDER BY c.name
''
) '

EXEC (@ContractList)

0 Likes