Forum Discussion
query cassandra database in report builder
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!
2 Replies
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!
Hi, Davis. Thank you for your reply. I'm using Cassandra database instead of SQL database. I cannot create this function in db.
