Parameter: User selects text but formula uses ID number

[DAH200]

Newbie question. Crystal XI, Oracle data.

These threads are close but more complicated than my problem (I think) thread149-1027872 and thread149-1165168.

I'd like to have a parameter based on fields in this view called SYS_NAME_V:
SELECT SYS_ACRONYM || ': ' || SYS_NAME AS ComboName, SYS_ID FROM SYSTEM_INVENTORY ORDER BY SYS_ACRONYM, SYS_NAME

I would show them the list of ComboName and then have my selection formula use the corresponding SYS_ID to pull a record from SYSTEM_INVENTORY for the report.

My report has SYSTEM_INVENTORY and SYS_NAME_V linked so I have ComboName available for the parameter.

I've got it working with a two-field(?) dynamic parameter that makes a two step process for the user. They select the ComboName and then the SYS_ID dropdown is narrowed down to one choice, the correct ID, and they click that.

The report selection formula is {SYSTEM_INVENTORY.SYS_ID} = {?MySystem - SYS_ID}

But is there a way, in the parameter or in the selection formula, to make it just one click for the user?

Thanks!
Denise

 

[Turkbear]

Hi,
Since SYS_ID is in both datasources use that field as a link between them (View on the left as the 'driving' one)
and make the selection criteria:

{SYS_NAME_V.ComboName} = {?ComboNamePrompt}




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[DAH200]

Thanks Turkbear! Worked like a charm.
Hope you're having fun in the sun!
DH

 

[Turkbear]

Hi,
Very much fun ( even when playing golf badly)

Glad I could help...





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

Or you could have added the ID as the value field in the parameter setup screen and the name field as the description field and then set the parameter to display the description field only.

I also wouldn't ordinarily link the command/query used for the parameter picklist to the tables used in the main body of the report.

-LB

 

[DAH200]

Thanks lbass,
That worked, too. I was ignoring the Description column. Duh!

But my ComboName only showed up in that column if I included the view that creates it in the Database Expert screen and linked it to my main table.

Is there some other way to include the command/query used for the parameter picklist, besides linking it via Database Expert?

Thanks again!
DH

 

[lbass]

If you create it as a command and do not link it, it will still be available for use in populating the parameter pick list. The command will not however be "activated" when running the main report. If you need to reference a field in the command in the main report, then you need to link it. However, this will slow your report, since the linking will occur locally. If you are using a command in your main report, it is best to design it so that it is your sole datasource. You could have a separate command to populate the picklist, and another one as the datasource for your report.

-LB

 

[DAH200]

Interesting!
I tried putting the SQL from my Oracle view into a command and then on the Database Expert Links tab I removed the link between the command and my main table. I got the notice "Your current link configuration contains multiple starting points. Please be advised that this is generally not supported." I assumed I'd get a cartesian product but went ahead anyway.

I tweaked my dynamic parameter to use the value and description from the command and checked my record selection formula. Everything worked great; one result for each system selected.

On the Field Explorer I noticed that the command and its fields seemed available so I dragged the ComboName to my report. That's when I got the cartesian product. So you're right they can't be used.

Thanks again!
DH