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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
LVL 101

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}
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 35

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 35

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 101

Expert Comment

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

LVL 35

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello, In my precious Article  ( saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

617 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