Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


How do I dynamically assign a value to a parameter

Posted on 2009-04-07
Medium Priority
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 600 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 300 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}
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

LVL 35

Assisted Solution

James0628 earned 600 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
How to increase the row limit in Jasper Server.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Suggested Courses

721 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