OBIEE - Nested selections or multi data tiers

We have a requirement:

1. We need to select offices who sold specific product.

2. Based on this offices list we need to select Revenue of all products ordered by those offices and the percentage of these revenues compared to the total orders per each product.

When I saw the requirement I thought it's native to OBIEE:

All we need is to create an analysis that returns the Offices based on a product prompt:

I named this analysis "offices selling selected product".

Naturally create a prompt for product...

 I had a little problem with the second part of the request. What comes naturally in OBIEE is to add those offices as a filter. Something like this:

 

But we had a request to compare it to the total sales of other offices as well.

I started thinking about options:

1. Should we use union all between this select and select with 1 more column of Revenue with all offices without the filter? It works but not elegant.

2. Should we create an external function to return total sales for given product and use Evaluate function? 

3. No, there is a native OBIEE way to do it: Just save the above filter and reuse it in a column filter:

 Create the second analysis, and add there 2 Revenue columns. In one of them Edit Formula and Select Filter there

Now in the Filter window just select the saved filter:

 Here we see a little problem:

The formula is:

FILTER("Base Facts"."Revenue" USING ("Offices"."Office" IN (SELECT saw_1 FROM (SELECT "Base Facts"."Billed Quantity" saw_0, "Offices"."Office" saw_1, "Products"."Product" saw_2, DESCRIPTOR_IDOF("Sample Sales Lite"."Products"."Product") saw_3 FROM "Sample Sales Lite" WHERE ("Base Facts"."Billed Quantity" > 0)) nqw_1 )))

 (sorry I was a bit lazy and didn't remove unnecessary columns in the first analysis)

 

There is no trace to the "is prompted" part for the Product !

To fix it we have to change the prompt we created and add an option of setting a Presentation Variable to it:

You might (but don't have to, in regular cases) want to replace the is-prompted filter in the first analysis with  "Products"."Product" = ('@{nested}{Maintenance}') 

In the second analysis we can manually change the formula to:

FILTER("Base Facts"."Revenue" USING ("Offices"."Office" IN (SELECT saw_1 FROM (SELECT "Base Facts"."Billed Quantity" saw_0, "Offices"."Office" saw_1, "Products"."Product" saw_2, DESCRIPTOR_IDOF("Sample Sales Lite"."Products"."Product") saw_3 FROM "Sample Sales Lite" WHERE (("Base Facts"."Billed Quantity" > 0) AND ("Products"."Product" = ('@{nested}{Maintenance}')))) nqw_1 )))

 Adding the AND ("Products"."Product" = ('@{nested}{Maintenance}'))
So the Where condition is:
WHERE (("Base Facts"."Billed Quantity" > 0) AND ("Products"."Product" = ('@{nested}{Maintenance}')))
instead of
WHERE ("Base Facts"."Billed Quantity" > 0)
we had before.

Now it's working.

Few remarks:

1. Why did I do manual modifications and didn't recreate the filter with the update to  "Products"."Product" = ('@{nested}{Maintenance}') of the first analysis?
Because even if I did, the result in Edit Formula would have been:
FILTER("Base Facts"."Revenue" USING ("Offices"."Office" IN (SELECT saw_1 FROM (SELECT "Base Facts"."Billed Quantity" saw_0, "Offices"."Office" saw_1, "Products"."Product" saw_2, DESCRIPTOR_IDOF("Sample Sales Lite"."Products"."Product") saw_3 FROM "Sample Sales Lite" WHERE ("Products"."Product" = ('Maintenance')) AND ("Base Facts"."Billed Quantity" > 0)) nqw_1 ))) 
with where condition including:
("Products"."Product" = ('Maintenance'))

Instead of: 
("Products"."Product" = ('@{nested}{Maintenance}'))

2. If you try to recreate my steps with the Sample Sales, it wouldn't work, because the Product column has "Descriptor ID column" defined in the administration.  So I cheated and the prompt formula was actually: "Products"."Product" || ''