topic Re: PowerBI Report Builder query parameters in Report Server

PowerBI Report Builder query parameters

gvinokur — Tue, 29 Nov 2022 23:32:15 GMT

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?

Re: PowerBI Report Builder query parameters

v-easonf-msft — Thu, 01 Dec 2022 08:31:37 GMT

Hi, gvinokur

Not sure what you mean.

Can you share relevant screenshots to explain further?

Best Regards,
Community Support Team _ Eason

Re: PowerBI Report Builder query parameters

gvinokur — Fri, 02 Dec 2022 04:21:06 GMT

Hi v-easonf-msft

Thanks 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

Re: PowerBI Report Builder query parameters

keping — Mon, 05 Dec 2022 04:20:00 GMT

Hi gvinokur.

Have you found a slution for that? We have axactly the same problem and still searching for a fix.

Re: PowerBI Report Builder query parameters

gvinokur — Mon, 05 Dec 2022 04:57:53 GMT

Hi keping, no I haven't got solution

Re: PowerBI Report Builder query parameters

v-easonf-msft — Mon, 05 Dec 2022 06:27:39 GMT

Hi, gvinokur

Please check if below tutorials could help:

Add a multi-value parameter to a paginated report

All About Multi Value Parameters in Power BI Paginated Report | Tips and Tricks in Paginated Reports

PAGINATED REPORT (MULTI VALUE PARAMETER)

Best Regards,
Community Support Team _ Eason

Re: PowerBI Report Builder query parameters

gvinokur — Tue, 06 Dec 2022 02:33:18 GMT

Hi v-easonf-msft

Thank you for your suggestions. My problem however lies not in building report with multi-value parameter - I've done it as described in videos you linked. The problem is that when report is executing query on Oracle it passes to Oracle text string containing SQL statement. In that statement text parameters are listed with prefix N. Like I stated above: cust_id IN (N'31341234134kwer', N'3898798734asf'). That means parameter values have been given in national character set. Since cust_id is VARCHAR2 it's been converted to NVARCHAR2 before comparing with parameters values. That in turn is stopping Oracle from using index based on cust_id column and consequently causes performance problems.

I am looking for a way to let PowerBI Report Bulder pass text parameters without N at the beginning of parameter value. It's got to be some Report Builder's configuration settings or at least I hope so...