Assistance Required for Resolving URL Character Limit Issue in Paginated Report

[Heena_9980400]

Hi Team,

I hope this email finds you well. I am reaching out for assistance in resolving an issue I am encountering with a paginated report.

The report is designed such that, after selecting multiple parameters, clicking on the Reject column record redirects to a child report.

However, an error is displayed:

"The report parameter list exceeds the limit of 2040 characters. Please reduce the selected values for Zone 1, 2, 3 WO# dropdown."

To provide more context, I have attached the expression currently being used to achieve the functionality:

=IIF(LEN(Globals!ReportServerUrl + "?%2fBRS+Singapore%2fPA+Reports%2fTIJ4%2fSGP_Falcon%2fProduction+Reports%2fReject_Pen_summery_yield&rs:Command=Render&rc:Toolbar=False&StartDate= " & Parameters!parstartdate.Value & "&EndDate= " & Parameters!parenddate.Value & "&ShiftList= " & Join(Parameters!parshiftinclude.Value, ",") & "&moduleky= " & Fields!MODULE_DIM_KY.Value & "&pruntype= " & Join(Parameters!paruntype.Value, ",") & "&Zone1Wo= " & Join(Parameters!Zone1Wo.Value, "&Zone1Wo=")) <= 2083, "javascript&colon;void(window.open('" + Globals!ReportServerUrl + "?%2fBRS+Singapore%2fPA+Reports%2fTIJ4%2fSGP_Falcon%2fProduction+Reports%2fReject_Pen_summery_yield&rs:Command=Render&rc:Toolbar=False&StartDate= " & Parameters!parstartdate.Value & "&EndDate= " & Parameters!parenddate.Value & "&ShiftList= " & Join(Parameters!parshiftinclude.Value, ",") & "&moduleky= " & Fields!MODULE_DIM_KY.Value & "&pruntype= " & Join(Parameters!paruntype.Value, ",") & "&Zone1Wo= " & Join(Parameters!Zone1Wo.Value, "&Zone1Wo=") + "'))", "")

The main concern is ensuring that even after selecting "Select All" for the parameters, the report does not exceed the character limit and continues to function without errors.

If this issue cannot be resolved due to known limitations, I would appreciate any suggestions for alternative approaches. Specifically:

1. Is there a way to handle this within the report itself, avoiding the need to jump to a child report?
2. After publishing to Power BI Service, can any modifications be made to overcome this limitation?
3. Are there any other workarounds to ensure that selecting "Select All" does not lead to an error?

   Looking forward to your valuable suggestions and assistance!

   Thank you.

   Best regards,
   Heena Kousar

[v-csrikanth]

HI @Heena_9980400 

I wanted to follow up since I haven't heard from you in a while. Have you had a chance to try the suggested solutions?
If your issue is resolved, please consider marking the post as solved. However, if you're still facing challenges, feel free to share the details, and we'll be happy to assist you further.
Looking forward to your response!

Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @Heena_9980400 
It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered?
If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

[v-csrikanth]

Hi @Heena_9980400 
We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please accept as solution to help others benefit.
Thank you.

[v-csrikanth · Accepted solution]

Hi @Heena_9980400 
If your datasSource (e.g., SDRDS_wali) is already configured and connected to your database, you do not need to create a new one. Try to create a new dataset under this data source.

Please follow the below steps to create a New Data Source (If required):

Step1: If no data source exists or if you want to use a different one:

1. Right-click on Data Sources (in the left panel).
2. Click on "Add Data Source".
3. Give it a name (e.g., MyDataSource)
4. Select "Use a connection embedded in my report".
5. Choose your database type:
   • SQL Server → Select "Microsoft SQL Server"
6. Enter connection details (server name, database name, authentication).
7. Click "Test Connection" → OK.

Step2: Create a Dataset with a Query

1. Right-click on "Datasets" → Click "Add Dataset".
2. Enter a dataset name (e.g., MyDataset).
3. Select "Use a dataset embedded in my report".
4. Choose the data source you created (or an existing one).
5. Enter the SQL query, adjusting for your database type:
   SELECT * FROM my_table WHERE Zone1Wo IN (SELECT value FROM STRING_SPLIT(@Zone1Wo, ','))

If the above information helps you, please give us a Kudos and marked the reply Accept as a Solution.

Thanks,
Cheri Srikanth

[v-csrikanth]

Hi @Heena_9980400 
Thank you for being part of the Microsoft Fabric Community.

Instead of passing a long list of parameters in the url, store the user’s selections in a temporary database table and pass only a sessionID to the child report. The child report will then retrieve the parameters dynamically.

Modify the Parent Report to Store Parameters

• When a user clicks on a record, store selected parameters in a database table with a unique SessionID.
  

