Oracle Analytics Cloud and Server Idea Lab

Products Banner

Filter based on a report in DV

More Information Requested

Organization Name

Verizon Wireline IT


In regular OBIEE Answers , there is an option to filter the report based on the results of another analysis. BI Server generates a subquery and filters the data . Our Business users are looking for similar functionality in DV.

Use Case and Business Need

This feature is extensively used in answers today . Users want to visualize the data similarly in DV  .

More details

We have few use cases where the report needs to be dynamically filtered . This is achieved today using the filter ( filter on results of another analysis) . These use cases cannot be achieved in DV.

Original Idea Number: 66cca02d43

15 votes

More Information Requested · Last Updated

Will provide a potential workaround. Please let us know if the workaround is viable.


  • We were looking for similar option to demo to Executives.

  • gmigotto
    gmigotto ✭✭✭

    There are to similar features now (the "use as filter" and the action) that relates to that, but probably don't do exactly what you're looking for ...

  • It would be good to also have the option to filter based on the values of a Column from a Data Set (External Data Sets, Local Subject Areas, etc.).

  • We need this functionality to be able to handle subqueries that allow for queries where we have one-to-many relationships.

    For example, we have customer orders that have multiple types of products associated with them. Users need to be able to filter dynamically to a specific product type and return KPIs based only on the orders that have that product associated with it. With DV, there's no way to subquery and if we inner join the tables, we would dupe the orders in our KPI calculations causing incorrect values.

    There are other use cases as well such as linking disconnected datasets via a common identifier. Some of that may be more easily done through other methods, but both of these can be done with the Answers functionality mentioned above.

  • Please review this video for a potential workaround and let us know if this allows you to accomplish the immediate requirement while we review a feature to address it.

    Oracle Analytics : How to Filter a Canvas on Results of Another Viz/Canvas

    This video shows how to leverage a simple trick in expression filters (Self Service Analytics) to filter a canvas based on results from another visualization...

  • Hi Luis, thanks for the suggestion but this is a hard-coded and cumbersome work around. We would need the filter to handle changes in the underlying visualization automatically, not having to copy code around.

  • Michal Zima
    Michal Zima ✭✭✭✭✭

    @Luis E. Rivas -Oracle The video from @Philippe Lions-Oracle is fine, but procedure described here has several "drawbacks" (compared to method used in "Classical" - filter based on result of another analysis):

    1) It requires from author some "literacy" of logical SQL statements (which is not the case for all authors - this is simple reality....)

    2) Logical SQL used for filtering must have all WHERE condition hardcoded (I can possibly, now refer Parameters here. but again this requires even more advanced authors) , as opposed to solution in "Classical" where analysis (hidden used just for filtering "main" analysis) can be filtered dynamically using dashboard prompts - everything in declarative way - no need to "boher" with logical SQL expertise...

    So pls, consider those arguments when planning new features for DV (beside nice new augmented analytics stuff). Thanks

  • Hi

    Recently implemented such a requirement:

    1. Created a variable using logical sql
    2. Placed that variable in global filters
    3. Capture the variable selection in another variable using logical sql.(This is the part where your calculation based on another analysis is taken care of)
    4. Now you have this variable which hold the calculation based on the other variable which user is selecting from prompts
    5. Use this variable to filter the data in your reports

    Hope this helps!

  • Hi,

    Can you please provide some additional examples of how this was achieved in Oracle DV and what version of DV is your instance running.

    The examples that would be appreciated are as follows.

    1. Code used for creating the functionality and in what object the variable was created in (Workbook, LSA, DATASET, DATAFLOW).
    2. Where were the Global Filters applied in Oracle DV. If they were done in a workbook was this a canvas filter or a viz filter. If this was another DV Object please specify object type from the list above.
    3. How was the variable selection captured.

    Ideally if you can please share your process in a simple video recording showing the performed steps.

    Your help is greatly appreciated.



  • Hi Adrian

    1. The variable was created in the 4th option F(x) section.
    2. Global Filter can be applied in the top section of the Can or else you can also go for Dashboard Filter Visual.
    3. Once we create a variable in F(x) Section its name becomes a place holder for the selected values, which can further be used in the logical sqls of another variable. It has a Syntax with which it is to be used.

    Please let me know if this helps



  • Perhaps I'm not following Richa's instructions but I don't believe that will work the way we need it to, at least in OAS at this time. The issue is that we need the users to be able to select a specific filter value in one workbook, pass those filter selections into a second workbook that would then return it's results to the first workbook. We use this today in Answers to resolve one-to-many issues in data.

    I need to somehow make the logical sql statement dynamic so that it passes in whatever values are stored in the filter prompt into the WHERE clause.

  • @Aaron Epstein-261578

    I believe that @Richa Singh Kushwaha's recommendations only apply more or less for static code.This does not replicate functionality from OBIEE classic (SI Classic Home)

    Lets hope this is considered somewhere down the line for the DV Product Roadmap.

    Though currently this feature is not yet planned as per official channels, link below.

  • Rajakumar Burra
    Rajakumar Burra ✭✭✭✭✭

    Sub queries from another reports are very commonly used one.

    Many good functionalities in OBIEE is ignored in OAC DVs.

    Finally Parameter functionality is came but it has limitation of 1000 results output using logical SQL.