How to require a response to one and only of a set optional parameters

Earlier today I responded to the following discussion question…

Does anyone know how to create a webi prompts where the user must choose one or the other?

I have wanted to do this before, but I had never really dug into it. However, I had some time today so I decided to give it a try.

It is moderately complex and certainly not without its drawbacks which I will discuss at the end.

There are five basic steps…

1. Remove the criteria which involves the optional prompts from the primary query.
2. Create a secondary query with “dummy prompts”.
3. Use custom query script on the secondary query.
4. Create variables in the report to capture the responses to the dummy prompts.
5. Create a variable related to those dummy prompt responses and filter on it.

In my example I am going to use a universe of mine that has Accounts and look at the Account Open Date and Account Closed Date dimensions against which I will in effect create optional parameters. The attached document has related screen shots.

1. Remove the criteria which involves the optional prompts from the primary query. I need to keep those dimension as Result Objects because I will need to filter on them in the report.

   

2. Next create a secondary query with “dummy prompts”. It doesn’t really matter what universe I use because I don’t want to return any data anyway; we are just going to use the responses to the prompts. I actually just duplicated primary query and then added some criteria to ensure that I get no data. In my case that is Branch Number Less than 0. I then added two fields to prompt on. One could be either my Account Open Date or Account Closed Date. The other can be any dimension that is a string. I chose “Equal to” as the operator for both of them and make them prompts.

   
   

3. Now switch to “Use custom query script” just for this secondary query with the dummy prompts. I have to keep in mind that if I am using custom query script and I edit my query in any way my custom query script will be thrown away without warning and replaced by newly regenerated query script. I can always go make my changes again, but I need to remember to do that.

   To switch to use custom query script click on the “View Script” icon at the top of the Query Panel and choose the “Use custom query script” radio button. For the first prompt that corresponds to whatever date field you chose change the prompt text in first parameter of the @prompt function to make it generic. I made mine ‘Enter Date:’…

   @prompt(‘Enter Date:’,’D’,’Account Attributes\Account Open Date’,Mono,Free,Not_Persistent,,User:0)

   The parameters of the prompt function corresponding to the string field requires a little bit more modification. I need to change the prompt text again; I made mine ‘Enter Date Dimension:’. The key here is to put the date dimension choices in the list of available values parameter. So instead of pulling the possible values from the underlying data I made mine {‘Open Date’,’Closed Date’}.

   @prompt(‘Enter Date Dimension:’,’A’,{‘Open Date’,’Closed Date’},Mono,Constrained,Not_Persistent,,User:1)

   

   So I have created a secondary query that will return no data, but will prompt for a date value and to which date that value should apply.

   

   I hit “Run Queries”. The query with the dummy prompts returns no data as expected.

4. Then create two variables to capture the response to the dummy prompts. Remember the UserResponse function always returns a string. My Date Prompt Response variable formula to capture the date looks like this…

   =ToDate(UserResponse(“Enter Date:”); “M/d/yyyy hh:mm:ss a”)

   

   And my Date Dimension Prompt Response variable formula to capture to which dimension that date should be applied looks like this…

   =UserResponse(“Enter Date Dimension:”)

   

5. Finally, create a variable based on those two variables which determines which date to filter on and whether the date entered matches. Here is my Date Comparison Flag variable formula…

   =If([Date Dimension Prompt Response]=”Open Date”; If([Date Prompt Response]=[Account Open Date];1; 0); If([Date Prompt Response]=[Account Closed Date];1;0))

   

   Add a filter where Date Comparison Flag Equal to 1.

   

That’s it!

I fully acknowledge there are a number of concerns about this approach. Here are a few of mine, you may have more…

1. The data is filtered in the report rather than the query resulting in potentially returning a lot more data than necessary and negatively impacting the performance of the report.
2. Using custom query script may be disabled in your organization. Also, if the query is edited in any way the query script gets regenerated and the customization is abandoned.
3. Although in effect this solution forces you to pick one and only one of two optional value they are not optional parameters making this potentially logically misleading.
   
4. I have not tried this approach with choose one value among a set of optional parameters where the values are not of the same data type. I think it could still be made to work, but I am not sure.
   

I know this is not a perfect solution, but I hope you can make it work for your situation or it give you an idea of something else to try.

Noel