Metric Selector

Objective:  The objective of this POC is to create a prompt that will allow a user to select a metric (from a list of metrics) and enter an amount to filter it by.

This is accomplished by creating three separate prompts and storing the values in Presentation Variables.  Then your report can use these Presentation Variables to filter your results.

Step #1 - Create an answers report that contains the metrics that you want to filter.  Save this report as "POC Metric Selector".

Step #2 - Create a prompt that will do the following:

• Allow the user to select a Year
• Allow the user to select a metric to filter
• Allow the user to select which operator they want to filter their metric with (<=,=,etc.)
• Allow the user to enter a value to filter their metric by

The first prompt on Year is self explanatory.

The second prompt on Quarter is not really a prompt on Quarter.  I had to choose a column in order to enter SQL, so I choose a column that wouldn't contain much data to begin with.  Once the column was on the report, I changed the "Show" drop-down box to SQL Results.  This allowed me to enter the following SQL:

SELECT CASE Time.Quarter WHEN '2006 Q4' THEN 'Revenue'

  WHEN '2007 Q4' THEN 'Month Ago Revenue'

  WHEN '2007 Q3' THEN 'Quarter Ago Revenue'

  WHEN '2007 Q2' THEN 'Year Ago Revenue'

  WHEN '2007 Q1' THEN 'Month Ago Billed Qty'

  ELSE 'Booked Amount' END as c1 

FROM "Sample Sales Reduced" 

ORDER BY c1

The important thing to notice here is the name I am assigning each column.  It has to match the Presentation Column Name exactly (Revenue, Month Ago Revenue, etc.).  This will not work if you change this name at all.

**Once this is complete, set a presentation variable = 'PV_METRIC_SELECTOR'

The Third Prompt is similar to the second prompt.  I choose Week for this prompt, and then I went into the column formula and changed the formula to ' '.  I only did this to show you options.  You don't have to do this if you don't want to.  Here is the SQL I entered into Prompt Three:

SELECT case Time.Year WHEN '2006' THEN '>=' 

  WHEN '2007' THEN '<=' 

  WHEN '2008' THEN '<>' 

  ELSE '=' END 

FROM "Sample Sales Reduced"

**Once this is complete, set a presentation variable = 'PV_METRIC_OPERATOR'

The Fourth Prompt is similar to the second prompt.  Instead of setting the control to Drop-down, I set the control to Edit Box.  I then assigned a presentation variable = 'PV_METRIC_VALUE'

    RESULTS

Save this prompt as "Metric Selector Prompt".

Step #3 - Modify the report you created in Step #1 (POC Metric Selector).  You will need to add a new filter to the report.  Choose any column from the catalog to filter.  Once the filter dialog box is up, choose Advanced --> Convert this filter to SQL.  Delete everything in the edit box and paste the following:

"@{PV_METRIC_SELECTOR}{Revenue}" @{PV_METRIC_OPERATOR}{<>} @{PV_METRIC_VALUE}{-123456789}

If you know how Presentation Variables work, then you can see that the default for this filter (if a user enters nothing in the prompt) is Revenue <> -123456789.  This should not affect your results (other than filtering out NULLS).

Step #4 - Build a dashboard page and place your prompt and report in different sections.  Then test away...

Notes:

• Typically in OBIEE you reference everything by "Table Name"."Column Name".  I learned through this POC that OBIEE is just like SQL, you only need to reference the Table Name if your Column Name exists in more than one Presentation Table.  Since best practices advise against this, you should not need to reference your Table Name to get this POC to work.
• If you have a requirement to enter the table name in your filter, you can always hard code it in front of the SQL filter you put on the report ("Table Name". "@{PV_METRIC_SELECTOR}{Revenue}" @{PV_METRIC_OPERATOR}{<>} @{PV_METRIC_VALUE}{-123456789})
• I would like to credit the many BLOGs out there that helped me achieve this result.  I got the idea for this from  http://www.biconsultinggroup.com/obiee-tips-and-tricks/using-a-dashboard-prompt-as-a-column-selector-for-multiple-r.html and  http://obiee101.blogspot.com/2008/05/obiee-making-column-selector-in-prompt.html.
• Wondering what the Logical SQL looks like?  DetailFilter: D0 Time.T05 Per Name Year = '2008' and 3-01 Booked Amt (Sum All):[DAggr(F0 Rev Base Measures.3-01 Booked Amt (Sum All) by [ D2 Market.M00 Mkt Key, D2 Market.M01 Market, D4 Product.P00 Product Key, D4 Product.P01 Product, D0 Time.T02 Per Name Month, D0 Time.T03 Per Name Qtr, D0 Time.T05 Per Name Year] )] >= 10000
• Wondering what the Physical SQL looks like?  I wish I could show you that, but since I created this off of an XML data source, the SQL is very hard to read.  I can tell you from real world experience that the filter on the metric is not sent to the database.  The filter is applied once the data is on the BI Server layer.