Forum Discussion
PowerBI Report Builder query parameters
Hello people
Could please someone help?
I use PowerBI Report Builder with Data source Oracle Database(ODP.NET) Dataset based on Text query. Query is expecting text parameter with multiply values. These values passed in national character set as in: cust_id IN (:customer_id) is passed as cust_id IN (N'31341234134kwer', N'3898798734asf'). Trouble is Oracle in this case converts cust_id to national character set and so doesn't use index based on cust_id.
How can I set PowerBI Report Builder to pass parameters as they are without N at the start so Oracle can use index in query?
6 Replies
Hi, gvinokur
Not sure what you mean.
Can you share relevant screenshots to explain further?
Best Regards,
Community Support Team _ EasonThanks for your reply. I attached here screenshots of data source, data set and parameter properties below. When I run report in PowerBI Report Builder it let's me pass many customer_id values. Because it executes long time I ran the following queries on Oracle db using SQL Developer:
select sid, sql_id, serial#, osuser
from SYS.GV_$SESSION
where username = 'user name used in Datasource connection';
and
SELECT SQL_FULLTEXT
FROM SYS.GV_$SQL
WHERE SQL_ID = '4vwzntwmagup9';
SQL_ID value is taken from result given by first query. It gave me text of SQL statement actually being passed to and run by Oracle. It's in this SQL statement I saw WHERE condition became
WHERE cust_id IN (N'31341234134kwer', N'3898798734asf')
So parameter's values have been passed in national character set. This causes Oracle to convert cust_id column value to national character set before checking and so prevents it from using index based on cust_id.
Please let me know if you need any more details.
Best Regards,
gvinokur
Hi gvinokur.
Have you found a slution for that? We have axactly the same problem and still searching for a fix.
