I’m going to start a small series of posts showing you what’s new in Business Objects XI 3.0
I’m starting off small; with Conditional Prompts.
The Problem
Just to clarify. A conditional prompt is a question that is placed in the query that allows you to fine tune the results that you return from your database. The problem is; sometimes you want to see “some” of the data, other times you want to see it all. This is fine if you are a report developer as you can edit the query to add or remove the prompt. If you are a report consumer then you are stuck with the query as it has been developed.
When you use a prompt object it will ask you “Enter value(s) for Year:” for example and then allow you to select one or more years. If you want to see all years, then you have to select every value from the list. This is fine for years as there are not so many of them. When you have “lots” of values this becomes a pain to select, you might miss one, your query becomes “ugly” as it has all of the selected values embedded within the SQL and some databases actually put a limit on the number of values that you can pass in this manner.
The “Old” Solution
Using versions prior to XI 3.0 the way that you got around this problem was to manually edit your condition objects. Rather than having a “simple” condition:
Dates.Year in @Prompt('Enter value(s) for Year:')
You would manually edit this to become:
(Dates.Year in @Prompt('Enter value(s) for Year:') or 'All' = @Prompt('Enter value(s) for Year:'))
This works because if the user types in ‘All’ then ‘All’ = ‘All’ would be true for every row, so you get all of your data.
The problem with this method is that the user has to type in ‘All’, most users prefer to use a list of values (LoV) to select the values you want. You then have to manually edit the SQL that populates the LoV values to be something along the lines of:
select distinct year from dates union select 'All' from dual;
Again, this works but is more manual editing. This then has to be done on every object that you think that you will need this feature on.
The XI 3.0 Way
As of XI 3.0 there is a much more elegant (less labour intensive) way to do the same thing.
When you are building your query and you create a query filter you will see a new option denoted by the question mark icon to the right of the prompt text.
(by clicking on any of these images you will see a larger version)
Clicking this icon opens the prompt properties dialog box.
Here I have ticked the “Optional prompt” box to tell XI that I may not always want to supply values.
When I now refresh my query it asks me for the values I wish to associate with the prompt. Note the new text “(optional) If no value is selected, this filter will be removed.”
For the moment I’m going to select a single year, 2004.
When I run my report, you can see that only a single years data has been returned as requested. I have altered my report to also display the SQL used and notice how there is a “where” clause to restrict the year to a single value.
If I now re run the report but do NOT supply a year:
The report runs and shows me all years:
However, notice that this time there is no where clause to restrict the data.
Summary
Whilst this may not be the most earth shattering piece of new functionality, it is on that I think will speed up the report development process as it removes a lot of the simple but mundane tasks that a Designer had to do. It also results in much more efficient queries being sent to the database so the users should get their results just a little bit faster.
10 replies on “Business Objects XI 3.0 New Features – Conditional Prompts”
Hi Ross,
I’m using Xi-3.0 WebI. I’m trying to set the propmt as optional but I can’t see the option to make it as optional (Prompt Properties dialog box with Optional button). I see the box without that optional check. Am I missing any settings?
Your help would be highly appreciated.
Regards
Selva N
If you have a look at the screen shots in The “XI 3.0 Way”, the “?” icon should bring up the options dialog.
The second screen shot shows the radio button for “optional prompt”.
That should do the trick.
Hi Ross
that’s exactly what we are looking for. However, your example was focused on WebI reports. Is the same feature working on DesktopIntelligence, too?
Kind regards
Urs
I had a quick look and unfortunately I do not see this functionality in Desktop Intelligence.
Hi Ross,
My reports are in DESKI XI R3, migrated from BO 6.5 where I had used this syntax by editing report SQL to get ‘ALL’ in LOV of the prompt
Table.Column IN @variable(‘3. Select Column:’) or ‘*ALL*’ IN @variable(‘3. Select Column:’)
In 6.5 it works fine but in XI R3 DESKI it doesn’t. Any idea how to fix it?
Sorry for not getting back to you sooner, it’s been a hectic week.
Unfortunately and very unprofessionally I do not have my XIR3 virtual machine handy, I will have to build a new one from scratch before I can investigate this personally.
I saw that you have also logged this on the BOB forum : http://www.forumtopics.com/busobj/viewtopic.php?p=577898&sid=dc2f2ee645e7af24e8c1cf12415a314e
Hopefully you will get a speedy turn around from the wider audience that that site gets.
Apologies for not being much help this time.
Ross
Need some info for one of the Project.
1. Can we access data from 2 different cubes on one Voyager workspace?
For Ex: We can have a max of 4 components in a BO Voyager workspace. Out of which 2 are crosstabs. So, for one crosstab we are getting the data from one cube and for the other we are getting the data from another cube. Can we do this?
2. As we can access a Voyager workspace through Web I, Crystal reports and OLAP intelligence. but Can we access a Voyager workspace through Dashboard?
3. Can We access other docs(webI, Crystal rep, and OLAP I) through Voyager workspace? ( I guess NO as we dont have that hyperlinking functionality in Voyager)
4. Can we create Prompts in voyager workspace? (My guess is NO we cant create prompts in BO voyager). If no then is there any alternative to that??
5. What are the different kind of Analytics that we can add in a BO Voyager workspace?
6. As we know that Voyager was earlier OLAP intelligence the difference was OLAP intelligence use to sit on top of a Universe to fetch data from the cubes whereas Voyager directly accesses the data from the cube.
Can we use voyager to access data through a universe??
regards
Vivek
hi vivek…..
the answers to all the question is as follows:
1. yes we can do that … create different connections for different cubes and then use one connection to access data in one crosstab only…. u cant use more than one cube in one cross tab.
2. not sure yet but i guess yes…
3. yes very much but only from BO XI 3.1 version.. as this feature doesn’t exists in BO XI 3.0 or lower versions….
4. I’m afraid my friend .. Voyager is not so friendly yet.. powerful but not so friendly….
u cant create the prompts.
5. check the exception highlighting. Ranks Sorts etc etc etc …
6. yes very much… we can use universe to access the data using Voyager.
need to create a connection in CMC for that.and need to configure it with that universe..
By the way ….
from where did you get these questions..
As these are the same questions that i asked 2 weeks back in my mail to my competency head …. in my company….and fortunately the language is 100% same… neways it feels good to answer yourself. thanks that you gave me a chance. 🙂
Dear, by any chance do i know you…..
Dear, By Any chance do i know you??