Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
peacherry
Frequent Visitor

Cannot view report on powerbi report server with stream was too long error

Hello,

 

I use powerBI report server sep 2021 when open some report that execute store procedure on sqlserver

it shows error:

 

An error occurred during client rendering.Stream was too long.

Screen Shot 2564-10-20 at 16.26.22.png

As I check store procedure on sqlserver it already executed finish but in Powerbi report server cannot rendering so Do you have any idea about it? 

 

thank you,

Peach

 

 

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @peacherry ,

 

Reporting Services has limits on the parameter size. For details, please check this blog: Passing Large Report Parameters – Prologika.


 

Issue: The filter list could get rather large. Even if the application follows the Excel filtering pattern to include only the parent node if no child nodes are selected, you might end up with a list consisting of hundreds, perhaps even thousands of accounts. However, Reporting Services has limits on the parameter size. When you test the report in the Report Designer, the report parameter is limited to some 32,000 characters. The Report Viewer doesn’t have this limitation so a string-type report parameter can deceptively take the entire list. However, a change was made in SQL 2012 to limit the parameter size during report rendering. Specifically, ParameterInfoCollection.ToUrl throws an exception “The value of parameter ‘param’ is not valid.” when a parameter value exceeds 65,520 characters. That’s because this method calls the .NET System.Uri.EscapeDataString method which has the 65,520 limit. Interestingly, the report query is executed but the error is thrown after the dataset is processed.

 


 

Then we can speculate that PBIRS also has this limitation. Please check if the workarounds in the above blog work in your scenario.


 

Resolution: What if you still need to pass a parameter value whose size exceeds 65,520 characters? Here are some options:

  1. Although not a solution, the easiest approach is prevention, such as to limit the user selection to 65,520 characters.
  2. Another option is to save the report value to a database and pass the table key to the report parameter. Then, the report query can join to the report parameter table. This approach may work well for relational reports but it doesn’t work so well with cube reports because you can’t join the cube to a relational table. You might try to default the report parameter to a database query that brings the report value from the database but you will still get the above error. For cube reports, one option might be to concatenate the report query text with the filter from the database. Another option could be to write an SSAS stored procedure that retrieves the filter from the database and generates an MDX set.
  3. Yet another option is to compress the report parameter. For example, MDX has a rather verbose notation. Instead of passing the member unique name, e.g. [Account].[Account Level 1].&[1000], consider collapsing the level name to |al1|.&[1000] and then expanding it on the report side by replacing the token with the attribute or level unique name. This approach may reduce the parameter list x10 and help you overcome the 65K limit.

 


 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
Icey
Community Support
Community Support

Hi @peacherry ,

 

Reporting Services has limits on the parameter size. For details, please check this blog: Passing Large Report Parameters – Prologika.


 

Issue: The filter list could get rather large. Even if the application follows the Excel filtering pattern to include only the parent node if no child nodes are selected, you might end up with a list consisting of hundreds, perhaps even thousands of accounts. However, Reporting Services has limits on the parameter size. When you test the report in the Report Designer, the report parameter is limited to some 32,000 characters. The Report Viewer doesn’t have this limitation so a string-type report parameter can deceptively take the entire list. However, a change was made in SQL 2012 to limit the parameter size during report rendering. Specifically, ParameterInfoCollection.ToUrl throws an exception “The value of parameter ‘param’ is not valid.” when a parameter value exceeds 65,520 characters. That’s because this method calls the .NET System.Uri.EscapeDataString method which has the 65,520 limit. Interestingly, the report query is executed but the error is thrown after the dataset is processed.

 


 

Then we can speculate that PBIRS also has this limitation. Please check if the workarounds in the above blog work in your scenario.


 

Resolution: What if you still need to pass a parameter value whose size exceeds 65,520 characters? Here are some options:

  1. Although not a solution, the easiest approach is prevention, such as to limit the user selection to 65,520 characters.
  2. Another option is to save the report value to a database and pass the table key to the report parameter. Then, the report query can join to the report parameter table. This approach may work well for relational reports but it doesn’t work so well with cube reports because you can’t join the cube to a relational table. You might try to default the report parameter to a database query that brings the report value from the database but you will still get the above error. For cube reports, one option might be to concatenate the report query text with the filter from the database. Another option could be to write an SSAS stored procedure that retrieves the filter from the database and generates an MDX set.
  3. Yet another option is to compress the report parameter. For example, MDX has a rather verbose notation. Instead of passing the member unique name, e.g. [Account].[Account Level 1].&[1000], consider collapsing the level name to |al1|.&[1000] and then expanding it on the report side by replacing the token with the attribute or level unique name. This approach may reduce the parameter list x10 and help you overcome the 65K limit.

 


 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.