Parameter with multiple values in command line

[21128]

I have created a static parameter called {?Account}in command line.
In query i have a condition where it checks users.account_number = {?Account}
In this case while i run the report, for example if i enter the parameter value to be 1000 it gives me the correct data. It works fine for one value. how will i be able to pass multiple values at once (ie. if i want to run the report for account 1000 and 2000), how do i do it?
I tried to change the condition to: users.account_number in ({?Account) and set the parameter to accept multiple values.
this condition works fine for one parameter value but for multiple values it throws an error saying failed to retrieve data from the database(MYSQL)
Is it possible to set to select all {?Account} parameter values to run at once as one of the options?

Thanks,
-P

 

[CoSpringsGuy]

are you talking about in the SQL query command line? Did you select the allow multiple values box when you created it?

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[21128]

Yes i am talking about the SQL Query Command line. I first created the parameter in command prompt itself and later edited it to allow multiple values box as well. With that if i select a single account it works fine but if i select multiple account then it gives an error saying failed to retrive data.

 

[CoSpringsGuy]

Which version oif Crystal Reports are you using? I dont remember which version implemented the "Allow Multiple Values" selection box in the Command Parameter.. This is different than in the report parameter itself.

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[21128]

Its Crystal Reports XI R2

 

[CoSpringsGuy]

I dont have access to XI at the moment but try creating your parameter field from the SQL dialogue box and not in the report itself. Select "Allow Multiple Values" from that dialgue. When you try to edit the parameter from the report, you will not be able to change those.. The SQL code should use in and not =

Ex.
SELECT User_Master.USER_ID
FROM SQLUser.User_Master User_Master
WHERE User_Master.USER_ID in {?UserID}


_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[lbass]

CR XI does not accept multiple value parameters. See faq766-6779 for a work-around. Or use start and end parameters to define a range of values.

-LB

 

[RTag]

If you don't have subreports you can create a report with a multivalue parameter , add your current report as a subreport and pass the multivalue parameter from the main report to the subreport using a formula, which will join the values - for example Join({?Account},','). The value send to the subreport will be a comma separated string, xml string or a number. You will need to parse it and then you can use it to filter the data in your command.

There are 4 ways to join/parse the values, check this article to see which one will work best for you:

http://www.r-tag.com/Pages/MultivalueParametersWithSQLSP.aspx

At the very bottom are some sample Crystal reports formulas.

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.