How do I dynamically assign a value to a parameter

Report uses Crystal Reports 10
Back-End is MS-SQL Server 2000
Database is Northwind
Table is Employees
Report feeds from a command
The sql code is:
      SELECT * FROM Employees WHERE country =  {?The_Country}
(1) {?The_Country}  
    (data type is string)
     posible values are 'USA' or 'UK'

(2) {?The_Mode}
    (data type is string)
     posible values are  A  or  B
(1) {@The_Selection}
     The content is:
      IF {?The_Mode} = 'A' THEN
          " 'USA' "
          " 'UK' "

The Record selection formula contains the following:
      {?The_Country} = {@The_Selection}


The content of {@The_Selection}
does not get assigned to {?The_Country}.

and I know {?The_Country} = {@The_Selection}
evaluates as True or False

so, I forgot how to assign
the value inside of {@The_Selection} to {?The_Country}


Who is Participating?

Improve company productivity with a Business Account.Sign Up

mlmccConnect With a Mentor Commented:
You can't.

A parameter gets its value when you select the value in the parameter selection screen.

A formula evaluates to a value.

You can compare the 2 but you cannot assign the value of one to the other.

What are you using the The_Mode  for?

bleach77Connect With a Mentor Commented:
why don't you change it to "SELECT * FROM Employees WHERE country =  {@The_Selection}" instead?
{?The_Country} := {@The_Selection}
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

James0628Connect With a Mentor Commented:
It seems like what you're trying to do is allow the user to select A if they want the USA or B if they want the UK.  If so, why not just have them select USA or UK to begin with?

 If I'm right, and if you really want them to select A or B for some reason, you can set the {?The_Mode} parameter so that the actual parameter values are USA and UK, but the descriptions are A and B, and set the parameter to only display the description.  That way the user will only see the A and B, but the actual values are USA and UK.  Then use {?The_Mode} in your SQL command instead of {?The_Country}.

YosseleAuthor Commented:
So there is no way to dynamically assign a value to parameter {?The_Country} without the user interacting with the parameter selection screen?  
It is hard for me to believe that Crystal Reports is so  limited.

Thank you guy for the replies.

Please, I am opened to other ideas or suggestions.  Thanks.  Yossele
Well, I don't really follow what you're trying to do.  You've got one parameter that's A or B and one that's USA or UK, and apparently you want to set the second one based on the first one?  Why?  With those limited choices, it seems pretty pointless.  It occurs to me that you may really be trying to do something more complicated and this is just an example, but you didn't say that.  :-)

 CR XI has the capability to dynamically get the default values for a parameter by creating a query that is executed when you run the report, and the results of that query are used as the default values for the parameter, but that doesn't sound exactly like what you're trying to do (and would be big time overkill for the scenario that you described anyway).

YosseleAuthor Commented:
Thank you for your reply.
You are very intuitive, and correct, I am using this example to represent something more complex.
I will go ahead and give the details:
I have a query, and in the where clause there is a portion of the code that should change depending on {?The_Mode} ['A', 'M'] A=Automatic(default value) or 'M'=Manual
if automatic the portion to substitute in the sql query should read: AND REPORT_INDICATOR='A'
if Manual then the user should fill in two dates through parameters {?BeginDate} and {?EndDate}, and the portion to substitute will be an evaluation of the string:
" AND BEG_DATE='"+{?BeginDate}+"' and END_DATE='"+{?EndDate}+"' " 
which could be something like:
  AND BEG_DATE = '2009-03-14'  and  END_DATE = '2009-03-20'
Unfortunately I am pressured by time, and no time to relax and let an elegant and less complicated solution popup in my brain.
It looks like this might explore another solution.

So, what is on you mind(s) ...
What you are really looking for are optional parameters which are added in CR 2008.

I haven't used CR 2008, so I'll defer to mlmcc on that.

 In CR 10, what you're describing would normally be handled by having all 3 parameters, {?The_Mode}, {?BeginDate} and {?EndDate}, but ignoring the date parameters in your code unless {?The_Mode} was "M".  Something like:

({?The_Mode}  = 'A' AND
({?The_Mode}  = 'M' AND
BEG_DATE={?BeginDate} and END_DATE={?EndDate})

 So, the report would always ask for {?BeginDate} and {?EndDate}, but would only use them if {?The_Mode} was "M".  Probably not what you wanted to hear, but that would be the "normal" way to handle something like that.

 If your report does not include any subreports, you might be able to fudge your way around this.  I'm really not sure.  What I'm thinking is that you would make your current report a subreport and create two versions of it that used two versions of the CR command, one that looked for (REPORT_INDICATOR = 'A'), and one that looked for (BEG_DATE = {?BeginDate} and END_DATE = {?EndDate}).  The {?BeginDate} and {?EndDate} parameters would be in the second subreport, as opposed to the main report.  Then the main report would accept the {?The_Mode} parameter and execute one subreport or the other, depending on that parameter.

 The question is, would CR prompt you right away for the {?BeginDate} and {?EndDate} parameters in the second subreport, even if you ended up not executing that subreport, or would it wait until the report actually tried to execute that subreport, and not prompt you at all if you ended up using the first subreport instead?  I'm really not sure.  If CR automatically asks for those parameters, whether you end up using that subreport or not, then this isn't going to help.  But if CR only asks for those parameters if/when the main report actually tries to execute the second subreport, then it might work.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.