Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Hi all
I need to create a paginated report with
- a multivalue parameter PeopleIDs, specifying a list of person IDs
- a dataset connected to an Oracle database, using that parameter
I used the suggested path, for the dataset
- defining the parameter expression as :peoplepar =JOIN(Parameters!PeopleIDs.Value,”,”)
- joining the source table with a table function that splits that expression and returns a collection
SELECT some_cols FROM source_table st JOIN TABLE(split_tf(:people_par)) par ON (st.id = par.column_value)
The report is passing a single string, concatenating parameter values, and the data source undoes this, splitting the string into a collection, before using it.
It works fine till one or two thousand of values; increasing the number of values this approach lead to errors;
isn't there a way to pass directly a collection to Oracle?
thanks
Luigi
Solved! Go to Solution.
Hi @10246532 ,
Thanks for the additional clarification.
You're correct , in Oracle (unlike SQL Server) you cannot directly bind a SSRS multi-value parameter (which comes as an array) into an Oracle table function without additional handling, especially when using standard OLEDB/ODAC drivers.
Issues might be:
You can try below workarounds :
Actually, There’s no native way to pass a true array from SSRS into Oracle via ODAC 19.3 without string-splitting or temp tables.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! |
Regards,
B Manikanteswara Reddy
Hi v-bmanikante
thanks for your replay
I opted for the comma-separated string approach, adding to the report a second parameter in order to paginate the multivalue parameter.
It is not clear the second approach (GTT); how can i load the IDs into a temporary table first?
bye
Luigi
Hi @10246532 ,
Thank you for reaching out to Microsoft Fabric Community forum.
@Akash_Varuna Thank you for your quick response.
If you're running into issues with passing a large number of values through a multi-value parameter in a paginated report (like PeopleIDs), especially when using Oracle as the data source, the string-splitting approach might not scale well.
A better and more efficient solution is to use Oracle collection types. Instead of passing a long comma-separated string and splitting it in SQL, you can:
This method is cleaner, faster, and doesn't break when you have thousands of IDs to pass.
You can check the following Microsoft documents for better understanding and for alternate solutions:
How to pass multi value parameters in SSRS for the Oracle Database using OLEDB Connection - Microsof...
Pass a report parameter within a URL - SQL Server Reporting Services (SSRS) | Microsoft Learn
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Regards,
B Manikanteswara Reddy
Hi v-bmanikante
thanks for your replay; I've already seen that documents.
It seems that a multivalues parameter can be passed to a Transact-SQL instance,
specifying a wher clause like that: "WHERE column IN (@parameter)".
Unfortunately, a similiar approach fails with a Oracle instance;
I tried to define a table function accepting a collection, and to invoke it with this query:
SELECT column_value val
FROM TABLE(my_tf(:Parameter))
I obtained the error
ORA-06553: PLS-306: wrong number or types of arguments in call to 'MY_TF'
I defined the input parameter of table function alternatively as
- a nested table,
- an associative array,
- a varray;
none of these is correct.
I am using driver odac 19.3
bye
Luigi
Hi @10246532 ,
Thanks for the additional clarification.
You're correct , in Oracle (unlike SQL Server) you cannot directly bind a SSRS multi-value parameter (which comes as an array) into an Oracle table function without additional handling, especially when using standard OLEDB/ODAC drivers.
Issues might be:
You can try below workarounds :
Actually, There’s no native way to pass a true array from SSRS into Oracle via ODAC 19.3 without string-splitting or temp tables.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! |
Regards,
B Manikanteswara Reddy
Thanks Akash_Varuna,
that is the question: how can I pass an array from a paginated report to Oracle?
Hi @10246532 Instead of passing a concatenated string, you can use a PL/SQL associative array or a pipelined table function to pass the parameter as a collection. You could aslo try to load the values into a global temporary table (GTT) and use it in your query.
User | Count |
---|---|
85 | |
82 | |
66 | |
52 | |
48 |
User | Count |
---|---|
100 | |
49 | |
42 | |
39 | |
38 |