Oracle Transactional Business Intelligence Idea Lab

Home› Oracle Analytics› Oracle Analytics Idea Labs› More Solutions Idea Labs› Oracle Transactional Business Intelligence Idea Lab

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Allow advanced SQL statements in prompted filters

Submitted
32
Views
3
Comments
Jessica D'Apice
Jessica D'Apice Rank 3 - Community Apprentice
in Oracle Transactional Business Intelligence Idea Lab

Organization Name

Inovalon

Description

CURRENT BEHAVIOR: Prompted filters and data parameters on OTBI Analyses and BI Publisher reports respectively are limited to the operators given when setting up the prompt or parameter (equal to, between, like, etc), and these prompts and parameters do not accept SQL queries.

DESIRED BEHAVIOR: Add an Operator option to OTBI prompted filters and a Parameter Type option to BI Publisher data model parameters for a SQL Expression (as is available when putting filters on an analysis or building a data model).

Use Case and Business Need

Capturing relative timeframes in OTBI analyses and BI Published reports requires SQL expressions. If we need to capture different timeframes, we are forced to create different reports, which causes clutter, confusion, and inefficiency in our system. We should be able to create one analysis or report, and then several Agents and Report Jobs to support the different filtering needs of our different business partners.

Original Idea Number: fb7e774f5a

Tagged:
1
Up
1 votes

Submitted · Last Updated

Comments

  • Gail Langendorf-Oracle
    Gail Langendorf-Oracle Rank 5 - Community Champion

    In OTBI, when creating or editing a prompt, have you tried expanding the Options section and in Choice List Values, select SQL Results? Then you can enter a SQL statement to generate the list of values.

  • Jessica D'Apice
    Jessica D'Apice Rank 3 - Community Apprentice

    Selecting the SQL Results option only generates the list of values. We need a FIELD that allows the ability to enter SQL statements in the field itself. Take a look at the Use Case and Business Need section of this idea.

  • MaartenDIF
    MaartenDIF Rank 4 - Community Specialist

    Hi Jessica,

    From your requirements, I read that you want to use the advanced SQL in Agent prompts. This is also something our business requires.We need to be able to schedule a report based on the SYSDATE (e.g. end of current month). Currently, we're forced to run a dashboard manually every week - as it is possible to incorporate SQL statements in dashboard prompts.

    If your users are running the dashboards themselves, and not using an agent. Your requirements can actually already be fulfilled using Gail's suggestion. I've been struggling with this requirement and found quite some resources, but I'm missing the last bit of functionality to get there (using sql in agents for parameters).
    Hopefully these resources prove usefull to you too:
    https://cloudcustomerconnect.oracle.com/posts/d801ffcf62
    http://obieeil.blogspot.com/2014/02/obiee-prompts-last-year-last-month-and.html 
    https://www.rittmanmead.com/blog/2014/12/timestamps-and-presentation-variables/ 

    Kind regards,
    Maarten