cancel
Showing results for 
Search instead for 
Did you mean: 

data foundation @prompt

Former Member
0 Kudos

Hi all,

I created a derived table in data foundation and put a @prompt in where clause for user to input.

if i type:

@Prompt('ID: ','N') - > it's OK

if i type:

@Prompt('ID: ','N','DB\Daypart\ID', mono, constrained)-> There's an error "Invalid Prompt definition.  (IES 00005)"

Please help me!

Thanks,

Danh.

Accepted Solutions (0)

Answers (1)

Answers (1)

former_member189638
Active Contributor
0 Kudos

Classes and Objects are created in the Business Layer. Derived Tables are created in the Data Foundation layer which is below the Business Layer. Hence, the Classes and Objects that you create in the Business Layer are not available in the Data Foundation layer. 

Try the below and it will work fine.

@Prompt('ID: ','N','', mono, constrained)

Former Member
0 Kudos

I have another question:

I have a query which has param for user to input.

SELECT sd.A, sd.B, sd.C

FROM    sd

WHERE sd.entity = 'Sche_day'

AND      sd.id = :chl                     --to be provided by user

UNION

SELECT ss.A, ss.B, ss.C

FROM    ss

WHERE ss.act = 'loc'

AND      ss.id = :chl                     --to be provided by user

I created derived table for this query in data foundation, then create a class and objects corresponding with the table in business layer.

SELECT sd.A, sd.B, sd.C

FROM    sd

WHERE sd.entity = 'Sche_day'

AND      sd.id =  @Prompt('ID: ','A','', mono, constrained)                  

UNION

SELECT ss.A, ss.B, ss.C

FROM    ss

WHERE ss.act = 'loc'

AND      ss.id = @Prompt('ID: ','A','', mono, constrained)                  

when I create a report using the objects, a prompt appears to input value for id but there is no list of values for user to choose.

A list of value for user to choose is all what i want.

I found that creating an object for id in business layer, then create a filter for it. however id comes from 2 table ss and sd. This means users have to input 2 times although one time is enough.

Could you show me how can i achieve this?

former_member189638
Active Contributor
0 Kudos

Try the below option.

1. In the Data foundation layer , goto Parameters and List of values tab

2. Create a list of value with custom SQL to select distinct ID values

3. Then create a Parameter where you put the prompt text as ID: and associate the list of values that you have created. 

4. Then in derived table use it as  sd.id in @prompt(parameter) and ss.id in @prompt(parameter)

Former Member
0 Kudos

Hi Rakesh,

In that case, how do I use a field instead of hardcoded LOVs ?

Need help in syntax

Thanks very much

Regards

Rashmi