List Variable as query parameter
Posted: Fri May 26, 2017 11:37 pm
Hi,
I have a sample report attached with a sample database. In the report I have a list variable (varResType) where users can choose a resource type (Labor, Equipment or Material).
I'd like to be able to pass what the user chooses to a query using a parameter. Something like the following... select count(*) as RCnt from Test_Data WHERE RESOURCE_TYPE in (@paramResType).
I have the parameter setup as a varchar with a size of 50. The parameter expression I use is a string variable called varResChosen. I set the value of varResChosen in the BeforeRender event of the report with the following expression... varResChosen = varResType.ToQueryString().
Unfortunately I can't get the value of the parameter to be recognized in the query - eventhough it appears to be in the correct format. I've added columns to the report displaying the value of the varResType,varResChosen, and @paramResType and they all seem to be correct. No matter what I try, the row count is always 0. If I manually type in the parameters such as... select count(*) as RCnt from Test_Data WHERE RESOURCE_TYPE in ('LABOR','MATERIAL') I get a correct number of rows returned from the query.
I have tried setting the datatype of the parameter to be Text, but that doesn't help. Maybe I should be trying to set the value of varResChosen in a different event,
or maybe I should be going about this in a different way.
I'm using Stimulsoft Reports.Web build 2015.3 with Flash Player 25.
I hope someone can point me in the right direction.
I have a sample report attached with a sample database. In the report I have a list variable (varResType) where users can choose a resource type (Labor, Equipment or Material).
I'd like to be able to pass what the user chooses to a query using a parameter. Something like the following... select count(*) as RCnt from Test_Data WHERE RESOURCE_TYPE in (@paramResType).
I have the parameter setup as a varchar with a size of 50. The parameter expression I use is a string variable called varResChosen. I set the value of varResChosen in the BeforeRender event of the report with the following expression... varResChosen = varResType.ToQueryString().
Unfortunately I can't get the value of the parameter to be recognized in the query - eventhough it appears to be in the correct format. I've added columns to the report displaying the value of the varResType,varResChosen, and @paramResType and they all seem to be correct. No matter what I try, the row count is always 0. If I manually type in the parameters such as... select count(*) as RCnt from Test_Data WHERE RESOURCE_TYPE in ('LABOR','MATERIAL') I get a correct number of rows returned from the query.
I have tried setting the datatype of the parameter to be Text, but that doesn't help. Maybe I should be trying to set the value of varResChosen in a different event,
or maybe I should be going about this in a different way.
I'm using Stimulsoft Reports.Web build 2015.3 with Flash Player 25.
I hope someone can point me in the right direction.