Conditions with Prompts

system · November 17, 2010, 1:02pm

I am trying to create a condition in the universe which will prompt the users to select either a single value, multiple values or type the work ALL to get all of the values but am getting an error. - Please help!

The syntax I am trying is (error is missing right parenthesis)

DECODE(@prompt(‘Enter Profile(s) or ALL’,‘C’,‘Risks\Risk Profile’,multi,constrained),‘ALL’,‘ALL’,RISK_PROFILES.risk_profile_name)
=@prompt(‘Enter Profile(s) or ALL’,‘C’,‘Risks\Risk Profile’,multi,constrained)

If I replace the @prompt with an @variable statement (below) it works but the users don’t get a LOV and there are thousands of values in the list.

DECODE(@variable(‘Enter Profile(s) or ALL’),‘ALL’,‘ALL’, RISK_PROFILES.risk_profile_name) = @variable(‘Enter Profile(s) or ALL’)

Joanna.Adde@eaga.com (BOB member since 2010-11-17)

system · November 17, 2010, 1:08pm

Try these FAQs

https://bobj-board.org/t/15227/2
https://bobj-board.org/t/15227/9

And welcome to B B!

Mak 1 (BOB member since 2005-01-06)

MarkP · November 17, 2010, 1:15pm

You shouldn’t be using @Variable. @Prompt is used for prompts.

system · November 17, 2010, 2:20pm

Brilliant thanks - the solution on the first link worked

Joanna.Adde@eaga.com (BOB member since 2010-11-17)

system · November 17, 2010, 2:22pm

You will still need the second link to create your custom LOV…:).

Mak 1 (BOB member since 2005-01-06)

system · November 17, 2010, 2:24pm

Doesn’t the 3rd from last parameter in the @prompt statement mean that all values from the column “Risk Profile” are selectable in the LOV?

Joanna.Adde@eaga.com (BOB member since 2010-11-17)

system · November 17, 2010, 2:28pm

I meant displaying ALL to the user…

Mak 1 (BOB member since 2005-01-06)

system · November 17, 2010, 2:31pm

No ALL is not a value. I means when the prompt is displayed (when the report is refreshed) and the “Values” button is selected then all of the values that the column contains appear to the user.

I assume that is because teh parameter ‘Risks\Risk Profile’ is present. Based on this what else would a customer LOV give me unless I only wanted a subset of the values to be available?

Joanna.Adde@eaga.com (BOB member since 2010-11-17)

system · November 17, 2010, 2:33pm

Or did you mean the word ALL can be typed instead of *?

Joanna.Adde@eaga.com (BOB member since 2010-11-17)

system · November 17, 2010, 2:40pm

Hi Joanna,

Yes, of course, you will get all the customer values in the database, unless you have any row restrictions.

Let me clarify 8), you wish the users to also be able to select ‘ALL’, or ‘*’, or whatever, to show all records, correct?
The second link shows you how to insert the ALL into your Customer LOV, so the user can still pick this as a choice from the list, even though it is not a value in your DB column.
This, of course, may not be important for you, or your users, but it can be for others…

Cheers,

Mark.

Mak 1 (BOB member since 2005-01-06)

system · November 17, 2010, 2:43pm

Right I’ve got you. Yes I want them to be able to view all records is required but not too bothered about it being from a list - i’m sure typing a * is easy enough!

Use ful to have the link though incase I need in future - Thanks

Joanna.Adde@eaga.com (BOB member since 2010-11-17)

system · November 18, 2010, 4:20pm

Have you exported the LOV with the universe, this is under the objects properties, in Designer?
Deski will be using a local copy on your PC.
To be honest, one of this size will take a long time to refresh anyway, especially, if it comes from a non-dimension table.

EDIT
Gulp, I’m now talking to myself

Mak 1 (BOB member since 2005-01-06)