Nov 01 2008
Designer XI 3 New Feature: Extended Prompt Syntax
The @Prompt() function has been around for a very long time. There have been subtle changes made to it over the years to support some of the new Web Intelligence extended options but they were not always documented. I don’t know about you, but I find using undocumented features a bit daunting. 🙂
Documentation is Good
Designer 3.0 is the first time that I have been able to find the extended prompt syntax documented. It even mentions the primary_key attribute which was apparently present (and usable) in XI R2. This feature allows me to create prompts in the Universe Designer that will still leverage Index Awareness, assuming I have set that up.
Here is the prompt syntax shown in the XI 3 Designer manual:
@Prompt(‘message’,’type’,[lov],mono/multi,free/constrained/primary_key,per
sistent/not_persistent, [{‘default value’:’default key'[,’default value’:’default
key’,…]})
I’ve covered the prompt syntax before so I won’t review everything. But the new features are interesting. The new feature primary_key was used in my “Ninja” presentation from last year with a disclaimer about it being an “undocumented feature” and the note of being a bit risky to use as a result. Now it is not only documented but there is a modifier for the default value option as well.
Using Index Aware Objects With Prompts
Recall that index awareness is designed to create more efficient SQL. Instead of
RESORT.RESORT = 'Bahamas Beach'
I would see
RESORT.RESORT_ID = 2
With the new prompt parameter I can specify the default value in a value:key pair, like this:
RESORT.RESORT_ID = @Prompt('Please select Resort', 'A', 'Resort\Resort', mono, primary_key, not_persistent,{'Bahamas Beach':'2'})
The option mono of course means I will only select one value. That is not new. The option primary_key tells the SQL generator that this object is index aware, and that I want the key to be used rather than the value. The option not_persistent is used to tell the SQL generator that I don’t want to retain the last user selected value. And finally, the string 'Bahamas Beach':'2' is used to show that the default value shown to the user is Bahamas Beach but the default value provided to the prompt is the key value 2 instead.
Special note: if I want to use a default value, then I have to specify not_persistent. If I don’t, then the default value is only good the first time the query is refreshed. From that point forward the last user selection will be used instead.
What About Those {}?
A reader with extremely good attention to detail will notice that the syntax I posted above does not match what was shown in the user manual. In order to get the object to parse I had to follow the exact syntax shown in the function help in Designer rather than what is in the documentation. What’s different? The code I used has curly braces { } around the value:key pair at the end. The curly braces are also used when providing a hard-coded list of values, so I guess that’s not too surprising.
Conclusion
The primary_key and default:key additions to the @prompt() syntax are quite powerful, and should be well-received by universe designers. I feel much more comfortable using something that is documented (even with slight errors) than using a feature that isn’t detailed in the documentation or help files anywhere. The concept of index awareness just became a lot more attractive with this addition.
This feature was one of several mentioned in my Designer Essentials presentation delivered at the 2008 GBN conference. This feature was covered on page 9.
This is nice but I don’t often use hard-coded LOV’s and am wondering whether the syntax will accept dynamic values derived from the database? In other words, can you use index aware by referencing a database attribute rather than a database field value?
Thanks,
Keith
Hi, Keith, and welcome. At this time I don’t believe you can have any sort of dynamic value in the default, index aware or otherwise.
Hi Dave,
Thanks for the explanation but i think it will be great if designer gives us the option for selecting current month or date as default which i am waiting for years
Hi, Anil, and welcome to my blog. I absolutely agree that we need this sort of feature. In fact, I am hoping to open an Influence Council through the GBN based entirely on prompt handling improvements.
Hi Dave,
I heard from a person that is close to BO that 3.0 (or 3.1) supports “today” or “now” (or something similar) as default value, which is converted into current date in prompt by BO. Do you know if this is true?
Regards,
James
No, I have not heard that at all. It’s certainly not in any of the documentation I read. I can test in 3.0, but I haven’t installed 3.1 anywhere yet.
I’m wondering if there’s any syntax that allows you to flag whether or not a prompt is mandatory. Since this is possible in Webi reports by ticking a flag it should be the same for predefined prompts, don’t you think?
Hi Dave,
Can you please help me out on how to link the universe LOV to free hand SQL prompt.It will be grateful if you explain with a small example.
Best Regards,
Vikram
The only way that I know of to link a universe LOV within a free hand SQL data provider is to also have a universe query in the document. In order to have a list of values, you have to be able to reference an object in the universe. With FHS there is no universe, so it doesn’t work.
In my opinion, this is just one of many reasons not to use FHS in a production report.
What about the 8th parameter User:n that determines the sort order when the prompts are displayed ?
This is present in XIr2 and can be seen when looking at the SQL for a query within WebI.
The only problem I had was that the Webi prompts are automatically numbered 0 thru n for the 8th parameter and I wanted to display my predefined prompts first and giving them a value of 0 did not achieve what I wanted.
I tried using negative numbers and found that they came out ahead of the 0 value one from WebI but were sorted alphabetically on the text of the prompt.
I had to play around with the text of the prompt to get the alphabetic sorting correct but left the the User:n parameter as User:-
Also Sandor (comment 7) have a look at the SQL for a WebI generated prompt and see if there is another parameter that determines if a prompt is mandatory or not ?
Its a good Parameter. Thanks Dave for highligting such a beautiful feature.
It took lots of time for BO to come up with the feature while cognos was using it from a long time.
Its really a nice feature provided the object have index awareness set but Index awareness doesn’t work for objects with @aggregate_aware() as each alias table in the @aggregate_aware function has a different key. Any work around ?
Hi, SBHUSAN, and thanks for your comment. In my testing, Aggregate Aware measures will work fine with Index Aware dimensions. Aggregate Aware dimensions cannot be Index Aware for the reason you mention.
Hi Dave, Thanks for the quick reply.yes my concern is for Aggregate Aware dimensions. I would love if BO allows to use the Objects instead of DB columns from the SQL editor while selecting the key for index awareness. One quick question regarding Index aware- when set on a object at a hierarchy level higher than the primary key grain, the LOV shows repeated occurance of the object value as it repeats the same value for each primary key. This stops me use index awareness hence i would not be able to use such a nice prompt feature.
I talked about the grain issue (repeating values) in the presentation from last year where I covered Index Aware at some level of detail. Our solution was to snowflake where we wanted to use index aware.
hello…
but i came know that “User:n” works fine in Webi. but doesn’t work in Deski. any workaround ?
thanks in advance.
The ability to order prompts never existed in Deski. The standard workarounds include prefacing each prompt with a sequence number, or ensuring the text came out in the desired order alphabetically.
Dave:
Is there a way to format the date @prompt, for example to generate a query like this one:
trunc(tm_end_time) between trunc(to_date(’04/02/2009′,’dd/mm/yyyy’))
and trunc(to_date(’06/02/2009′,’dd/mm/yyyy’))
thanks
Hi,
I am using Bo Xi r3.
how to use “User:n” parameter in prompt?
i have given this parameter as 8th parameter but am getting parsing error as ‘Error parsing default values parameter(7th parameter)’
can any one help me on this?
thanks,
Srinivas
Hi, Srinivas, and thank you for your comment. Many of the “extended” parameters for prompts that Web Intelligence will create are not fully supported within the Designer application. You really have only a few options. You can ignore the parsing error and save the prompt as it is, export your universe, and test it to see if it works.
Or you can stay within the supported parameters and skip trying to use the prompt order option in Designer.
I have XIR2 SP4 and I am obviously doing something very wrong here because I cannot get these prompts to work for me. They default the values I want and they also show up in the order I want, but they do not render results in the report when I use these parameters.
The syntax I’m using for two prompts is as follows:
@Prompt(‘Please Enter School Division:’, ‘A’,,,{’10’},user:1,),
@Prompt(‘Please enter School District:’, ‘A’,,,{’20’},user:0,)
When I run the report with the appropriate result objects, no data is returned. When I remove the parameters at the end (for example the {’20’},user:0,) )the results show up in the report just fine. Any suggestions would be SO appreciated!
Hi, I think I read where you solved your issue on BOB.
I am using Bo Xi 3.1 SP2 FP 2.2
how to use “User:n” parameter in prompt?
i have given this parameter as 8th parameter but am getting parsing error as ‘Error parsing default values parameter(7th parameter)’
But in BO XI r2 SP2 it is parsing fine.
Hi, thanks for your comment. First suggestion for your question is to reconfirm the syntax for @prompt() shown in the help. If it’s not there, then it probably will not parse.
Next, save the object and export it. Test it. If it works, you can consider leaving the code in place even if it doesn’t parse. However, there is risk involved. Officially with 3.0 the user:n parameter is not supported. It may or may not work once it gets to a report, that’s why you need to export and test.
Ultimately something else may break further down the line if you use unsupported code, so I tend to stay away from the extra prompt values that don’t parse. But that’s just my opinion.
By befault The sysdate is come into promt text box,whenever user refresh the report.
Is it possible?
Clear me about optional prompt
No, not today. But I previously posted a workaround.
Optional prompts are not available in the universe yet.
Hi Dave:
Is it possible to prompt the user for the database user name and password when they refresh a report in InfoView, without using the SDK? What have two connections — one that has a hard coded user name and password and it is used for scheduled refreshes by a super ID. But, using Universe Restrictions, we want to be able to switch to a different connection for regular users when they use that Universe to refresh/create reports and that connection needs to prompt the users for the database name and password. Thanks.
You can override the connection based on user / group membership. In the substitute connection you can use @variable(‘BOUSER’) and @variable(‘BOPASS’) to pass the existing BusinessObjects account information. Does that get you where you need to go?
Thanks, Dave. Unfortunately, these are not options in this case. The database user names and passwords are not the same as the BOBJ ones. SSO is not enabled to the database and not an option they can pursue at this point. Using a single “super id” for the connection is not an option. So the user needs to be prompted at run time, when the report is opened in infoview and refreshed. Let me know if you have any other thoughts on this. Again, thanks for your response!
I tried use this primary_key parameter feature on XIR2 but didn’t worked well. The prompt returned all values of the object, not only that one i had selected.
It’s only officially documented (and therefore I assume supported) as of XI 3.
Is it possible to dynamically generate a value for the ‘message’ parameter in the @prompt command?
Hi, Steve, I don’t think that would work. To be honest, I have never tried it, but I don’t think it’s that flexible. Welcome, and thanks for your question.
It seems we cannot use UPPER before @prompt.Here is the code which does not work for me::
UPPER(TABLE_NAME.PRDCT_NAME) IN UPPER(@Prompt(‘Select Product’ ,’A’, ‘Product\Product Name’,MULTI,)
here is the code which works::
UPPER(TABLE_NAME.PRDCT_NAME) IN UPPER(@Prompt(‘Select Product’ ,’A’, ‘Product\Product Name’,MONO,)
any suggestions/ workarounds ??
Dave,
Its always been a great help whenever I referred your views/posts.I am looking for an easy way to built a Rolling 5 Quarter prompt.
Any help would be appreciated!
hi dave,
i want create optional prompt in universe level pls help me…………
In XI 3.1 optional prompt syntax copied from a Web Intelligence report may work in a universe but is not officially supported.
Dave, I used following syntex but it did not fetch me any row:-
@Select(Plant\plantcd)=@Prompt(‘Select Plant Name’,’A’,’Plant\Plant Name’,Mono,Primary_key,Not_Persistent,{‘Wildwood’:’1043′})
Any suggestions?
I am trying to apply @prompt syntax with primary_key option in my query and it is throwing an error (IES:10901) SAP knowledge base article 2054721. Applying LOV on dimension which contains both numeric and character (not an alphanumeric).
SAP BI 4.0 version.
UDT Module