Maximum Items in Multivalue parameter

[Sobha]

Hi, 

  We have a paginated report. It has 2 parameter. One parameter is multivalue, where we can enter text values. Each Items is of size 10 char long. 

Requirment is,  we have to enter max 3000 to 4000 items to the multi value parameter.

here we have 2 issues,

1, In the query, we are retrieving  values using 'IN ' clause. the IN clause has the restriction of 1000 items in Oracle. 

2, Is it possible to send 3000*11 char to Back end.

Do we have restrictions of max size of the parameter vlaue send to Back end?

like I can send all 3000 id to backend as procedure input parameter..

Thanks in advance

Sobha

[v-menakakota]

Hi @Sobha ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-menakakota · Accepted solution]

Hi @Sobha ,

Thank you for reaching out to us on the Microsoft Fabric Community Forum.

In Power BI Paginated Reports, multi-value parameters are natively supported and can be passed to a backend database as a single comma-separated string. This is particularly useful for your scenario, where you need to send 3000–4000 items (each 10 characters long, totaling approximately 33,000–44,000 characters) to a stored procedure to avoid IN clause limitation of 1000 items. Power BI Paginated Reports allows you to concatenate the selected parameter values into a single string using a custom expression, which can then be passed to a stored procedure for parsing.

If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.

[Sobha]

Hi,

Wheni am passing the multi-value parameters as an argument to the Oracle Procedure, it is giving the error as

ORA-06550: line 5, column 6:
PLS-00306: wrong number or types of arguments in call.

Do we have to convert the multi-value parameters to any string?

as per my understaing, the multi-value parameters and sperated by comma and for the procedure call the input parametes are seperated by commas. So these are considered as arguements.

Any workaround for this?

TIA

Sobha

[Sobha]

Hi, 

I am not sucessful for sending the Multi value parameter to Oracle Procedure.

I have created a SP in  Oracle and calling the same from Power BI Paginated Report like below

Declare xx SYS_REFCURSOR;
Begin
DORIS.XYZ_collections_select_SingleParam(
:prSubscriber,
xx );
DBMS_SQL.RETURN_RESULT(xx);
End;

:prSubscriber is the multivalue parameter, which is of type text.

if there is only one value then it works fine. But if there are more than one, it gives the error

ORA-06550: line 5, column 6:
PLS-00306: wrong number or types of arguments in call.

I think I am missing some steps in beween.

Any Inputs?

TIA

Sobha