Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations John Tel on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Can prompts handle simultaneous multiple entries and parse the values?

Status
Not open for further replies.

baycolor

MIS
Mar 23, 2005
148
US
Hi,

I'm using CR XI and a Teradata database.

I have a report that groups by employee id. The output that's important to me is in the GF1 section; GH1 and Detail sections are suppressed. The report also contains about 10 parameters/prompts. One is for an employee id. Another is for a supervisor id. Both are set to "Allow Multiple Values". There is not a list of values associated with either parameter.

The two parameters work in conjunction with each other. That is when record selection occurs I can enter 123 for the employee id and 456 for the supervisor id. By doing this my report will include all employees that have a supervisor id of 456 and the employee with an id of 123. Note: Employee 123's supervisor is not 456.

Also IGNORE can be entered for either parameter so that records selection doesn't consider the parameter at all.

The following Report-->Selection Formulas-->Record code accomplishes this and works...

Code:
(
   if {?and Employee ID} = "IGNORE" AND {?or Supervisor ID} = "IGNORE" then
     (
      If {?and Employee ID} = "IGNORE" then true;
      If {?or Supervisor ID} = "IGNORE" then true;
     )
   else
     (
      {CRAL_V_INTERNAL_COMMS.employee_id} = {?and Employee ID} 
   OR
      (
       if {?or Supervisor ID} <> "IGNORE" then
       {CRAL_V_INTERNAL_COMMS.supervisor_id} = {?or Supervisor ID} else
       if {CRAL_V_INTERNAL_COMMS.supervisor_id} = "IGNORE" then
       true
      )
     )
)

The problem my users are having is when they go to enter an employee id the prompt window only allows them to enter one at a time. Enter an id, then click the arrow button to move it to the selected employee id portion of the window. Not fun when you have a bunch to enter.

They want to be able to enter a delimited string of employee id's like
125,948753,72753508,934
then click the arrow once.

I have attempted using an array in the Report-->Selection Formulas-->Record area and populating it with a delimited string value from the prompt. Can't get the array code to work though. Following:

Code:
stringvar array TheEmpIdValues := split({?and Employee ID}, ",");

keeps giving the message "This array must be subscripted. For example: Array". I've played around but can't get it to work and I've used this same array syntax in other formulas. If I could have created the array I would have spun through it and incorporated it into the existing logic for employee id and supervisor id.

I did attempt the array approach using a regular formula (code above works in the formula by the way) but this approach doesn't work. I end up creating a TRUE or FALSE value based upon what the database returns which is outside of my Report-->Selection Formulas-->Record formula - I'm out of the "OR" logic that is working with my supervisor id.

So...

Is there a simple solution / way in CR to setup a parameter so it parses what's entered automatically and enters it into the selected values window as individual entries? I've looked pretty hard but haven't found anything.

If not is there a way to make this work?

Thanks.
 
Your code looks overly complicated, but I won't correct that here.

Crystal won't parse anything into a window, if you mean will it automatically determine that what was enetered is not what should have been entered and parse it out to an array instead.

The way I would resolve this in Oracle is to use an Add Command and the translate function.

You don't want a array type of parameter anyway if they are placing them all in one string:

Hav e them enter them as a comma delimited string.

select * from sysadmin.customer, sysadmin.registry
where ','||translate({?Parameter},'^ ','^')||','
like '%,'||translate(table.field,'^ ','^')||',%'

That way the database does the work.

Otherwise I think that you'd have to use suppression in the report to accomplish this, which will prove slow.

Also ,might consider using a Stored Procedure to sort this out.

-k
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top