During my report development efforts the other day I knew that I needed a business objects universe-level prompt, but I also knew that it was mandatory to “purge” the prompt values. Like a good little Business Objects developer I always purge all of the data from my reports and reports’ prompts before I publish them for User Testing and promotion to production; however, in Business Objects XI R2 I have found that WebI reports have a strangle-hold on prompt values and they don’t want to give them up without a fight.
So I started looking around to see if I was just missing something obvious. What I found on my search was some completely unsupported extended @prompt syntax. The funny thing was that I did not have to install anything to enable it. The syntax worked immediately, just by passing in the additional parameters. Note: this is unsupported officially by BO, but BusinessObjects obviously developed it for some larger client and slipped it into the XI R2 code. Therefore, there is no promise that this will be supported in the future, but there is hope.
Anyway, on to the syntax:
@Prompt('1) Enter Product Family','A','Product Hierarchy\Product Family',multi,free,[Not_Persistent],[<{default_value(s)_or_leave_blank}>],[User:#])
With this syntax you can now add 3 additional/optional parameters after you universe-level prompt’s “free/constrained” option.
- Prompt Persistence: Default value is “Persistent”; however, the value “Not_Persistent” is the reason most Business Objects WebI report writers want to use this syntax. Using “Not_Persistent” removes the last prompt used with the report; it purges the value. This can be very important in many settings because it can be the difference between an end-user accidentally running the report for some unwanted parameter value.
- Default Value(s): If left blank no default value used. If a value or values is desired then the set should be surrounded by { } characters and separated by commas (much like hard-coded LOV values earlier int he @Prompt parameters). This feature might seem to counter the “Not_Persistent” setting; however, it may be possible that you always want your Web Intelligence report prompt to include the default “*” value even while you are testing or running it for other values. Therefore, some might find that using this option with the “Not_Persistent” option provides value and extended functionality to their WebI reports.
- User:#: This particular value has been ignored by many, but it also provides some interesting value. It allows the universe business objects developer to define the relative order of the prompt. Yes, no more not-so-tricky “1.”, “1)”, or “1-” prefixes. In fact, I believe this option provides better value because it defines order of universe-level prompts and it two prompts have the same “User:#” value then they are sorted alphabetically.
The following are some examples of potentially usage of this extended @Prompt Syntax:
@Prompt('Enter Product Family','A','Product Hierarchy\Product Family',multi,free,Not_Persistent,{'Kiwi','Guava'},User:0)
@Prompt('Enter Product Family','A','Product Hierarchy\Product Family',MULTI,FREE,Not_Persistent,{'*'},User:9)
@Prompt('Enter Product Family','A','Product Hierarchy\Product Family',multi,free,Not_Persistent,,User:1
I have found this syntax to be invaluable in my BusinessObjects Designer and Web Intelligence report development efforts. As good little programmers we know that universe-level prompts are a “best practice”; however, without this syntax they can retain last-run prompt values and cause confusion and even misleading and blatantly wrong business intelligence. This syntax finally brings universe-level prompts into the 21 century and makes our jobs all a little easier and maybe even more secure. 😉
Tip: Including “All” or “*” In Your LOV
Try hard coding “All” or “*” in the object’s LOV, in the universe. You could do this by editing the object’s LOV and adding a query with UNION that will only return the word “All”. You may have to make sure that the LOV for the object is returning a character data type for this to work properly.
Thanks Julian for your quick response. I have modified the syntax a little since I am using sql server instead of oracle and it’s parsed. However, when I ran a webi report using the prompt, it is giving me an error; A database error occured. The database error text is: [Microsoft][SQL Native Client][SQL Server]Conversion failed when converting the varchar value ‘*’ to data type int.. (WIS 10901).
Following are the different systax versions that I have tried. Unfortunately, I am stuck with the same error. Would please take a look to see if I missed anything. Thank you.
convert(char,(@Select(Order\Order Number)) IN @Prompt(‘Enter Order Number(* for All): ‘,’N’,’Order\Order Number’,multi,free) OR
(‘*’ IN @Variable(‘Enter Order Number(* for All): ‘))
convert(char,(@Select(Order\Order Number)) IN @Prompt(‘Enter Order Number(* for All): ‘,’N’,’Order\Order Number’,multi,free) OR
(convert(char,’*’) IN @Variable(‘Enter Order Number(* for All): ‘))
convert(varchar(1),(@Select(Order\Order Number)) IN @Prompt(‘Enter Order Number(* for All): ‘,’N’,’Order\Order Number’,multi,free) OR
(convert(varchar(1),’*’) IN @Variable(‘Enter Order Number(* for All): ‘))
Hi Kel, I think you are almost there, you just went a little too far. Take the “
convert()” off of the'*'. That should resolve your current error. You can’t convert'*'to a character data type, it already is one. 🙂Hi Julian,
As the matter of fact I had already tried what you suggested. It is one of the different syntax versions that I tried and it still gave me the same error.
convert(char,(@Select(Order\Order Number)) IN @Prompt(‘Enter Order Number(* for All): ‘,’N’,’Order\Order Number’,multi,free) OR
(‘*’ IN @Variable(‘Enter Order Number(* for All): ‘))
Thank you.
Hi there, this site is fantastic, thx for sharing.
I’m trying to add 3 optional prompts to a report in XIR2.
1. City
or
2. State
or
3.Zip
so you can select one or two or three choices. I do not want to have a default value in the prompt. So far the query will not run unless you have a value in the other two prompts. So I edited the sql and put a blank value and it works fine however; on the right side I now have a blank value so when I select a value in the prompt, the value is placed below the blank value.
My question is, is it possible that when I select a value the blank space is removed from the right side??
Hi Regis, good question. I think that if the prompts were “MONO” then this would not be an issue, but that may not meet your needs. I have no other idea on how to automatically remove the default values, sorry.
Hi,
I want to create custom prompt in my report side, my Bo version is Xi release 2. I want my prompt should have constant values and user can select one at a time, there are few problems i’m facing with this :-
1. When i create a dummy table with only one column in universe side and create a prompt, Cross join is getting applied in the query.
2. When i create in report side AND condtion is getting applied to query, due to which no data is displayed, because the dummy table doen’t have any relation with other tables. I can resolve this by custom query, but i don’t want to use custom query.
Please help
Hi sam, I assume you created a dummy table to be able to get a custom list of values. If this is true then you add any objects of the dummy table to the universe in a new class that could be named “Dummy Values” or “LOV Only, Do Not Use in Reports”. Then I would set the dummy table to the side away from the other tables in the diagram pane, not attaching it to anything. Then in the objects to which I wanted to have the LOV I would edit those objects, edit their LOV and change the objects out to be the new dummy objects, OR you could just use these objects in the @Prompt LOV parameters.
An example would be a dummy table with a list of possible dates. There are really so many ways you can implement this successfully, it all depends on your requirements and other choices.
Hi there,
I have a report that has 5 SSN prompts. The user can enter up to 5 SSNs, so I need to default them to 999999999. This is what I have:
…AND (
EMPL_SSN = @variable(‘1. Enter SSN:’,’A’,,mono,free,non_persistent,{‘99999999’},User:0)
OR EMPL_SSN = @variable(‘2. Enter SSN:’,’A’,,mono,free,non_persistent,{‘99999999’},User:1)
OR EMPL_SSN = @variable(‘3. Enter SSN:’,’A’,,mono,free,non_persistent,{‘99999999’},User:2)
OR EMPL_SSN = @variable(‘4. Enter SSN:’,’A’,,mono,free,non_persistent,{‘99999999’},User:3)
OR EMPL_SSN = @variable(‘5. Enter SSN:’,’A’,,mono,free,non_persistent,{‘99999999’},User:4)
)
Results are not consistent. Sometimes the prompt values default to 99999999, sometimes they don’t. Is my syntax wrong? Any help would be greatly appreciated.
Hi keala, I would try making these @Prompt() functions. I only reserve @Variable() when I am referencing the value of a prompt defined previously and only with a single parameter, the prompt text. Yes, try switching these to @Prompt() and see how that works. Please report back your results.
Hi Julian,
Thanks so much for replying. I also noticed that I had “non_persistent” rather than “not_persistent”, but that didn’t seem to affect the results.
Anyway, I tried changing the @variable (in the SQL Viewer) to @Prompt, but I get “Syntax error in variable”. Am I’m doing something else wrong?
Please paste the exact SQL and @Prompt() function that is failing and I will take a closer look.
Here it is…
SELECT
EMPL.EMPLOYEE_ID,
EMPL.EMPL_FIRST_NM_UP,
EMPL.EMPL_LAST_NM_UP,
EMPL.EMPL_MIDDLE_NM,
EMPL.EMPL_SUFFIX_CD,
EMPL.EMPL_SSN,
EMPL_ASGNMT.HOME_DEPT_CD,
R_DEPT.DEPT_DSCR_UP
FROM
R_DEPT,
R_UNIT,
EMPL,
EMPL_ASGNMT
WHERE
( EMPL.INTERNAL_EMPL_ID=EMPL_ASGNMT.INTERNAL_EMPL_ID )
AND ( R_UNIT.DEPT_CD=EMPL_ASGNMT.HOME_DEPT_CD AND R_UNIT.UNIT_CD=EMPL_ASGNMT.HOME_UNIT_CD )
AND ( R_DEPT.DEPT_CD=R_UNIT.DEPT_CD )
AND (EMPL_ASGNMT.HOME_DEPT_CD = ‘DDD’)
AND (
EMPL.EMPL_SSN = @prompt(‘1. Enter SSN:’,’A’,,mono,free,not_persistent,{‘999999999’},User:0)
OR EMPL.EMPL_SSN = @prompt(‘2. Enter SSN:’,’A’,,mono,free,not_persistent,{‘999999999’},User:1)
OR EMPL.EMPL_SSN = @prompt(‘3. Enter SSN:’,’A’,,mono,free,not_persistent,{‘999999999’},User:2)
OR EMPL.EMPL_SSN = @prompt(‘4. Enter SSN:’,’A’,,mono,free,not_persistent,{‘999999999’},User:3)
OR EMPL.EMPL_SSN = @prompt(‘5. Enter SSN:’,’A’,,mono,free,not_persistent,{‘999999999’},User:4)
)
Thanks!
It may be a few days before I can run that prompt on my side, but initially I have a thought, why not create a single prompt with MULTI instead of MONO and allow users enter as many SSN as they want?
You probably thought of this and the users can’t trusted to enter multiple values in correctly or they demand multiple entry points or you want to limit them to a maximum of 5, but I thought I would check with you.
Hi Julian,
No problem at all. I appreciate your help. We want to limit the users to a maximum of 5 SSNs.
Thanks again!
Hi Keala,
I pulled your syntax in an Auditing universe and made minor changes and was able to get it to work:
AUDIT_EVENT_DOCUMENT_REFRESH.SERVER_CUID = @Prompt('1. Enter SSN:','A',,mono,free,not_persistent,{'999999999'},User:0)
The problem I think lies in the single-quote character. For Designer you must have the single quote character, the character that is on the the same key as the double-quote character on the US keyboard. You can’t even copy and paste from this article because the fonts were misbehaving and changing the characters. I think I fixed this issue and the syntax I posted above should work even with copy and paste.
Hmm, that’s strange. That’s the character I was using. I copied and pasted your post and got a different error this time (syntax error in variable). I’m running into other problems, so I’m not sure if this could be related to something other issue. Thanks so much for your help, though.
Hey Keala, I recommend making it more simple at first and then more and more complex as you get it to parse.
Hi there,
I am trying to create a prompt which has the highest date value of a special column in a table as default value. I noticed that there already was the requirement to use an object as default but didn’t find an answer which definitely says if that is possible or not. Is there any way to make this work?
Hi Eric, directly it is not possible to meet your requirement, but you could possibly do it by creating a keyword substitution prompt (allow user to enter keywords that your condition translates through SQL logic to values, such as a maximum date). Then you could make the keyword, such a “MaxDate”, the default value. The downside is that this will require that you prompt have a character type of “A” or string and this could have performance impacts on your query. I should write a full-blown article about how I have done this in the past.
Hi Julian,
thanks for your fast reply. I think that should meet my requirement.
Thank you!
Hi All,
When WebI generates @Prompt(‘1. Enter Number’,’A’,,mono,free,Persistent,,User:0) and I click on Run Query, the report just hangs.. Does anybody have a clue why this is happening, and how I can avoid this problem?
Thanks!
Welcome to BOT Jonathan, I could only suggest process of elimination. Remove the prompt and be sure that the behavior goes back to normal. The prompt you describe seems to be a universe prompt, not generated by WebI exactly. Could you share the entire condition object’s contents so that we can try out your syntax in our systems? BTW, what is your BOE server version?
Hi Julian,
Thanks for your help.
Actually, the prompt is generated by WebI and not at universe level. The version of BO we are using in XI R2. The report works correctly without prompts or if the filter is a constant.
Could it be because of the Java version installed? The version being used in version 6 update 11
Thanks for your help.
Jonathan
Hi Jonathan, I have not been able to think of any explanation for this. I would still go for a process of elimination or even just rebuild a new prompt as simple as possible and then build from there.
I would to apply a mask in the prompt. Is it possible ?
In my case, I ask to user to enter an hour, so I would like to be sure that he will enter a number a format like hh:mm and constrained him.
Sorry m@nu, this is not possible. You can put an edit mask in the text of the prompt for visual communication of the requirement, but you cannot enforce the format without some kind of macros attached. Native BO has no such functionality.
Thanks Julian for the response.
Where can I find a such macro to attache ?
Regards
I wish I knew. Sorry m@nu.
Hi All,
I need to creat a object in the BO Universe as “Unit of Measure” and the values shown in this objects are “In Thousands” or “In Million”
Basically a drop down with two options for the user to select at the report level.
How do I create this object in the universe.
Your help would be highly appreciated.
Thanks,
Anushka
Hi Anushka, this is fairly off-topic and I am not sure I can answer you without more information, but I will try.
I can tell you that if you just want a text object that always has the text “In Thousands” then you could just create a string/character object and then set the SQL to ‘In Thousands’. It will fail parse by itself, but it will be fine when combined with any other object in a query.
Let’s try to limit our discussion to the article’s topic.
Julian,
I am trying to build a condition which would have ‘All’ value as default in the prompt and this is what I created,
@Select(Emp Info\Unit Name) IN @prompt(‘Enter Unit Name:’,’A’,,Mono,Free,Persistent,{‘All’},User:0)
But I get error “Parse failed: Invalid definition (UNV0023)”.
Am I doing something wrong here.
I need to add “Current Date” as LOV of one object, which displays today’s date. Could you please help me to do that?
Thanks & Regards,
Muntasir
Hi Chikki, I haven’t tested your code but I think the problem may be that you have the SQL WHERE clause using “IN” and the BO @Prompt is set to “Mono”. I am not sure that this is the problem but I would try switching either the “IN” to a “=” or the “Mono” to a “Multi”. If the error still persists I suggest manually replacing all of your single-quote characters; sometimes you get characters that look like single quote characters, but they are not (this often happens when doing copy and paste from examples on the web or from a Word document).
Hi Muntasir, the word “Current Day” is possible, but a dynamic date, well it is possible too. Firstly, the work “Current Date” may seem useless, but read my comment to Eric above and you will understand its value.
Now if you must have the current date, then this is how you make it happen. You could create a table or a view that could be queried such that it would return the current date always without changing the query. Once you have this just add the table to the universe, don’t bother joining it to anything, and then add the date field as an object. Then edit that objects LOV so that it executes that SQL that will always give the current date. Another trick is to create an object that contains your databases function so that when those functions are placed in the SELECT clause they always give the current date. Another trick would be to change the LOV of an existing object and replace that LOV with some query that you will always give the current date. You need to no a bit more about SQL than BO usually requires, but not too much to get these tricks to work.
BTW, be careful to make sure that the date you put in the LOV has its hours truncated off or set to 00:00:00, unless you want the current time too. 😉
hi….
i have problem where my prompt field always showing the value. But the value is not the last selected value. Anyone knows how to set blank for the prompt field. I try to set the condition object to not_persistent it’s works but then my launch in context is failed then i have to set to persistent. The weird thing is when I open some of the report with the same setting, it does not display the values in the prompt field. Means, all the prompt is empty/blank. So, I have no idea what went wrong with that report. Is there any setting to delete the cookies?
thanks in advance
I solved the problem!
Great news emeralda. Please share the solution when you have a moment.
Actually, what i have done is purge the data.It is to clean up the previous selected data. But how to purge the data???
First, you have to be in editor mode
2nd, on right top there is a purge button, if it is grayed you then you have to run/refresh the report and make sure it return the value. If no data retrieve then we cannot purge the data. Once u hv done, you click the purge button. Go to document properties and select refresh when open. click save and run the report again but do not select or key in any value. Just click cancel. Then go back to the editor mode and remove the selection for refresh when open then click save.Now when you open the report the prompt is blank since there no value selected from the last action. 😉 just try.
Summary:
1) editor mode –> click purge button(on right top) —> tick check box refresh when open (document properties) —–> save
2) report view —> open report —> click cancel
3) editor mode —-> un-tick the check box refresh when open(document properties) —> save
Hi ALL,
My query is when i am caluluate Age of person it calcukate on the bases of sysdate and date of births of the person.
But i want to create one more object to calculate Age.But it shouldnot give age on Sysdate,insted of Sysdate i want Assignment snapshort date.
It can be possible and please send Systex for this.
Thanks,
Gokul
Hi Gokul, you are asking a sQL question. Yes, this is possible, but you would need to find a way to get the date you need in there using SQL. There are so many ways to go at this and without understanding your requirements or data I cannot really help.
Hi Julian,
Gr8 to see some awesome BO tips & tricks shared by you 🙂
I want to know how we can change the order of prompts in Web 3.1???
Thanks,
Ansh
Hi Ansh, on Business Objects XI 3.1 Web Intelligence you will find that the old trick of making the @Prompt text alphabetically sort by prefixing each with numbers still works. However, all prompts that are created at the report level can be ordered in the “Properties” within the “Edit Query” mode. Look for the section called “Prompt Order”, which is near the bottom of the list of available properties. Again, this will only work for prompts created in the report and not for @Prompt conditions coming from the universe. I hope BO fixes this to include both someday soon.
Can anyone please give an example for a date prompt. have been trying for hours now, and cannot make it happen.
@Prompt(‘Choose As Per date’,’D’,,,,Persistent,)
The idea is to use for looking at accounts payable buckets, so later on I need to use the prompt input variable. Anybody has a good example?
br DAvid
Hi Julian,
I have a prompt which has the ‘*’ but is currently not displaying it. anything missing ?. Thanks for you help in advance
@Select(Current\Dept) IN @Prompt(‘Dept:’, ‘A’, ‘ListOfValues\Dept’, Multi, Constrained, Non_Persistent, {‘*’}, User:5) OR ‘*’ IN @Prompt(‘Dept:’, ‘A’, ‘ListOfValues\Dept’, Multi, Constrained, Non_Persistent, , User:5)
Hi Webber, the extended syntax is said notto be supported in BO XI 3.1. I suggest that you put “*” in the prompt text like this:
@Select(Current\Dept) IN @Prompt('Dept (* for all):', 'A', ‘ListOfValues\Dept', Multi, Constrained, Non_Persistent, {'*'}, User:5) OR '*' IN @Variable('Dept (* for all):')Can Anyone explain why my prompt will not work correctly in derived table. It works fine when I make the comparison with The “=”
sign(Phase.State = @Prompt(‘State’,’A’,phase\State,Multi,Free,Not_Persistent)),
but not with the “IN” Keyword(Phase.State IN @Prompt(‘State’,’A’,phase\State,Multi,Free,Not_Persistent)). the code goes like this:
SELECT A.FacilityName,A.FacilityCity,A.FacilityState, A.FacilityZip, (A.FacilityCounty + ‘, ‘ + A.FacilityState) AS FacilityCounty,
@Prompt(‘State’,’A’,phase\State,Multi,Free,Not_Persistent) AS StateFacility
FROM PROV A
INNER JOIN Geo B ON B.AreaKey = A.AreaKey
INNER JOIN @DerivedTable(Phase) ON Phase.AreaKey = B.AreaKey
Where Status = ‘Display’ AND Phase.State IN @Prompt(‘State’,’A’,phase\State,Multi,Free,Not_Persistent)
Hi Julian,
I have two questions. My BO system is XI 3.1. According to your answers i can upgrade it.
1)I want to restrict prompting values to be seen. For example; i have month variable. It has numbers 1 to 12. At prompt interface i want to see just “6,7,8,9,10,11,12” for month then i want to choose one or more of them. Point is restricting the values. It can be done at SQL with a where clause but can it be done at prompt syntax?
2)According to question 1 i want to restrict the seen numbers for each user. For example, if the active user/log in is Seckin then “6,7,8,9” can be seen at prompt, if the active user/log in is Julian then “10,11,12” can be seen at prompt. Is t possible at prompt syntax. I can write a SQL CASE for these but i can not be so useful i think.
Waiting answers ASAP.
Thnx for help
Hi Durant, you should not put a MULTI prompt as a field in your SELECT clause. I think this is your problem. Try this:
SELECT A.FacilityName, A.FacilityCity, A.FacilityState, A.FacilityZip, (A.FacilityCounty + ‘, ‘ + A.FacilityState) AS FacilityCounty, Phase.State
FROM PROV A
INNER JOIN Geo B ON B.AreaKey = A.AreaKey
INNER JOIN @DerivedTable(Phase) ON Phase.AreaKey = B.AreaKey
WHERE Status = ‘Display’ AND Phase.State IN @Prompt(‘State’,’A’,’phase\State’,Multi,Free,Not_Persistent)
Make sure that ‘phase\State’ is a valid class and object in your universe.
Hi Seckin,
1) Yes. You need to defined the desired list of values explicitly in the prompt and you need to set the prompt to “CONSTRAINED”.
2) No and Yes. A single prompt cannot present a different LOV to different users. Having said this, if you set the LOV to pull data from a table and you configure the data in the table so that the desired values are paired with the desired users and you use the @Variable(‘BOUSER’) in the LOV query then you might be able to pull off your requirement.
Hi Kel,
convert(char,(@Select(Order\Order Number)) IN @Prompt(‘Enter Order Number(* for All): ‘,’N’,’Order\Order Number’,multi,free) OR
(‘*’ IN @Variable(‘Enter Order Number(* for All): ‘))
As you have already changed the datatype of Order Number to char, use ‘A’ as datatype in prompt syntax instead of ‘N’. Probably it will work.