Dave's Adventures in Business Intelligence

I have seen this question come up frequently on BOB lately. And if the question keeps coming up, it becomes an easy idea for a blog post. That way, once I document a concept or a solution I have somewhere consistent to link to. The short answer to the question, unfortunately, is no. But let’s investigate and explain a bit further than that, shall we?

Problem Description

I have a report. The report has a date prompt. I want the date prompt to have a default value (partially possible) and I want the default value to be “today” instead of a standard date. The question is quite reasonable, in my opinion. Prompt handling has been one of the weakest points of the Business Objects tools for a long, looonnng time. A prompt can be created on a report or in the universe. If the prompt is defined in the universe there is a standard syntax. So I will start with a brief review of that syntax and move on from there.

@Prompt() Me One More Time…

The standard prompt syntax has five arguments. The first two are generally considered required while the remaining three are optional. Here is the syntax:

@prompt('Enter prompt text here','A',LOV Source,mono/multi,free/constrained)

I’ll break down each of those five parameters next. First, the prompt text. The first argument is required and is the most obvious… it contains the text that the user will react to. It’s a good idea to let the user know what’s going on and to provide some clue as to how the prompt will operate. For example, if you are using a prompt in an equality test, you should inform the user that they can only select a single value. If you are using the prompt in a list, then let them know they can select one or more values.

The next argument has three options: A, N, or D. These match with the datatype expected from the prompt. The ‘A’ represents alphanumeric data, and any data entered is accepted as it was entered. Using an ‘N’ denotes a numeric prompt and only valid numeric values are accepted. Finally, a ‘D’ represents a date prompt. With this type of prompt Webi will include a spiffy calendar that a user can use to select their entry. This is available even if the LOV has been turned off.

The third parameter is a reference to a potential list of values for the prompt. There are two possible options: either a hard-coded list, or an object from the universe. If I wanted to make a hard coded list with the values “Yes” and “No” on it here is the syntax:

{'Yes','No'}

The values are surrounded with curly braces { } and entered as a comma-separated list. Character data is in quotes. Simple enough, yes? no? 😉

If I wanted to use an existing object in the universe for the list of values I would enter that choice without the curly braces and in the format 'Class Name\Object Name' instead. One common designer mistake is attempting to use a table.column syntax here. The table.column is of course the eventual source for the list of values, but the LOV query is defined as part of an object definition. That’s why you enter an object here rather than a table column.

The last two parameters are optional, but even if you leave them out you have to include the commas for the prompt syntax. In other words, this is valid:

@Prompt('Do you like BOB?','A',{'Yes','Yes, of course'},,)

Even though the values are missing the commas remain. What do the values do if you include them? The fourth parameter allows you to specify whether the prompt is a multi-select option or not. The value mono (no quotes here) says the prompt is restricted to a single value. The value multi (again, no quotes) allows for multiple values to be selected. The fifth parameter determines whether the user can type the prompt response or if they are required to pick from the list of values. The value free does the former, and the value constrained requires the LOV selection. Here’s a hint: don’t make a prompt constrained without providing a valid list of values source. 😉

So those are the five basic arguments for the @Prompt() function. Most of you will have noticed that none of them have anything to do with providing a default value. 😕

@Prompt() Extended Syntax for Web Intelligence

We got some new prompt options starting with Web Intelligence 6.x. One of the options a report writer could use was whether or not the last prompt value entered was the default for the next report run or not. Another option was whether the prompt had a default value or not. And finally, report prompts could appear in a user-specified order rather than in alphabetical order as before. But all of these options were report specific and not supported in Universe Designer.

Here’s what the extended prompt syntax looks like:

@Prompt('Enter value(s) for Something:','A','Class\Something',Multi,Free,Persistent,{'Default Option'},User:0)

I’ve already covered the first five parameters so now I’ll detail the last three. The sixth parameter has the value Persistent or Not_Persistent. The default value is to retain the last value entered (Persistent). This is also the legacy behavior; previous versions of the reporting tools only worked in a persistent manner.

The seventh parameter has a syntax that looks a bit like the hard-coded LOV from earlier with the curly braces. This is where a default value for the prompt can be stored. And finally, the last parameter determines the order of the prompt, starting with zero (0) as the first prompt and incrementing from there.

To reiterate something I stated earlier: These are report options only. These parameters can be entered into a prompt object definition in the universe, but they are unlikely to parse because of the extended syntax. In order to test this syntax I would have to export my universe and test in a Web Intelligence query.

What About That Default Value?

Extended prompt parameter number six: a default value. That brings me back to the original subject for this post, doesn’t it? I wanted to have a value like “sysdate” or a formula of some kind like CurrentDate() plugged into that parameter. Unfortunately, it doesn’t work. Any value entered for that parameter is treated as data, and no formulas or calculations will be calculated. So anything like sysdate or getdate() or CurrentDate() is going to fail.

The next trick is to try to reference an object, and store the formula as the object definition instead. Unfortunately, again, it fails. The object name becomes the default value for the prompt which is generally not the intended result.

Conclusion

What it boils down to, as I see it, is this: you can’t do it. There is no way to create a prompt syntax that will substitute in a value like “today’s date” for a prompt default value. It would be nice, but it doesn’t work.

However, I do have a work-around that I’ve used for many years. I create a “magic date” and some creative logic within the prompt to do the substitution after the user has already responded to the prompt. That’s the subject for the next post in this series. Stay tuned for details. 😎

Things I really REALLY wish prompts could do…

1. Set a default with a variable. It’s the subject of this post, need I say more?
2. Be optional! With XI 3.0 a report writer can create optional prompts now, but we still can’t do them in Designer. Boo.
3. Do cascading prompts effectively. This has been on my wish list for-EVER. I have another post about cascading prompts coming up soon so I’ll save the majority of my whining until then.
4. We have free and constrained, I want a third option: validated. This would be the best of both worlds. A user would be “free” to enter the value if they wanted to, but it would be validated against the LOV provided in the universe. Today I have to allow the user to shoot themself in the foot by typing the wrong value, or I have to force them to use the LOV.