SSRS: Solving ORA-01795 (Maximum Expressions in a List is 1000)

This blog post shows a simple method of solving the problem of Oracle only allowing 1,000 records to be passed into an IN statement. When you try and pass in 1001 you get the error message;

ORA-01795: maximum number of expressions in a list is 1000

This is no way round this issue from an Oracle view point, the only solution lies in not passing in so many values.

Now in SSRS the most common way this bug has exhibited itself is when you have a drop down list (of Vendors for example) and the user wants to see either a handful of specific vendors or all of them. The user triggers the drop down, hits "Select all" (a check box appears next to every item), submits the report and then gets the error.

The key to solving this problem is the "Select all". If the user has selected all records then rather than passing in the full list if we passed in a single value that the dataset could then interpret as meaning "give me everything".

Looking at our original WHERE-clause in the SQL;

...

AND NID.VENDOR_ID IN (:Vendor_ID_List)

If we change this to;

...

AND (-1 IN (:Vendor_ID_List) OR NID.VENDOR_ID IN (:Vendor_ID_List))

Then if the values we pass in include -1 we will get everything.

Next we need to modify the SSRS report to pass in -1 when the user has selected every value. Open the report and bring up the "Dataset Properties" and click on "Parameters" on the left;

Dataset Properties | Parameters

Click on the "function" button next to the parameter and replace the text;

=Parameters!VENDORLIST.Value

With the function;

=IIF(Parameters!VENDORLIST.Count > 1000, -1, Parameters!VENDORLIST.Value)

Now the one issue with this is that, occassionally, users will want to see everyone EXCEPT one or two vendors. Clearly with the SQL above if you select 1001 of your 1003 Vendors then it will be treated as "All" for the purposes of reporting.

The solution we have found for this is to add another parameter called "Excluded_Vendor_Id_List" which works in very much the same way but allows users to specify a list of Vendors to exclude from the report. Not the most elegant solution (and it does increase the report complexity) but it does work.