How to create prompts with a default value ?
or
I want to create a prompt which should automatically recognize * when user doesnot selects anything for a prompt in WEB-i.
man4urheart (BOB member since 2003-02-14)
How to create prompts with a default value ?
or
I want to create a prompt which should automatically recognize * when user doesnot selects anything for a prompt in WEB-i.
man4urheart (BOB member since 2003-02-14)
Hello and Welcome!
We have a FAQ which addresses your question. In short, there isnât any easy way to default the prompts other than to allow a wild card to select all values.
Cindy Clayton (BOB member since 2002-06-11)
Thanks Cindy for your kind reply.
This implies user has to explicitely enter the wild card * for selecting all values.
man4urheart (BOB member since 2003-02-14)
There is further discussion on this, butâŚif you run the report and save it with a prompt filled in, that will be the default the next time you run the report.
Eileen King (BOB member since 2002-07-10)
I think thats smart, but not relaible or constructive.
next time you logon on and cache is gone like HIP HIP HURRAY! and there is no default value.
I just tried doing it with NVL function and Decode statement in universe, that too didnot worked.
I donot know why it is not recognising Null value when prompt is left blank in WEB-I.
ANY IDEA?
man4urheart (BOB member since 2003-02-14)
[quote:7b6c9c5325=âMandeep.itanâ]I donot know why it is not recognising Null value when prompt is left blank in WEB-I.
[/quote]
You canât leave a prompt blank, thatâs the entire problem. You have to have something in for each prompt before you can go forward with running the query. So it doesnât matter how creative you get with decode() or nvl() or case or whatever, it wonât work because you never get that far.
In the âgood old daysâ with version 3, you could create prompts and wrap the result of a prompt in a decode() to determine if the user entered a value or not. But starting with version 4, if there is a prompt, you had to fill something in.
The FAQ entry that was linked earlier is a way to work with a special symbol - often a * or % - that the user would enter when they donât want to put something in. But at least one character needs to be entered for each prompt.
Dave
Dave Rathbun (BOB member since 2002-06-06)
[quote:2c6eebb6bb=âMandeep.itanâ]I think thats smart, but not relaible or constructive.
next time you logon on and cache is gone like HIP HIP HURRAY! and there is no default value.
I just tried doing it with NVL function and Decode statement in universe, that too didnot worked.
I donot know why it is not recognising Null value when prompt is left blank in WEB-I.
ANY IDEA?
[/quote]
I found that if you run the report and save that report, next time when you run the report again, the prompt has all the values of the last run. However, it is not good to depend on this. Since everytime you make any changes in the universe, and export it, all the prompts will be empty. I think the safest way will be to use VBA to get the default values for all the prompts.
Lilly
Lilly J (BOB member since 2002-08-22)
Hello Lilly
Would you be kind to share the code in VBA to achieve same.
man4urheart (BOB member since 2003-02-14)
VBA Code to default prompts:
Sub Document_Open()
Dim MyVar As Variable
Set MyVar = Variables.Item("1.Department")
MyVar.Value = "ALL"
Set MyVar = Variables.Item("2.Category")
MyVar.Value = "ALL"
Set MyVar = Variables.Item("3.Start Date")
MyVar.Value = DateAdd("d", -1, Date)
Set MyVar = Variables.Item("4.End Date")
MyVar.Value = DateAdd("d", -1, Date)
End Sub
Andreas (BOB member since 2002-06-20)
Hi, I have a look at the FAQ and this posting but Im still not 100% clear how to achieve what I want.
What I want is to have a prompt, that by default is populated with % wildcard. So as soon as they open the report its ready to retrieve everything and the user doesnt have to even enter a %. However if they want to search on a specifc thing(s) they can by removing the % in the prompt. So far Ive got to
AND T.BANK like @prompt(âLedgerâ,âAâ,mono,free)
Also am I correct in my understanding that if I used a % I cannot also use the same prompt to accept multiple values if the user want to say search on Ledger A and B
Fiona (BOB member since 2002-12-16)
Hi,
I am also looking for default value in a prompt. I saw the vba code which andreas provided in this topic. However, I never used vba and not sure on how to start. can you please throw some light on it.
The code which andreas provided above :
Sub Document_Open()
Dim MyVar As Variable
Set MyVar = Variables.Item("1.Department")
MyVar.Value = "ALL"
Set MyVar = Variables.Item("2.Category")
MyVar.Value = "ALL"
Set MyVar = Variables.Item("3.Start Date")
MyVar.Value = DateAdd("d", -1, Date)
Set MyVar = Variables.Item("4.End Date")
MyVar.Value = DateAdd("d", -1, Date)
End Sub
I acheived using ALL in the prompt, thanks to the FAQâS. Now when the user refreshes the report I want him to see ALL in the prompt box.
Thanks for your help.
-Xen
xen (BOB member since 2004-11-13)
xen â you should realize that if you want to code VBA macros in BusinessObjects full client, you need to licensed for the SDK (Software Development Kit).
Anita Craig (BOB member since 2002-06-17)
I guess we are licensed.Some people used sdk.
I never used it. So not sure what I should be changing the code to acheive the desired.
Thanks.
xen (BOB member since 2004-11-13)
Even if some people use it â thatâs no guarantee that they are licensed for the SDK. And even if they are licensed for the SDK â thatâs no guarantee that youâre licensed for the SDK. You should find out from someone who knows.
Anita Craig (BOB member since 2002-06-17)
Here is a suggestion.
Open up Designer.
Open/Import your universe.
Find the object you are going to use for your prompt. In my example, letâs use an object called âCampaign Typeâ.
Double click on Campaign Type to edit the properties of this object. Click on the âPropertiesâ tab.
Where it says âAssociate a List of Valuesâ, click on the âEditâ button so that the query panel loads up. Campaign Type will be selected in Result Objects. Edit the SQL of the object by clicking on âView SQLâ icon.
Type in SQL like this:
SELECT DISTINCT
sum_CAMPAIGN_DIM.CPG_CAMPAIGN_TYPE_CD
FROM
CAMPAIGN_DIM sum_CAMPAIGN_DIM
where
sum_CAMPAIGN_DIM.EFFECTIVE_END_DATE>sysdate
union
select
â%â
from
CAMPAIGN_DIM sum_CAMPAIGN_DIM
Click on the SQL tick icon, to verify that the SQL compiles ok.
Then click on the check box âDo not generate SQL before runningâ.
Click on OK.
Click on âRunâ button.
Click on the âDisplayâ button. You should now see that one of the available options in the List of Values is a â%â symbol.
Ok, then create your prompt condition.
So it will look something like this:
sum_CAMPAIGN_DIM.CPG_CAMPAIGN_TYPE_CD = @Prompt(â1. Select the Campaign Typeâ,âAâ,âCampaign\Campaign Type Codeâ,MONO,CONSTRAINED) or â%â = @Prompt(â1. Select the Campaign Typeâ,âAâ,âCampaign\Campaign Type Codeâ,MONO,CONSTRAINED)
So what this prompt says is, if the user selects â%â then get all the Campaign Types.
Save your Universe. Export it to the repository if necessary.
Now open up your Business Objects report.
Add the prompt condition to your report.
Run your report select â%â at the prompt.
Then you save your report with the prompt already populated. So that every time the user runs the report, the prompt is populated with this value. You can replace â%â with âALLâ if you want. This is what I have been doing with my reports.
Another thing, if when you run the report and select the â%â option from the prompt, but you then donât want data to be displayed in the report, press the ESC key when the report is running. A message box will pop up asking you to either:
Continue the execution?
Stop the execution and keep partial result set?
Discard the results? (sometimes this is unvailable to select from)
Keep the results of the previous execution?
Select âDiscard the resultsâ. If this option isnât available, then select âKeep the results of the previous executionâ
Click on OK.
So what you have now is your prompt answer(s) populated, but your table/graph in the report isnât populated with data.
Another thing you can do, is create a separate data provider for prompt answers only. What you would do is select the common objects and conditions and just use this data provider for the prompt answers to be populated with default values. You donât use any of these objects on the report.
Also ensure that the data provider you are using for the table/chart/graph contains the same prompt conditions in it as well, so that it gets the prompt answers in the data providers SQL as well.
Hope this helps and makes sense.
aposullivan (BOB member since 2004-07-23)
Worked like a charmâŚthanks for the detailed explanation DaMouse and thanks for the Anitha, for the insight.
xen (BOB member since 2004-11-13)
Hi Andreas,
Iâve looked at the following code and found that it most likely is what I need.
Unfortunately, Iâm not familiar with the SDK or VBA at all. I know that we are licensed for the SDK, and our code is not in VBA but JSP. But I donât know where in the server Iâm supposed to put the above code.
If you could help me in any way I would really appreciate it.
Thank you!
melon (BOB member since 2005-09-15)
It doesnât go on a server. Itâs within the document itself. Press Alt-F11 to start the VB Editor. Double click the ThisDocument module on the left, and paste in the following:
Private Sub Document_BeforeRefresh(Cancel As Boolean)
'your code goes here
End Sub
The code will run before the document is refreshed.
Dwayne Hoffpauir (BOB member since 2002-09-19)
Thanks for the reply, Dwayne.
Do you know if this will also work with XI? Iâve tried pressing Alt+F11 in the Edit mode of the document but nothing happened.
melon (BOB member since 2005-09-15)
Edit mode? Is this a Web Intelligence document? If so, it doesnât support VBA.
Dwayne Hoffpauir (BOB member since 2002-09-19)