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.
