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-->
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.
Comments