Limit my Drop list in Dynamic Parameter 3

[socalvelo]

CR 11
Oracle db

I want to limit selections in my dynamic drop list to only names associated with a specific case number. I am using two tables for this report. For the purpose of this thread I will call them Table.case.information and Table.InvolvedPersons. The common field is called {case.number}. I linked these tables with an inner join and "enforced both" selected.

I want the user to first select a case number (specified by a mask)and typed manually. Only records matching this case number will be used for the report. The user then would select names associated with this case number from the Table.InvolvedPersons.

I am having trouble setting up this dynamic parameter so only names associated with the case number will appear in the drop list. I keep getting 1000 names.

Can I create a cascading parameter where the case number is first entered manually thus allowing the user to select from only names associated with this case number? There should only be a few names to pick from. I tried this by first creating a parameter field where the case number is selected. Then the second parameter field is the dynamic pick list.

Any suggestions?

 

[lbass]

What is the problem with using the case number as the first level of the dynamic cascading parameter? Please explain. If you want to limit the case numbers, you could use a command to populate the picklists.

-LB

 

[socalvelo]

LB,
The problem with the case number is that there are several hundred thousand of them. I am trying to limit the list to a date range if possible. Can you give an example on creating a command to populate the pick list.

 

[lbass]

Select caseinfo.caseno, invpers.name
from caseinfo
inner join invper on
caseinfo.caseno=invper.caseno
where caseinfo.date>={?StartDate} and
caseinfo.date<{?EndDate}+1

Create the parameters on the right within the command area. Do not link the command to the main report tables (ignore the warning).

Then in the main report, create the dynamic parameter adding {command.caseno} as the first parm and {command.name} as the second one. In the record selection formula area, set the main report table fields to each parameter.

This will result in two sets of prompts, first the dates and then the dynamic parm prompts. The parameter screen might be confusing (showing the dates a second time, with earlier dates), but the results should be correct. What you see might depend upon whether you have installed service packs (I'm not necessarily recommending doing this or saying it would fix the issue, not sure).

If you can write the command without using parameters, you wouldn't have the problem, e.g., referencing today-90 days.

-LB

 

[IdoMillet]

One of the 3rd-party Crystal Reports viewers listed at

http://kenhamady.com/bookmarks.html

provides an additional cascading parameter functionality that allows you to start with a Type_In parameter, feeding into and restricting further dynamic parameter cascades.

hth,
- Ido

view, export, burst, email, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[socalvelo]

LB,
Thank you - this worked well and got me on the right track.

As a follow-up question, The names in the table are associated with an involvement code from {invper.invcode}. There are about 10 different involvement codes.

When a user is prompted for the name, I would like them to be able to see the involvement code when selecting the person's name i.e. DRIVER - Jones, Bob. DRIVER is from {invper.name}. Jones, Bob is from {invper.name}

Since I am using a command to populate the dynamic parameters, can I use a formula where the involvement code and name are combined. I did include the involvement codes in my second cascading parameter, but I would like the user to see what codes are with which name before selecting the name.

 

[socalvelo]

Correction to my previous post:
DRIVER is from {invper.invcode}

 

[lbass]

Change the command to:

Select "caseinfo"."caseno", "invper"."name", "invper"."name"||' '||"invper"."invcode" "NameCode"
from "caseinfo"
inner join "invper" on
"caseinfo"."caseno"="invper"."caseno"
where "caseinfo"."date">={?StartDate} and
"caseinfo"."date"<{?EndDate}+1

Then use {command.name} as the value, and {command.NameCode} as the description field for the parameter. Then select the "prompt with description only" option.

-LB