For an SSRS report with multi select parameters, when I 'select all' for a parameter having a long list of values, report throws a javascript error

Mahesh Kumar Ronde

This is the error I get: "Error: Sys.WebForms.PageRequestManagerServerErrorException: An unknown error occurred while processing the request on the server. The status code returned from the server was: 500:". I suspect this is because the URL length is exceeding 2083 characters. Even though some of the solutions to this problem was to add the below tag to the web.config file in the reportserver folder and the ReportManager folder, it didnot work for me. Can someone please help?

tag-->

Louie

The error is due to having too many values in the dropdown menu and the URL most likely exceeds the maximum length as you noted. A workaround for this would be to create an option in the dropdown itself to "Select All" and let the stored procedure do the rest of the work.

If you are using a query to populate the available values for the parameter, you can do a UNION to include the "Select All" option.

Note with this solution, you will need to have a user defined function to split values, as SSRS sends back multiple values to SQL Server in a comma separated format.

CREATE FUNCTION dbo.udf_Split
(    
    @RowData NVARCHAR(MAX),
    @Delimeter NVARCHAR(MAX)
)
RETURNS @RtnValue TABLE 
(
    ID INT IDENTITY(1,1),
    Data NVARCHAR(MAX)
) 
AS
BEGIN 
    DECLARE @Iterator INT
    SET @Iterator = 1

    DECLARE @FoundIndex INT
    SET @FoundIndex = CHARINDEX(@Delimeter,@RowData)

    WHILE (@FoundIndex>0)
    BEGIN
        INSERT INTO @RtnValue (data)
        SELECT 
            Data = LTRIM(RTRIM(SUBSTRING(@RowData, 1, @FoundIndex - 1)))

        SET @RowData = SUBSTRING(@RowData,
                @FoundIndex + DATALENGTH(@Delimeter) / 2,
                LEN(@RowData))

        SET @Iterator = @Iterator + 1
        SET @FoundIndex = CHARINDEX(@Delimeter, @RowData)
    END

    INSERT INTO @RtnValue (Data)
    SELECT Data = LTRIM(RTRIM(@RowData))

    RETURN
END
GO

Your query to populate available values for the parameter would look something like this:

SELECT 'Select All' AS "FilterValues"

UNION

SELECT FilterValues
FROM tbl_AvailableValues

Your procedure to get your data would look something like this:

CREATE PROCEDURE usp_GetData @FilterValues NVARCHAR(MAX)

AS

BEGIN

    IF @FilterValues = 'Select All'
    SELECT *
    FROM tbl_Data;

ELSE 

    SELECT *
    FROM tbl_Data
    WHERE tbl_Data.FilterColumn IN (SELECT DATA FROM udf_Split(@FilterValues,','));

END

GO

Hope this helps

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

SSRS 2012: How to get a "Select All" that returns NULL instead of an actual list of all values from a multi-select parameter?

From Dev

SSRS Single report parameter used to set dataset query parameters

From Dev

SSRS Group by in the List Report

From Dev

SSRS report parameter - single selection dropdown with ''select all''

From Dev

SSAS report action to pass multi-value list values to a SSRS report parameter

From Dev

Report Parameter validation in ssrs report

From Dev

Adding values to a Report when there is no Data in query SSRS

From Dev

Pentaho Report Designer: Passing list of values as parameters to report

From Dev

AX SSRS Report Parameter is shown but not useable in the report

From Dev

SSRS multi-select parameter can't capture values with comma

From Dev

SSRS report - Casing parameter

From Dev

SSRS validating parameters and stopping report

From Dev

Passing parameters to a report in SSRS but no report

From Dev

SSRS - Getting report selected parameter with JavaScript

From Dev

SSRS datepart #error in report

From Dev

SSRS Passing multi-select parameter to report

From Dev

How to pass all values of a field to a sub-report as a parameter in SSRS?

From Dev

Add value of TOP (ALL) to parameter in SSRS report connecting to SSAS cube

From Dev

SSRS - list of parameters not visible after redirecting to a report

From Dev

How to display a multi valued parameter in an SSRS report without using a drop-down list

From Dev

SSRS Multiple Field Values Not Passing As Parameters To Drilldown Report

From Dev

SSRS multi-select parameter can't capture values with comma

From Dev

Oracle APEX select values into classic report rows

From Dev

SSRS passing multi-value parameters to open report in additional window

From Dev

Run SSRS report for all parameters, then each parameters

From Dev

How to select values for parameters in SSRS based on a previous parameter's selection?

From Dev

SSRS Report Works when the Parameter uses the "=" Operator but not "IN"

From Dev

How can I remove the commas from a multi-select parameter in Cognos 10 Report Studio?

From Dev

SSRS Report - Multi Valued Parameters Executed One at a Time

Related Related

  1. 1

    SSRS 2012: How to get a "Select All" that returns NULL instead of an actual list of all values from a multi-select parameter?

  2. 2

    SSRS Single report parameter used to set dataset query parameters

  3. 3

    SSRS Group by in the List Report

  4. 4

    SSRS report parameter - single selection dropdown with ''select all''

  5. 5

    SSAS report action to pass multi-value list values to a SSRS report parameter

  6. 6

    Report Parameter validation in ssrs report

  7. 7

    Adding values to a Report when there is no Data in query SSRS

  8. 8

    Pentaho Report Designer: Passing list of values as parameters to report

  9. 9

    AX SSRS Report Parameter is shown but not useable in the report

  10. 10

    SSRS multi-select parameter can't capture values with comma

  11. 11

    SSRS report - Casing parameter

  12. 12

    SSRS validating parameters and stopping report

  13. 13

    Passing parameters to a report in SSRS but no report

  14. 14

    SSRS - Getting report selected parameter with JavaScript

  15. 15

    SSRS datepart #error in report

  16. 16

    SSRS Passing multi-select parameter to report

  17. 17

    How to pass all values of a field to a sub-report as a parameter in SSRS?

  18. 18

    Add value of TOP (ALL) to parameter in SSRS report connecting to SSAS cube

  19. 19

    SSRS - list of parameters not visible after redirecting to a report

  20. 20

    How to display a multi valued parameter in an SSRS report without using a drop-down list

  21. 21

    SSRS Multiple Field Values Not Passing As Parameters To Drilldown Report

  22. 22

    SSRS multi-select parameter can't capture values with comma

  23. 23

    Oracle APEX select values into classic report rows

  24. 24

    SSRS passing multi-value parameters to open report in additional window

  25. 25

    Run SSRS report for all parameters, then each parameters

  26. 26

    How to select values for parameters in SSRS based on a previous parameter's selection?

  27. 27

    SSRS Report Works when the Parameter uses the "=" Operator but not "IN"

  28. 28

    How can I remove the commas from a multi-select parameter in Cognos 10 Report Studio?

  29. 29

    SSRS Report - Multi Valued Parameters Executed One at a Time

HotTag

Archive