A measure chosen with a prompt

Sometimes users want to have an option what a dimension or a measure to show in the report without modifying a report. Very handy might be an object with 2 columns and a prompt. In this eFashion data example I want to provide users with a prompt to choose whether they want to pull a revenue or a margin into a report.

Let’s create a new measure in a universe. This syntax is for MS Access but can easily be replaced for other databases.

@Prompt('Choose a measure:','A', {'Revenue','Margin'},Mono,Constrained,Persistent )='Revenue', 
@Select(Measures\Sales revenue), 

Let’s call it “Revenue or Margin” and pull into a report with some other objects.


Now we put together a simple crosstab with State, Year and our custom measure in the body of the table. In the corner we will show what value was selected by using a formula:

=UserResponse(“Choose a measure:”)


By refreshing a report and providing a value in a prompt we control what measure will show up in a crosstab. Please remember to set a prompt with settings Mono and Constrained so that the users would be able to choose only one value from the predefined list.


Here are the results of choosing one value or the other:



Leave a Reply

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

WordPress.com Logo

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s