Select Page

Using a Sub-Analysis as a Base Filter for Main Analysis Request

Author: Christian Screen | | April 6, 2011

Stepping away from the back-end of OBI 11g for a minute, I wanted to show one of the features of OBI that has just moved slightly to a different location that where it existed in 10g.  Seemingly in OBI 11g there a few features that look to be hidden just by the way the new prompts, wizards, or drop-downs are created.  Without looking deeply you just might miss them.  But this post isn’t about uncovering all the clandestine gems of OBI, it is simply to provide a tutorial for how to create an Analysis query filtered by a column on the results of an existing query.  I’ll let you determine for yourself where this may solve a use case and wield its power for your future development goals/requirements.

Get Started

This tutorial uses the SampleApp web catalog and metadata provided by Oracle which can be downloaded on Oracle’s BI portion of their website.

Here are the steps…

Tutorial Steps

  1. Create a New Analysis
  2. Select the Sample Sales subject area
  3. Create a query with the following criteria
  4. Add this simple filter on the Shipped Amount fact
  5. Save the filter in a simple folder (somewhere in My Folders) called “Analysis A” or something similar
  6. Create a New Analysis which will be our primary analysis that will filter off of “Analysis A”
  7. Select the Sample Sales subject area again
  8. Create a query with the following criteria
  9. Add a new filter either by clicking on the filter icon and selecting “Product”.”P1 Product” or by expanding the column drop-down for the Product column and selecting Filter
  10. In the Operator drop-down scroll all the way to the bottom of the list and select “is based on the results of another analysis”
  11. When the prompt window changes, click the “Browse…” button.  Navigate to the folder where the “Analysis A” resides and select “Analysis A”.  Ensure the Relationship drop-down is set to “is equal to any”.
  12. In the Use Values in Column drop-down select “P1 Product”
  13. Click OK
  14. Click the Results tab.
  15. Notice that the query results are predicated on the Analysis A query that returns only the products with a Shipped Amount of less the 2,000,000 dollars.
  16. Save the analysis as “Analysis B”
  17. You are done.  Pat yourself on the shoulder.


This tut just gave you a look at a filter option that most people simply overlook in OBI 11g because it is tucked at the bottom of the drop-down.  In addition, you learned about one of the nifty features of OBI that has been around for a minute but still works and looks better in OBI 11g.  And, if you never know about it, now you do. Peace.

Oracle BI Publisher (BIP) Tips: Functions, Calculations & More

Check out these BI Publisher tips including functions & calculations so you can understand more about the production and support of BI Publisher reports.

Sherry Milad | January 15, 2018

How to Index a Fact Table – A Best Practice

At the base of any good BI project is a solid data warehouse or data mart.

Christian Screen | March 16, 2010

Qlik vs. Tableau vs. Power BI: Which BI Tool Is Right for You?

Tableau, Power BI, and Qlik each have their benefits. What are they and how do you choose? Read this blog post for a quick analysis.

Tom Hoblitzell | June 6, 2019

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.


Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.