Overcome 1,000 items limit for IN clause in Oracle with SSRS

Overcome 1,000 items limit for IN clause in Oracle with SSRS In Oracle, there is a 1,000 items limit when using the IN clause. In order to overcome this, we can use table JOIN, having the list of items in a temp table or sort. But what happens when the list is for SSRS parameter? We can use a Common Table Expression (CTE) to solve this.

The following code is specific to Oracle, but it can be written for SQL Server too with tweak. This is just to show the idea.

WITH cteTempTbl AS (
	SELECT regexp_substr(txt,'[^,]+',1,level) AS ITM
	FROM (SELECT :prmSSRS AS txt FROM DUAL)
	CONNECT BY LEVEL <= LENGTH (REGEXP_REPLACE (txt, '[^,]'))+1
)

The above code takes the list of items, comma separated (that's how SSRS pass the values) from the parameter, prmSSRS, and create a temp table (cteTempTbl).

To use this CTE, we can do a couple of things.

• Using JOIN

      SELECT A.*
      FROM TABLE A
      INNER JOIN cteTempTbl t ON t.ITM = A.ITM
      

• Using EXISTS (potentially better performance)

      SELECT A.*
      FROM TABLE A
      WHERE
      	EXISTS (SELECT 1 FROM cteTempTbl t WHERE t.ITM = A.ITM)
      

With the example above, we should be able to pass any number of items in the SSRS parameter dropdown list into the SQL query.

Report Elapsed Time for SSRS report

1. Add a new report variable named thisTime with value of =DateTime.Now
2. Add the following code snippet to the Report Code section:

   Public Dim execText As String

   ' for this function to work,
   ' the following Report Variable needs to be defined and set.
   ' thisTime =DateTime.Now

   Public Function LapseTime(execTime as Date, sysTime as Microsoft.ReportingServices.ReportProcessing.OnDemandReportObjectModel.Variable) As String

       Dim ElapsedMins As Integer 
       Dim ElapsedSecs As Integer 

       Dim currTime As System.DateTime = sysTime.Value

       If (ExecText = "") Then
           'Calculate execution time here
           ElapsedMins = DateDiff(DateInterval.Minute, execTime , currTime) mod 60 
           ElapsedSecs = DateDiff(DateInterval.Second, execTime , currTime) mod 60

           execText = ElapsedMins.ToString() & "m " & ElapsedSecs.ToString() & "s"
           'execText = ElapsedMins.ToString() & ":" & Right("0" & ElapsedSecs.ToString(), 2)
       End If

       Return execText

   End Function

3. To use it, create a new textbox and enter the following in the Expression:

   =Code.LapseTime(Globals!ExecutionTime,Variables!thisTime)

4. Done!