nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Allow advanced SQL statements in prompted filters

Jessica D'Apice

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

Find more posts tagged with

Submitted

Agents

BI Publisher

BI Publisher Data Models

Filters

Status: Submitted

Comments

Gail Langendorf-Oracle

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

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

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