Solved

How do I dynamically assign a value to a parameter

Posted on 2009-04-07
9
725 Views
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}
Parameters:
--------------
(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
     
Formula:
---------
(1) {@The_Selection}
     The content is:
      IF {?The_Mode} = 'A' THEN
          " 'USA' "
      ELSE
          " 'UK' "

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


WELL ... NOTHING HAPPENS ...

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}

 ... ANY IDEAS ... ANYONE?

THANKS
0
Comment
Question by:Yossele
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 100

Accepted Solution

by:
mlmcc earned 200 total points
Comment Utility
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?

mlmcc
0
 
LVL 4

Assisted Solution

by:bleach77
bleach77 earned 100 total points
Comment Utility
why don't you change it to "SELECT * FROM Employees WHERE country =  {@The_Selection}" instead?
0
 
LVL 4

Expert Comment

by:bleach77
Comment Utility
{?The_Country} := {@The_Selection}
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 200 total points
Comment Utility
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}.

 James
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:Yossele
Comment Utility
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
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
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).

 James
0
 

Author Comment

by:Yossele
Comment Utility
James0628
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) ...
Thanks
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
What you are really looking for are optional parameters which are added in CR 2008.

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
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
REPORT_INDICATOR='A')
 OR
({?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.

 James
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now