List Variable as query parameter

Stimulsoft Reports.Web discussion
Post Reply
cbrydon
Posts: 107
Joined: Fri May 30, 2014 4:37 pm

List Variable as query parameter

Post by cbrydon » 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.
Attachments
ListVarTest.zip
(301.81 KiB) Downloaded 35 times
Alex K.
Posts: 6476
Joined: Thu Jul 29, 2010 2:37 am

Re: List Variable as query parameter

Post by Alex K. » Mon May 29, 2017 10:00 am

Hello,

Please check the sample report in the attachment.

Thank you.
Attachments
ListInQuery.mrt
(7.95 KiB) Downloaded 78 times
cbrydon
Posts: 107
Joined: Fri May 30, 2014 4:37 pm

Re: List Variable as query parameter

Post by cbrydon » Mon May 29, 2017 1:17 pm

Very Nice Aleksey - that worked. Thank You!
Andrew
Posts: 3710
Joined: Fri Jun 09, 2006 3:58 am

Re: List Variable as query parameter

Post by Andrew » Tue May 30, 2017 4:08 am

Hello,

Perfect. Thank you for letting us know about this.
cbrydon
Posts: 107
Joined: Fri May 30, 2014 4:37 pm

Re: List Variable as query parameter

Post by cbrydon » Fri Oct 06, 2017 4:21 pm

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
cbrydon
Posts: 107
Joined: Fri May 30, 2014 4:37 pm

Re: List Variable as query parameter

Post by cbrydon » Fri Oct 06, 2017 6:26 pm

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
Attachments
ParameterQuestion.JPG
ParameterQuestion.JPG (55.51 KiB) Viewed 1539 times
Alex K.
Posts: 6476
Joined: Thu Jul 29, 2010 2:37 am

Re: List Variable as query parameter

Post by Alex K. » Tue Oct 10, 2017 9:51 am

Hello,

Unfortunately, it is not possible to set string values in the parameters expression. You should use a variable in this case.

Thank you.
cbrydon
Posts: 107
Joined: Fri May 30, 2014 4:37 pm

Re: List Variable as query parameter

Post by cbrydon » Thu Oct 12, 2017 12:51 pm

OK Alex - Thanks for the guidance!
Alex K.
Posts: 6476
Joined: Thu Jul 29, 2010 2:37 am

Re: List Variable as query parameter

Post by Alex K. » Thu Oct 12, 2017 7:03 pm

Hello

We are always glad to help you!
Please let us know if you need any additional help.

Thank you.
Post Reply