Dynamically setting parameter in the Report
Hasitha
Sri Lanka
Hi,
I'm going to have a query something like this "Select * From ReportDefinition Where 1=1 AND ?" in the Query wizard and going to pass a parameter "Where ReportId = 20 AND Format = 'PDF'" from the code behind, since my client needs to handle parameter list dynamically. Idea here is to replace whole where clause with the parameter.
I tried but Query wizard giving me the following error.
Error
Deferred prepare could not be completed.
Statement(s) could not be prepared.
An expression of non-boolean type specified in a context where a condition is expected, near '@P1'.
Is there any possible way to do this.
Thanks,
Hasitha.
I'm going to have a query something like this "Select * From ReportDefinition Where 1=1 AND ?" in the Query wizard and going to pass a parameter "Where ReportId = 20 AND Format = 'PDF'" from the code behind, since my client needs to handle parameter list dynamically. Idea here is to replace whole where clause with the parameter.
I tried but Query wizard giving me the following error.
Error
Deferred prepare could not be completed.
Statement(s) could not be prepared.
An expression of non-boolean type specified in a context where a condition is expected, near '@P1'.
Is there any possible way to do this.
Thanks,
Hasitha.
Comments
In this case you should not use parameters. Replace the sql text in code:
TableDataSource table = Report.GetDataSource("Your_table") as TableDataSource;
table.SelectCommand = "select * from .... ";
This way we can set the where clause without a problem. But if we have a report with more than one TableDataSource attached and we need to get only the TableDataSource which is actually used by the report as the data source. Since in my case I need to change the where clause only in the TableDataSource which is actually used by the report dynamically and end user does not know the TableDataSource "Alias".
Is there any way get only TableDataSource which is used by the report or any other way to overcome this problem.
Thanks,
Hasitha.
If a report has several datasources, most probably that they are all used in the report. For example, the report may have two top-level databands connected to two different datasources, or it may be a master-detail report.