Can prompts handle simultaneous multiple entries and parse the values?

[baycolor]

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...

(
   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:

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.

 

[synapsevampire]

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

 

[baycolor]

Thanks!