Creating a “What if?” document with prompts.

In business, planning often requires us to look at alternate potential scenarios regarding the future. We can use prompts and variables to create documents that allow us to evaluate various scenarios. The idea here is prompt for answers to the “What if?” questions when the report is refreshed, and then use the responses to those prompts to calculate the future projections.

I used the Island Resorts Marketing universe to set up this example. We’ll start in Designer. Open the Island Resorts Marketing.unv universe, and create a new class called Prompt Objects. This doesn’t mean that these objects show up on time. It means that they contain prompts. J

Create a Dimension object in the new class called Bahamas Beach Increase? with the following Select:

@Prompt(‘Enter % Increase for Bahamas Beach (e.g. 20)’,’n’,,mono,free)

Let’s look at the 5 arguments for this @prompt function:

  • ‘Enter % Increase for Bahamas Beach (e.g. 20)’: This is the text of the prompt.
  • ‘n’: This means that the function will accept numeric input.
  • The third argument is left blank, as this is the List of Values, and we don’t need a List of Values for these objects.
  • mono: This prevents users from entering more than one value in the prompt.
  • free: Allows values to be typed into the prompt.

This object will not parse in Designer, as it doesn’t reference a table. It must, therefore, be used with other objects that do reference a table. This should be mentioned in the object’s description.

Create two more prompt objects, one called French Riviera Increase?, and one called Hawaiian Club Increase?, each with appropriate prompt text. Save and export the universe. Now it’s time to log into Web Intelligence.

Create the following query:

Run the query, and enter values into the prompts:

Remove the three prompt columns from the resulting block. Create a crosstab with the results, placing Year across the top and Resort down the left side. Next, create a variable called Projected Revenue with the following formula:

=If([Resort]=”Bahamas Beach”;[Revenue]+([Revenue]*(ToNumber([Bahamas Beach Increase?])/100));If([Resort]=”French Riviera”;[Revenue]+([Revenue]*(ToNumber([French Riviera Increase?])/100));If([Resort]=”Hawaiian Club”;[Revenue]+([Revenue]*ToNumber([Hawaiian Club Increase?])/100))))

Note: Values returned by a prompt are always characters, even if entered as a number. Therefore, we use the ToNumber() function to convert them to a number.

This will calculate the revenue change based on the values entered into the prompts. Add a column to the right side of the crosstab, and place Projected Revenue into the new column. Finally, add one more column to the crosstab and create a variable called Projection which will display the values entered in the prompts, and place the new variable in the blank column. The formula for Projection will be as follows:

=If([Resort]=”Bahamas Beach”;UserResponse(“Enter % Increase for Bahamas Beach (e.g. 20)”);If([Resort]=”French Riviera”;UserResponse(“Enter % Increase for French Riviera (e.g. 20)”);If([Resort]=”Hawaiian Club”;UserResponse(“Enter % Increase for Hawaiian Club (e.g. 20)”))))+”%”

Each time you refresh the document, you can enter different values into the prompts, allowing you to explore various scenarios.


One Response to Creating a “What if?” document with prompts.

  1. Garrett says:

    It’s going to be ending of mine day, except before finish I am reading this wonderful article to increase my know-how.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: