List Variable as query parameter
List Variable as query parameter
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.
- Attachments
-
- ListVarTest.zip
- (301.81 KiB) Downloaded 302 times
Re: List Variable as query parameter
Hello,
Please check the sample report in the attachment.
Thank you.
Please check the sample report in the attachment.
Thank you.
- Attachments
-
- ListInQuery.mrt
- (7.95 KiB) Downloaded 896 times
Re: List Variable as query parameter
Very Nice Aleksey - that worked. Thank You!
Re: List Variable as query parameter
Hello,
Perfect. Thank you for letting us know about this.
Perfect. Thank you for letting us know about this.
Re: List Variable as query parameter
Hi,
I have a similar question that I asked in May.
using the ListInQuery.mrt file you provided, is is possible to populate the var1 variable at run-time instead of design-time?
What I tried to do is leaving the Request From User checkbox for var1 unchecked and then populating the var1 variable in the
report BeginRender event using statements similar to the following....
var1.Add("Products");
var1.Add("Categories");
Unfortunately this doesn't seem to work - I got no results. Is it possible to do what I'm trying?
Carl
I have a similar question that I asked in May.
using the ListInQuery.mrt file you provided, is is possible to populate the var1 variable at run-time instead of design-time?
What I tried to do is leaving the Request From User checkbox for var1 unchecked and then populating the var1 variable in the
report BeginRender event using statements similar to the following....
var1.Add("Products");
var1.Add("Categories");
Unfortunately this doesn't seem to work - I got no results. Is it possible to do what I'm trying?
Carl
Re: List Variable as query parameter
Could one option be to avoid using variables altogether, and just enter a list of string values in the expression box for a query parameter?
I also have a couple of questions noted in the attached screenshot.
Carl
I also have a couple of questions noted in the attached screenshot.
Carl
- Attachments
-
- ParameterQuestion.JPG (55.51 KiB) Viewed 6016 times
Re: List Variable as query parameter
Hello,
Unfortunately, it is not possible to set string values in the parameters expression. You should use a variable in this case.
Thank you.
Unfortunately, it is not possible to set string values in the parameters expression. You should use a variable in this case.
Thank you.
Re: List Variable as query parameter
OK Alex - Thanks for the guidance!
Re: List Variable as query parameter
Hello
We are always glad to help you!
Please let us know if you need any additional help.
Thank you.
We are always glad to help you!
Please let us know if you need any additional help.
Thank you.