query cassandra database in report builder

[Tengmin123]

@DavisBI 

Hi, 
I'm using report builder to generate a report and my data source is cassandra database in shared data source in pbi report server.
At first, when I create a data set, my query is:
select * from table where user_id in (@user_id)
and my parameter can mutiselect, then I got error :
Error while executing a query in Cassandra: [33562624] : line 17:18 no viable alternative at character '@'
Then I cancel the multiselect for parameter and use the expression to generate the query and my expression is:
=" select * from table where user_id = '" & Parameters!userid.Value & "' "
Then the report generated very fast 
After that, I tick the multiselect for parameter again and change the expression like :
=" select * from table where log_time >= '" & Format(Parameters!start.Value, "yyyy-MM-dd") & "' and log_time <= '" & Format(Parameters!end.Value, "yyyy-MM-dd") & "' "
Then the report generate very very slow maybe takes aroud 5 minutes.
Anyone help to improve the query of ths dataset? 
Thank you!

[DavisBI]

Hi, @Tengmin123 ,

You can create a function (as shown in the figure below) that can split your PARAMETER from LIST to ('a','b',...) in SQL Server DB then you can using the query like below:

--Create a function, for example: [PBI_REPORT_BUILDER_SPLIT_PARAM]

CHANGE:

select * from table where user_id in (@user_id)

TO:

select * from table 
where user_id in 
    (SELECT [ITEM] FROM [PBI_REPORT_BUILDER_SPLIT_PARAM](@user_id,","))

Otherwise, you will get the error as your mention (Error while executing a query...).

Mark this answer as a solution if this helps, thanks!

[Tengmin123]

Hi, Davis. Thank you for your reply. I'm using Cassandra database instead of SQL database. I cannot create this function in db.