ReportServer Forum

kirti

Member

Registered: 2017-01-13

Dynamic Report Issue

I had created a function to execute create dynamic report. For report without parameter or with date parameter it gives proper output. But when i add other parameter with multiple selection it gives an error saying
Query could not be prepared.Error preparing statement for executing the query select top 0 * from (select * from function(?,?,?,$State,?,?,?,?)wrapped qry)limitqry.

jalbrecht

Administrator

Registered: 2016-10-21

Re: Dynamic Report Issue

Hi kirti,

i might be wrong, but i understand you created a dynamic list report with and without parameters. I assume that both work fine. My question is what is the function you created ?

jan

kirti

Member

Registered: 2017-01-13

Re: Dynamic Report Issue

Hi jalbrecht,
Yes i created dynamic report.
1 - Without parameter it works fine
2-  With Parameter and Single Selection feature is works fine
3-  With Parameter and Multiple Selection feature it is giving below error
Query could not be prepared: Error preparing statement for executing the report query : SELECT TOP 0 * FROM (SELECT * FROM (select * from cashordertrn where warehouse in ? ) wrappedQry) limitQry /* user: 6 */ /* report: 3753316 */ /* token: ca79ff7b-e1ab-4d68-b907-c3b6bd5cf15a */ /* currentuser: 6 */

All i wrote in query was:-

select * from cashordertrn where warehouse in ${warehouse}
1-cashordertrn is table
2-warehouse is column
3-${warehouse} is Datasource Parameter with Multiple Selection feature that i created

Last edited by kirti (2017-10-05 13:20:30)

jalbrecht

Administrator

Registered: 2016-10-21

Re: Dynamic Report Issue

Hi Kirti,

for Multiselction it is best to use $X{IN, MyTableColumn, MyParameterName}. This handles the NULL (-> no restriction) case correctly as well. You find more on this topic here:

https://reportserver.net/en/guides/admi … arameters/ -> check 6.3.3.

Hope this fixes your problem !

wbr jan

kirti

Member

Registered: 2017-01-13

Re: Dynamic Report Issue

how to use SQL Procedure or SP_EXECUTESQL in dynamic list report

kirti

Member

Registered: 2017-01-13

Re: Dynamic Report Issue

Hi Kirti,

for Multiselction it is best to use $X{IN, MyTableColumn, MyParameterName}. This handles the NULL (-> no restriction) case correctly as well. You find more on this topic here:

https://reportserver.net/en/guides/admi … arameters/ -> check 6.3.3.

Hope this fixes your problem !

wbr jan

Hi jalbrecht,

Thank you so much. it's worked