List of column values in @Prompt

[SergeS]

Hi,

It is possible to create a prompt asking the user to select one of the values in the declared list. However, I want to use a dynamic list of which the values are obtained from a specific database column.

F.e.: In my database I have a table called Region containing three regions: region 1, region 2 and region 3.

I want the user to select an region for which the measures should be calculated. However, I don't want to do the following:
@Prompt('Select region','A', {'region 1','region 2', 'region 3'), MONO, CONSTRAINED)

because when a new region is added to the database table the universe has to be changed!

The list of values from which the user can choose should be the result of something like: select distinct region from Region.

How should I realise this?

Thanks in advance,

Serge

 

[blom0344]

Hi Serge,

If you assign a list of values to an Object, BO inherently does a distinct lookup on the column you are basing the list of values on. The method you are describing is the alternative, namely limiting users to fill in a certain value. If you refresh the list of values BO will run a distinct lookup into the column and give you what you want !!! T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[SergeS]

If I have interpreted your solution correctly I have to conclude that it doesn't work.

I have defined the class Geography in which I have defined a dimension object called Region.
For this object the select statement is: region.name

I am trying to define the prompt in the where-clause. I have tried the following:

region.name = @Prompt('Select region','A','Geography\Region', MONO, CONSTRAINED)

When running a report I get a popup window, however pressing the button 'Values' doesn't give the list of values.

By the way: I am trying to do this in the universe!

 

[saramaia]

Try this SergerS,

in the designer, edit the properties of the object 'Region' you defined. Go to tab properties. In the bottom, you have something related to the list of values and there is a "show" button there.. Click there.. that´s what will appear to you when you run the report. You have the "automatic actualization" check box that updates that list of values every time you use it...

Hope it helps...

Sara

 

[blom0344]

Hello Serge,

You have to distinguish between using list of values that are associated with an object and are meant to give the end-user a distinct view of all possible values and the predefined prompts that can be designed just to prevent this and force users to select from a limited list or - in the case of FREE - to submit a value from heart. It all depends on the nature of your object. Some list of values are completely static (for instance something like 'month' will never see more than 12 values) while the list of values associated with a customer dimension can change during the day when new customers are added. In certain cases you want to prevent users from using a list of values altogether if using such an object in a condition would lead to non-indexed queries which perform very badly (for instance on free-text fields).
I think saramaia's remark works if you assign a list of values to an object in the universe, but not in a predefined prompt you are building.
T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[SergeS]

Thanks, I already had a search list linked to the object. However, I discovered the problem:

I am using two prompts: one in which a date is asked and one in which the region is asked.

In the report both prompts appear in one single popup window. After entering a date the user goes to the region input field. When he presses the button 'Values' ANOTHER popup window appears again asking for a date and a region (probably because in the prompt definition I am refering to the region object in which both prompts are used). The only way to obtain the list of possible values and to select one of them is to CLOSE the window.

This is odd isn't it?

I think I have to overlook the I way I defined and I am using the prompts.

Thanks people.