cancel
Showing results for 
Search instead for 
Did you mean: 

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

Former Member
0 Kudos

Create a prompt that prompts the user to enter either [Post Date] or [Service Date]. 

The user MUST choose one or the other but not both.

WebI

SAP BusinessObjects BI Platform 4.1 Support Pack 4

Version: 14.1.4.1327

http://www.sap.com/bi

Has anyone been able to make something like this work?

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hello Jennifer,

You can create a simple variable like :

if  (UserResponse("prompt_1") <> "" and UserResponse("prompt_2") <> "" ) then 1 .

Then filter the body of the report with your variable value not equal to 1.

Put a table at the header of the report, with the formula : hide when variable not equal to 1. And write a notification message at one of its cell to notify users that they should enter only 1 prompt value.

So basically, if they enter both prompt values they will see a blank page and an alert message at the header, else they will see the report itself. Though in both cases, background queries will run so users would have to wait a while even though they would see only a reminder message.

Regards,

Onur

nscheaffer
Active Contributor
0 Kudos

What do mean by "notification message"? How do you do that?

Thanks,

Noel

Former Member
0 Kudos

Hi,

I mean, if you just write something like "You must fill only 1 prompt value" in a cell of the table i mentioned, which you placed at the header; users will see that message if they enter both prompts and so they would know they should enter only 1 prompt to see the results of the report.

nscheaffer
Active Contributor
0 Kudos

I think I have an answer for you. It is moderately complex and certainly not without its drawbacks, but I believe it will achieve your objective.

There are five basic steps...

  1. Remove the date based criteria from your query.
  2. Create a second query with "dummy prompts".
  3. Use custom query script on the second 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 their open and closed dates which will correlate to your Post Date and Service Date dimensions. The attached document has related screen shots.

  1. Remove the date base criteria from your query. You should keep Post Date and Service Date as Result Objects because you will need to filter on them in report. This is one draw back of this approach; you need to filter the data in the report rather than the query resulting in potentially returning a lot more data and negatively impacting the performance of the report.
  2. Next create a second query with "dummy prompts". It doesn't really matter what universe you use because you don't want any data return; we are just going to use the responses to the prompts. I actually just duplicated my first query and then added some criteria to ensure that I get no data. In my case that is "Branch Number Less than 0". You will need to added two fields to prompt on. One could be either Post Date or Service Date. The other can be any dimension that is a string. Choose "Equal to" as the operator for both of them and make them prompts.
  3. Now you need to switch to "Use custom query script" just for this second query with the dummy prompts. This is the other drawback in my view because not everyone likes using this option. Also, you have to keep in mind that if you are using custom query script and you edit your query in any way your custom query script will be thrown away without warning and replace by newly regenerated query script. You can always go make your changes again, but you 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. We need to change the prompt text again; I made mine 'Enter Date Dimension:'. The key here is to put your date choice in the list of available values parameter. So instead of pulling the possible values from the underlying data put something that make sense for your situation. I made mine {'Open Date','Closed Date'}.

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

    So we have created a second query that will return no data, but will prompt for a date value and what that date should apply to.

    Hit "Run Queries". The query with the dummy prompts will return no data as expected.

  4. We need to create two variables to capture the response to the dummy prompts. You have to keep in mind 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. Then 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))

    And finally, add a filter where Date Comparison Flag Equal to 1.

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

Noel

nscheaffer
Active Contributor
0 Kudos

I figured out that I could post the images that I had put in the attached document in-line if I used Firefox. Check out the document I created related to this topic with the images in-line...

Hope this help you.

Noel