How do I dynamically assign a value to a parameter

Posted on 2009-04-07
Last Modified: 2013-11-15
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}


Question by:Yossele
  • 3
  • 2
  • 2
  • +1
LVL 100

Accepted Solution

mlmcc earned 200 total points
ID: 24093790
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?


Assisted Solution

bleach77 earned 100 total points
ID: 24094057
why don't you change it to "SELECT * FROM Employees WHERE country =  {@The_Selection}" instead?

Expert Comment

ID: 24094067
{?The_Country} := {@The_Selection}
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

LVL 34

Assisted Solution

James0628 earned 200 total points
ID: 24096070
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}.


Author Comment

ID: 24096203
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
LVL 34

Expert Comment

ID: 24096373
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).


Author Comment

ID: 24097164
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) ...
LVL 100

Expert Comment

ID: 24101391
What you are really looking for are optional parameters which are added in CR 2008.

LVL 34

Expert Comment

ID: 24103543
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.


Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit If you want to manage em…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question