Sample SQL query to insert user selections:

******************************************************************
INSERT INTO ReportParameterStore (SessionID, StartDate, EndDate, Zone1Wo, ShiftList, ModuleKY, PrunType)
VALUES (NEWID(), @parstartdate, @parenddate, STRING_AGG(@Zone1Wo, ','), STRING_AGG(@ShiftList, ','), @ModuleKY, STRING_AGG(@PrunType, ',')

*******************************************************************
This query states that even if "Select All" is chosen, the entire parameter list is stored without bloating the url.

Modify the URL to pass only SessionID

• Instead of passing multiple parameters, modify the url to only include SessionID.

*******************************************************************

= "javascript&colon;void(window.open('" & Globals!ReportServerurl &
"?2fBRS+Singapore%2fPA+Reports%2fTIJ4%2fSGP_Falcon%2fProduction+Reports%2fReject_Pen_summery_yield" &"&rs:Command=Render&SessionID=" & Fields!SessionID.Value & "'))"

*******************************************************************

The url becomes much shorter and avoids encoding overhead.

Modify the child report to retrieve parameters using SessionID.
This allows the child report to dynamically load the parameters instead of relying on a long URL.

*******************************************************************

SELECT StartDate, EndDate, Zone1Wo, ShiftList, ModuleKY, PrunType
FROM ReportParameterStore
WHERE SessionID = @SessionID

*******************************************************************

If the above information helps you, please give us a Kudos and marked the reply Accept as a Solution.

Thanks,
Cheri Srikanth

[Heena_9980400]

Hi @nilendraFabric ,

Is there a way to implement this kind of approach directly in Report Builder (.rdl) itself?

If there are any alternative approaches that I can implement without involving a database, please let me know.

thanks in advance!

[v-csrikanth]

Hi @Heena_9980400 
Yes! you can implement passing comma-separated values when "Select All" is chosen directly in power-bi report builder (.rdl) without involving a database.
By default, report builder sends multiple values as separate parameters (e.g., &Zone1Wo=value1&Zone1Wo=value2). Instead, combine them into a single comma-separated string.

Steps to Implement:

Configure the parameter in report builder:

• Open Report Data Pane, right-click on the parameter (e.g., Zone1Wo), and select Properties.
• In the General tab, enable Allow multiple values to support multiple selections.

Adjust the Parameter Expression in Report Builder:

• Use the JOIN function in SSRS expressions to convert multiple selected values into a single, comma-separated string.

Expression for the dataset parameter:

=Join(Parameters!Zone1Wo.Value, ",")

This ensures that multiple selected values (e.g., value1,value2) are  passed as "value1,value2" instead of separate parameters.

Modify the SQL Query in the Dataset to handle multiple values efficiently, update the dataset query using sql functions that can split comma-separated value.

SELECT * FROM my_table WHERE Zone1Wo IN (SELECT value FROM STRING_SPLIT(@Zone1Wo, ','))

Postgresql query correctly filters based on the selected values without requiring multiple parameters.

SELECT * FROM my_table

WHERE Zone1Wo = ANY(string_to_array(@Zone1Wo, ','))

If the above information helps you, please give us a Kudos and marked the reply Accept as a Solution.

Thanks,
Cheri Srikanth

[Heena_9980400]

Hi @v-csrikanth ,

Here at the left side we have the datasource option.
you are asking me to create a table here by writing query, if yes can you please help me with the steps one by one in ssrs

thanks in advace!

[nilendraFabric]

Hello @Heena_9980400 

The problem seems here because of encoding overhead of url. 

URL Encoding Overhead
• Special characters (spaces, commas, symbols) are URL-encoded, adding extra characters:
• Example: A space becomes `%20` (3 chars), and `&Zone1Wo=` becomes `%26Zone1Wo%3D` when encoded.
• Impact: A URL with 1800 visible chars can exceed 2040 after encoding

Try these

Configure your paginated report to use default parameter values from the dataset. This ensures parameters are omitted from the URL unless explicitly selected by users, significantly reducing URL length:
• Set parameters like `Zone1Wo`, `ShiftList`, etc., to default to “Select All” values in the report itself.
• Modify your URL generation logic to only include parameters where users have made selections, avoiding unnecessary duplication

https://community.fabric.microsoft.com/t5/Service/Paginated-Report-Using-URL-Parameters-and-Select-A...

Instead of `&Zone1Wo=value1&Zone1Wo=value2`, use `&Zone1Wo=value1,value2`.
• In the child report, split the comma-separated string back into individual values using expressions like `Split()`

https://www.kerski.tech/bringing-dataops-to-power-bi-part42/

Hope this helps

Please accept the solution and give kudos if this is helpful