OBIEE - What the "For" in Selection Steps stands for

As you probably remember, in OBIEE 11 there is a powerful option of selection steps, that enables us to add conditions that are executed after the filter / where condition of the analysis.

I covered the issue few times:

• OBIEE 11g Hierarchical Columns and Selection Steps (just to be clear, selection steps work fine on any non-measure column, not only hierarchical).
• OBIEE - Top 2 of top 10 with Selection Steps.
• OBIEE - Comparing performance to peers. 

While creating a condition with Selection Steps, there is a "For" hiding in the center of the Selection Step Wizard:

 After pressing the plus sign, the following is visible:

A question was asked, what is it used for?

I got a little scared seeing the documentation:

 

For:

"Use this area to qualify all of the dimensions of the analysis other than the dimension whose members you want to select, when creating condition steps of type Exception or Top/Bottom. For each dimension, you select which of its members to include. You can select specific members, or All, which specifies to aggregate the members when creating the condition. For example, suppose that you are qualifying the Region dimension. You can select a specific region, such as East, whose value is used in the condition. If you select All, then the values of all regions are aggregated and used in the condition.

You can use the For area to create a qualified data reference (QDR). A QDR is a qualifier that limits one or more of the dimensions to retrieve a single value for a measure column. A QDR is useful when you want to temporarily reference a measure column value without affecting the current status of the dimensions. The following is an example of a QDR:

Add members of Total Products (Rgd Sk Lvl) where "A - Sample Sales"."Base Facts"."1- Revenue", For: Cust Segments Hier: 'Active Singles', 'Baby Boomers' is greater than "A - Sample Sales"."Base Facts"."1- Revenue", For: Cust Segments"

When you specify a QDR, you can specify multiple members for limiting the dimensions. When you specify multiple members, the measure column value is aggregated using the default aggregation from the Oracle BI Server. For example, suppose that you want to create a condition for displaying those Regions in which Units is greater than 100. Suppose that you create a QDR for the Year dimension that specifies 2000 and 2001 and that the default aggregation is Sum. If the values for 2000 and 2001 for the Central region are 50 and 60 respectively, then both those years are displayed, because their sum exceeds the 100 units that was indicated."

I'll do an example that I hope would make it easier to understand.

I created a simple Analysis:

Year, Product_Type and Revenue (Aggregation Rule - Sum)... The only not totally trivial part is the % of year calculation that is 100*Revenue / (Sum Revenue by Year)   (100*"Base Facts"."Revenue"/sum("Base Facts"."Revenue" by "Time"."Per Name Year")). i will use that column in the next post.

Using regular Filters I can limit the data only to those Product Types that have Revenue greater that 200,000.

But I have a slightly different need: I want to limit the data to the Product Type with Revenue  greater that 200,000 at the Year 2009. Of course I could do it with 2 analysis (using filter "based on results of another analysis"), but it's so easy to do with the For option of selection steps!

The original Analysis was (I color coded the Revenue>200,000):

With 2 values over 200,000 at year 2008, 4 at 2009 and 3 at 2010.

In selection step for Product Type I'll keep only Revenue greater that 200,000 ,

But that's not enough, I need it only for the Year 2009. That is where the "For" option kicks in. 

 Now the result is as requested:

I can see only the Product Types with Revenue over 200,000 in the year 2009.

To summarize it to myself:

By default Selection Step work on specific dimension column, "For" option uses the condition with additional dimension / dimensions.

If I would have selected 2 years, for example, 2009 and 2010, I would have limit the results to (Revenue of 2009) + (Revenue of 2010), greater that 200,000.

The default of "For All" takes all the Years Revenue (for each Product Type) and combines it.

So Keeping Product Types with Revenue >200,000 with no "For" definitions will summarize all values for each Product Type and make sure they are greater than 200,000.

More about it, in the next post.  

As you might have noticed, the manual selection of Year=2009 and of 200,000 as minimal Revenue, can be replaced by Prompt.