Unicode Parameters

Work around to overcome SSRS report performance due to UNICODE conversion issue



I have used a new parameter (of type Internal) which collects/duplicates the parameter values as comma separated in string from orig parameter.

In the report Dataset query parse the comma separated string into list using XML trick and load into a variable table.

Use the variable table in WHERE IN clause



Steps:

Create a new Internal parameter (call it InternalParameter1)

Under Default Values -> Specify values : Add Exp : =join( Parameters!OrigParameter1.Value,",")

Pass/Use the InternalParameter1 in your dataset query.

/*
Example code  
DECLARE @InternalParameter1 NVARCHAR(MAX)
SET @InternalParameter1 = '100167600,
100167601,
4302853605,
4030753556,
4026938411
'
*/

--- Load comma separated string to a temp variable table  ---
SET ARITHABORT ON
DECLARE @T1  AS TABLE (PARALIST VARCHAR(100))
INSERT @T1 SELECT Split.a.value('.', 'VARCHAR(100)') AS CVS  FROM  
( SELECT CAST ('<M>' + REPLACE(@InternalParameter1, ',', '</M><M>') + '</M>' AS XML) AS CVS ) AS A CROSS APPLY CVS.nodes ('/M') AS Split(a)

--- Report Dataset query  ---
SELECT CONTRACT_NO, report fields…  FROM mytable
WHERE  CONTRACT_NO IN (SELECT PARALIST FROM @T1)   -- Use temp variable table in where clause

---

Mahesh