Dave's Adventures in Business Intelligence

One of the possible enhancements we have been requesting for years is the ability to validate prompts. (We’ve also been looking for the ever-so-popular ability to use a formula such as “Today()” as a default for a prompt but this is different.) If we had true cascading prompts in Web Intelligence that would eliminate one use case for validated prompts but not all. I had someone comment on my blog recently asking about how to validate one prompt selection against another and that started me thinking… what would something like this look like if we did get it?

Here’s a really simple example: I want to ensure that a user enters an end date that is at or beyond the entered start date value. This validation rule is designed to avoid confusion since many databases will not return any rows for a “backwards” between operation. (It’s also a fairly common request, found as far back as 2006 in this topic on BOB and again 2008.) I can think of two ways to make this work. First, enforce the prompt entry order so that the user cannot enter the second date before they enter the first date, and give me some way to reference their first entered date in the list of values (LOV) for the second date. By making the second prompt constrained I would force the user to pick from the list of dates, and by referencing the first date in the LOV I can ensure that the only dates that show up do actually occur after the first date. This is technically probably easier to implement, but not the preferred solution.

A second option would be to allow me to create validation rules that fire after all of the prompts have been entered. This is far more flexible as it allows the user to respond to prompts in any order they would like (as it works today) but the validation is done before the query is sent to the database. This could be far harder to implement, primarily because I envision some form of validation language (VB? Crystal?) would be required. Do they invent some new language, or try to implement something that already exists? What if the language is not supported on all platforms? The date example that I have used so far seems fairly trivial: the rule would simply be end_date >= start_date which doesn’t look that complicated. It looks like an expression rather than a language, but a language is more than that. A full-blown language can have a grammar, reserved words, and all sorts of rules that specify how the various components can be compared.

Does my prompt validation language allow looping structures? I might want to be able to loop through a list of selected items in the case where a prompt offers more than one value. “Make sure all of the end dates are greater than or equal to the latest selected start date” would be one example. First I have to parse the list of start date values to find the largest entry, and then I have to process the list of end dates to make sure that 100% of them exceed the largest start date.

Doesn’t look so simple any more, does it? 🙂

Any prompt validation logic would also have to ensure that validation loops don’t exist. Here I have defined validation rules on each entry that can be true individually but can never be true collectively. This is actually a data entry or coding error but I need to be able to check and react to that.

• Rule 1 on the start date: Make sure that the start date is greater than the end date.
• Rule 2 on the end date: Make sure that the end date is greater than the start date.

Can the start date be greater than the end date at the same time the end date is greater than the start date? Probably not. 🙂 Since only one of those two rules could ever be true at a given time the query would never run. With three (or even more) prompts the potential complexity for validation loops only gets worse.

How Might Simple Validation Logic Work?

As a universe designer I am charged with creating components of code that ultimately will be placed together in some random fashion by a query writer. I have no idea what sort of questions they’re going to want to answer, nor should I be constrained by that concern. I should be able to make prompt objects that function perfectly fine by themselves, but also in combination with other prompts. The “start date / end date” example I started with is very simple. Consider this syntax:

some_table.some_date between @prompt('Enter Start Date','D',,mono,validate:&1<&2) and @prompt('Enter End Date','D',,mono,validate:&1<&2)

What I have done here is supplement the "free/constrained/primary_key" option with a new feature: validate. The syntax breaks down like this:

The < character is providing a simple validation expression "less than" using a standard mathematical symbol. Other options might include >, >=, <=, ==, and !=. These are all single-value operators that would not support a multi-selection prompt, and the scope of the &1 and &2 doesn't extend beyond this particular prompt object, but it's a start.

Named Prompt Components

In order to extend my validation scope beyond a single prompt I need to ensure that I have a unique name that I can reference. (As an aside, this is why class names must be unique within a universe structure. The class name\object name combination must be unique within the universe in order to support the @Select() functionality.) To do this, I might extend my new prompt syntax with the following:

I have introduced two new arguments. The extra argument &0 will allow me to define a unique name for this prompt, and &n:name allows me to reference that value in another prompt. Now I can create two separate prompts that look like this:

Start date check
some_table.some_date >= @prompt('Enter Start Date','D',,mono,validate:&0:start_date&n:start_date<=&n:end_date)

End date check
some_table.some_date <= @prompt('Enter End Date','D',,mono,validate:&0:end_date&n:start_date<=&n:end_date)

Keeping in mind that it is entirely likely that I would have more than one start date or end date in my universe, I would have to use more verbose prompt names like account_start_date or invoice_range_start_date and so on. In the above example I have two prompts, each has a name, and each has a validation rule. By allowing prompts to have names I can reference the result of one prompt inside of another prompt.

Event Triggers

The next challenge could be to determine when the validation logic fires. Does it fire at the end of each prompt selection? I can't really see that working because all of the required values might not yet be defined (selected). Does the validation logic fire when the query is executed? That also might not make sense because if I have a chain of three prompts where 3 depends on 2 and 2 depends on 1 I should be able to trigger the validation as soon as any two values are present. In my simple "start date / end date" example it would be easy to say that the validation logic fires as soon as both values are present. I also haven't addressed how to create the message that is delivered to the user if the prompt validation fails...

Most of the complexity in this process comes from the fact that I'm trying to design a fully generic solution. I don't want to have to write new code for each new prompt screen that I might design, I want to create reusable logic and syntax that works across the entire universe. I think it's easy to see why this is such a complex question, and perhaps indicates why we don't have anything like it so far within the universe.

But being able to set up a date value like "today" as a dynamic default value... that should be easier to implement. I hope we see that... and soon.