I am trying to create an optional prompt on the universe side. I have been reading through some material and below is the prompt I have created but when I try to parse I get an error about ‘parsing default parameter (7th parameter)’.
I have tried a few different combinations but I cannot get it to parse. Can anyone see where I am going wrong?
Many thanks!
Jen
Code: @Prompt(‘Select Reporting Unit of Measurement 1’,‘A’,‘Lov Objects\Unit - Reporting Unit’,mono,constrained,Persistent,Optional)
A) I thought that as of XI 3.1 SP3 optional prompts are NOT supported on the universe side
B) Nevertheless, what happenes when you use this object in a Webi document?
Yes a little more searching on these boards led me to understand this as you described.
However I did get the object to work and appear as an optional prompt on the report side, but then I ran into the issue which has been described in the post I have linked below.
I think I might have to do as they suggest and maybe enter a wild card in there. The only problem is that the prompt I am trying to make optional is to pick a unit of measurement (a conversion is then done) but if they don’t pick anything then it should just be blank… not sure how I am going to get on with this…
I got optional prompt working by doing something like this
trunc(@Prompt(‘Select Reporting Unit of Measurement 1’,‘A’,‘Lov Objects\Unit - Reporting Unit’,mono,constrained,Persistent,’ ',optional))
Baiscally I used a ’ '(blank value) as my default value and used a trunc function to prune the bank at the report level. this worked for me some time back hope its work for you as well.
Basically Optional prompt tries to eliminate the condition thats been applied on the query. But trying to hardcode it in the Universe means the report sql wont be able to discard it. If i’m not wrong thats the reason why we get the error. But as a workaround pass a dummy value as default value for which the condition fails if its opt’d out by the user.
Say for eg:
Prompt Definition @Prompt(‘End Date’, ‘A’,MONO,FREE,not_persistent,‘99999999’},optional)
Report SQL:
SELECT
COL1, COL2, COL3 FROM
TABLEX WHERE XDATE = @Prompt(‘End Date’, ‘A’,MONO,FREE,not_persistent,‘99999999’},optional).
So here if user enters the date the prompt will pass the date to XDATE otherwise it will pass 99999999 which is a dummy date value not present in the tablex.
This is how i’ve used it in my universe copy!! and it works!!
Could you elaborate on those two approaches. Both seems to limit sql query to a value which is not present in DB, what means return nothing. But it should return all values possible ,right.
Sorry, I realize you posted this about a month ago, but I’ll throw this out there anyway; you may be able to use it in the future. See my post on creating an optional prompt in the universe (with screen shots).
You’ll have to download the document from the post.
Hi, and thanks for your effort and the post. A couple things: one is, you need a closing parenthesis right before the final brace. The second is that, in SQL Server, I had to put in a ‘%’ for the default value; it didn’t like a space and returned no rows. So, in SQL Server the code would look like this:
Customer.Customer Name LIKE {fn concat(@Prompt(‘Name Like’,‘A’,Mono,Free,Not_Persistent,{’%’},optional),’%’)}
When this runs, of course, you will see a % filled in as the default, but if you just run it as is you will get all rows.
The real limitation of this approach (besides the amount of time I have to spend on it) is that it only works with ‘Mono’, one value to select, because LIKE can only handle ‘Mono’. Obviously you can get multiple values by either leaving the default (get everything) or type in ‘J’ to get all names with J in them, but, if you wanted to get just John and Bill, for example, you couldn’t do it.
If someone knows, or wants to spend the time to figure out, how to combine LIKE and IN, please let us know. Otherwise, you may as well use the old method described in my previous post, or use the optional prompt at the report level.
We can only hope that Business Objects gets the message that this is something the BO community would like to be able to with just a simple additional prompt parameter at the universe level.
(BOB member since 2007-07-06)
(BOB member since 2002-06-20)
(BOB member since 2010-12-10)
(BOB member since 2007-11-07)
(BOB member since 2002-08-29)