we were running in to this error quite a bit for an increasing number of reports with large datasets for cascading parameters. We had some reports with multiple parameters with over 1500 values in the drop-down. The solutions described above worked for some but did not work for others. That's when a colleague came up with a great fix (I am leaving all of field names as they are instead of swapping out for a generic description. You should still be able to get the point):

Fix for the subscriptions issue with POS_08:

The report POS_08 has a parameter with label Organization Unit: which has drop down values close to 1500. This report also has several other parameters with considerable count of drop down values for each parameter, which created as issue in setting up subscriptions on the report. With a high amount of parameter drop down values, even after making some architectural changes to the report server the issues was not solved.

I came up with a kind of parameter masking in the datasets. Normally to pass Organization Units to the main dataset query we use the following filter in the main query in the where clause

“dbo.vw_orgunit.orgunit in @orgunit”.

(@OrgUnit is the parameter which passes Organization Units to the query.)

Instead of using the above filter for Organization Unit, I used a modified one as below:

((dbo.vw_orgunit.Orgunit IN ((SELECT item

FROM [DW_Master].[dbo].fn_Split (@orgunit_d, ','))))

OR (@orgunit_d) IN ('All Org Units'))

The dataset corresponding to the parameter Organization Unit is also modified as follows to add a value “All Org Units” to the drop down values.

SELECT DISTINCT

[OrgUnit] AS [OrgUnit_Code],

[OrgUnitDescription] AS [OrgUnit_Desc],

([OrgUnit] + ' - ' + [OrgUnitDescription]) AS [OrgUnit_Label]

FROM dbo.vw_DimOrgUnit

WHERE (

[OrgLevelAgency] = '73'

AND [OrgLevelFiscalYear] IN ( @FiscalYear )

AND ([OrgLevel_1] + ' - ' + [OrgLevel_1_Description]) IN ( @OrgLevel_1_Label )

AND ([OrgLevel_2] + ' - ' + [OrgLevel_2_Description]) IN ( @OrgLevel_2_Label )

AND ([OrgLevel_3] + ' - ' + [OrgLevel_3_Description]) IN ( @OrgLevel_3_Label )

) OR [OrgUnitSKey] = '-1' OR ( [OrgUnit] = '5117' AND [OrgUnitFiscalYear] = '2013' /* A Special Case*/ )

UNION

SELECT '' , '', '(Blank)'

UNION

SELECT 'none' , 'none', 'None'

UNION

SELECT '-100','All Org Units','All Org Units'

ORDER BY [OrgUnit]

;

This way when users want to select all Organization Units, they can just select “All Org Units”, instead of selecting and passing the whole drop down values to the query.

This reduces the execution time of the report and increases the performance of the query. After all the changes were implemented the subscriptions were also set up successfully.